Double entry with same info but in two columns in MS Excel

up vote
0
down vote

favorite

I have a big database where the data is like this:

Database data

Now, I just want to get the info for unique values (for me Steve-Kevin is the same as Kevin-Steve, I can just take any one of them). How can I sort them in a way to get the unique value?

edited Nov 14 at 16:44

Worthwelle

2,1832724

asked Nov 14 at 16:08

A S

So is Kevin-Steve a duplicate that you can effectively delete out of the data, keeping Steve-Kevin?
– BruceWayne
Nov 14 at 16:24

Exactly, I want to effectively delete out the Kevin-Steve and want to work with the data associated with Steve-Kevin only. That is where I am stuck now. Thank you!
– A S
Nov 14 at 16:43

add a comment |

up vote
0
down vote

favorite

I have a big database where the data is like this:

Database data

Now, I just want to get the info for unique values (for me Steve-Kevin is the same as Kevin-Steve, I can just take any one of them). How can I sort them in a way to get the unique value?

edited Nov 14 at 16:44

Worthwelle

2,1832724

asked Nov 14 at 16:08

A S

So is Kevin-Steve a duplicate that you can effectively delete out of the data, keeping Steve-Kevin?
– BruceWayne
Nov 14 at 16:24

Exactly, I want to effectively delete out the Kevin-Steve and want to work with the data associated with Steve-Kevin only. That is where I am stuck now. Thank you!
– A S
Nov 14 at 16:43

add a comment |

up vote
0
down vote

favorite

I have a big database where the data is like this:

Database data

Now, I just want to get the info for unique values (for me Steve-Kevin is the same as Kevin-Steve, I can just take any one of them). How can I sort them in a way to get the unique value?

edited Nov 14 at 16:44

Worthwelle

2,1832724

asked Nov 14 at 16:08

A S

I have a big database where the data is like this:

Database data

Now, I just want to get the info for unique values (for me Steve-Kevin is the same as Kevin-Steve, I can just take any one of them). How can I sort them in a way to get the unique value?

microsoft-excel microsoft-excel-2016

edited Nov 14 at 16:44

Worthwelle

2,1832724

asked Nov 14 at 16:08

A S

edited Nov 14 at 16:44

Worthwelle

2,1832724

asked Nov 14 at 16:08

A S

edited Nov 14 at 16:44

Worthwelle

2,1832724

edited Nov 14 at 16:44

Worthwelle

2,1832724

edited Nov 14 at 16:44

Worthwelle

2,1832724

asked Nov 14 at 16:08

A S

asked Nov 14 at 16:08

A S

asked Nov 14 at 16:08

A S

So is Kevin-Steve a duplicate that you can effectively delete out of the data, keeping Steve-Kevin?
– BruceWayne
Nov 14 at 16:24

Exactly, I want to effectively delete out the Kevin-Steve and want to work with the data associated with Steve-Kevin only. That is where I am stuck now. Thank you!
– A S
Nov 14 at 16:43

add a comment |

So is Kevin-Steve a duplicate that you can effectively delete out of the data, keeping Steve-Kevin?
– BruceWayne
Nov 14 at 16:24

Exactly, I want to effectively delete out the Kevin-Steve and want to work with the data associated with Steve-Kevin only. That is where I am stuck now. Thank you!
– A S
Nov 14 at 16:43

So is Kevin-Steve a duplicate that you can effectively delete out of the data, keeping Steve-Kevin?
– BruceWayne
Nov 14 at 16:24

Exactly, I want to effectively delete out the Kevin-Steve and want to work with the data associated with Steve-Kevin only. That is where I am stuck now. Thank you!
– A S
Nov 14 at 16:43

add a comment |

2 Answers
2

active

oldest

votes

up vote
0
down vote

accepted

Easy with two "helper" columns. Say we have:

enter image description here

In D2 enter:

=IF(A2<B2,A2,B2) & "-" & IF(A2<B2,B2,A2)

and copy downward. (column D shows the data combined in "alphabetic" order)

In E2 enter:

=IF(COUNTIF(D$1:D2,D2)>1,"X","")

and copy downward (column E marks duplicated records):

enter image description here

Now you can filter out those "X" rows.

answered Nov 14 at 17:35

Gary's Student

13.2k31729

Wow Thank you! It worked!
– A S
Nov 14 at 18:50

add a comment |

up vote
0
down vote

Tough one. This formula will provide a numeric index that is the sum of the ASCII value (case sensitive) for the first 4 characters of each pair. Should get you very close to correct sort order.

=CODE(A1)+CODE(B1)+CODE(MID(A1,2,1))+CODE(MID(B1,2,1))+CODE(MID(A1,3,1))+CODE(MID(B1,3,1))+CODE(MID(A1,4,1))+CODE(MID(B1,4,1))

answered Nov 14 at 17:43

Brian

212

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1375372%2fdouble-entry-with-same-info-but-in-two-columns-in-ms-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
0
down vote

accepted

Easy with two "helper" columns. Say we have:

enter image description here

In D2 enter:

=IF(A2<B2,A2,B2) & "-" & IF(A2<B2,B2,A2)

and copy downward. (column D shows the data combined in "alphabetic" order)

In E2 enter:

=IF(COUNTIF(D$1:D2,D2)>1,"X","")

and copy downward (column E marks duplicated records):

enter image description here

Now you can filter out those "X" rows.

answered Nov 14 at 17:35

Gary's Student

13.2k31729

Wow Thank you! It worked!
– A S
Nov 14 at 18:50

add a comment |

up vote
0
down vote

accepted

Easy with two "helper" columns. Say we have:

enter image description here

In D2 enter:

=IF(A2<B2,A2,B2) & "-" & IF(A2<B2,B2,A2)

and copy downward. (column D shows the data combined in "alphabetic" order)

In E2 enter:

=IF(COUNTIF(D$1:D2,D2)>1,"X","")

and copy downward (column E marks duplicated records):

enter image description here

Now you can filter out those "X" rows.

answered Nov 14 at 17:35

Gary's Student

13.2k31729

Wow Thank you! It worked!
– A S
Nov 14 at 18:50

add a comment |

up vote
0
down vote

accepted

Easy with two "helper" columns. Say we have:

enter image description here

In D2 enter:

=IF(A2<B2,A2,B2) & "-" & IF(A2<B2,B2,A2)

and copy downward. (column D shows the data combined in "alphabetic" order)

In E2 enter:

=IF(COUNTIF(D$1:D2,D2)>1,"X","")

and copy downward (column E marks duplicated records):

enter image description here

Now you can filter out those "X" rows.

answered Nov 14 at 17:35

Gary's Student

13.2k31729

Easy with two "helper" columns. Say we have:

enter image description here

In D2 enter:

=IF(A2<B2,A2,B2) & "-" & IF(A2<B2,B2,A2)

and copy downward. (column D shows the data combined in "alphabetic" order)

In E2 enter:

=IF(COUNTIF(D$1:D2,D2)>1,"X","")

and copy downward (column E marks duplicated records):

enter image description here

Now you can filter out those "X" rows.

answered Nov 14 at 17:35

Gary's Student

13.2k31729

answered Nov 14 at 17:35

Gary's Student

13.2k31729

answered Nov 14 at 17:35

Gary's Student

13.2k31729

answered Nov 14 at 17:35

Gary's Student

13.2k31729

Wow Thank you! It worked!
– A S
Nov 14 at 18:50

add a comment |

Wow Thank you! It worked!
– A S
Nov 14 at 18:50

Wow Thank you! It worked!
– A S
Nov 14 at 18:50

add a comment |

up vote
0
down vote

Tough one. This formula will provide a numeric index that is the sum of the ASCII value (case sensitive) for the first 4 characters of each pair. Should get you very close to correct sort order.

=CODE(A1)+CODE(B1)+CODE(MID(A1,2,1))+CODE(MID(B1,2,1))+CODE(MID(A1,3,1))+CODE(MID(B1,3,1))+CODE(MID(A1,4,1))+CODE(MID(B1,4,1))

answered Nov 14 at 17:43

Brian

212

add a comment |

up vote
0
down vote

Tough one. This formula will provide a numeric index that is the sum of the ASCII value (case sensitive) for the first 4 characters of each pair. Should get you very close to correct sort order.

=CODE(A1)+CODE(B1)+CODE(MID(A1,2,1))+CODE(MID(B1,2,1))+CODE(MID(A1,3,1))+CODE(MID(B1,3,1))+CODE(MID(A1,4,1))+CODE(MID(B1,4,1))

answered Nov 14 at 17:43

Brian

212

add a comment |

up vote
0
down vote

Tough one. This formula will provide a numeric index that is the sum of the ASCII value (case sensitive) for the first 4 characters of each pair. Should get you very close to correct sort order.

=CODE(A1)+CODE(B1)+CODE(MID(A1,2,1))+CODE(MID(B1,2,1))+CODE(MID(A1,3,1))+CODE(MID(B1,3,1))+CODE(MID(A1,4,1))+CODE(MID(B1,4,1))

answered Nov 14 at 17:43

Brian

212

Tough one. This formula will provide a numeric index that is the sum of the ASCII value (case sensitive) for the first 4 characters of each pair. Should get you very close to correct sort order.

=CODE(A1)+CODE(B1)+CODE(MID(A1,2,1))+CODE(MID(B1,2,1))+CODE(MID(A1,3,1))+CODE(MID(B1,3,1))+CODE(MID(A1,4,1))+CODE(MID(B1,4,1))

answered Nov 14 at 17:43

Brian

212

answered Nov 14 at 17:43

Brian

212

answered Nov 14 at 17:43

Brian

212

answered Nov 14 at 17:43

Brian

212

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Gfrktyl