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:
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
31
add a comment |
up vote
0
down vote
favorite
I have a big database where the data is like this:
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
31
So isKevin-Stevea duplicate that you can effectively delete out of the data, keepingSteve-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
up vote
0
down vote
favorite
I have a big database where the data is like this:
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
31
I have a big database where the data is like this:
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
microsoft-excel microsoft-excel-2016
edited Nov 14 at 16:44
Worthwelle
2,1832724
asked Nov 14 at 16:08
A S
31
edited Nov 14 at 16:44
Worthwelle
2,1832724
asked Nov 14 at 16:08
A S
31
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
2,1832724
asked Nov 14 at 16:08
A S
31
asked Nov 14 at 16:08
A S
31
asked Nov 14 at 16:08
A S
31
31
So isKevin-Stevea duplicate that you can effectively delete out of the data, keepingSteve-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 isKevin-Stevea duplicate that you can effectively delete out of the data, keepingSteve-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
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
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:
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):
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 |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
Easy with two "helper" columns. Say we have:
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):
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:
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):
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
up vote
0
down vote
accepted
Easy with two "helper" columns. Say we have:
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):
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:
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):
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
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
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
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
212
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So is
Kevin-Stevea duplicate that you can effectively delete out of the data, keepingSteve-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