Question for excel gurus

MKNiner · February 7, 2014, 4:27am

Need some help from someone who knows their excel.

[font=verdana][size=2px]I’ve[/size][size=5] got two sets of data in an excel sheet.[/size][/font]

[font=verdana][size=5]Set 1 is in columns A and B of an excel sheet (A=last name, B=first name)[/size][/font]

[font=verdana][size=5]Set 2 is in columns D and E of the same sheet (D=last name, E=first name)[/size][/font]

[font=verdana][size=5]I’m trying to figure out how many names that appear in the first set also appear in the second set. One set of names is about 800 names, the other set is about 2000 names. So I want to know how many of the 800 appear in the set of 2000.

Any help someone can give is much appreciated. [/size][/font]

Normmm · February 7, 2014, 5:58am

Try:

=SUMPRODUCT(–($C$1:$C$2000=A1),–($D$1:$D$2000=B1))

Then copy the formula down to 800.

[font=Verdana]=SUMPRODUCT(–($C$1:$C$2000=A1),–($D$1:$D$2000=B1))[/font]
[font=Verdana]=SUMPRODUCT(–($C$1:$C$2000=A2),–($D$1:$D$2000=B2))[/font]
[font=Verdana]=SUMPRODUCT(–($C$1:$C$2000=A3),–($D$1:$D$2000=B3))[/font]
[font=Verdana]etc…[/font]

[font=Verdana]A =countif(x,y) might work too. But I think the sumproduct should get it for you.[/font]

49r9r · February 7, 2014, 6:04am

MK, is your kid doing this for an assignment in school? This way should work, but I’m sure there is an easier way.

I would merge the data, then sort, and then do a subtotal of the count for each name. This is one way: Copy the data in the first 2 columns and paste into 2 new columns (you may want to start a new sheet). Note: When specifying the data to copy, make sure you only copy from the first row of the data for these 2 columns to the last row of data and do not highlight the entire columns. Then copy and paste the data from the other 2 columns onto the end of the 2 new columns. Then sort by last name/first name. This should group any duplicate names together. Add a count field or a subtotal function to give the count for the number of times each name combination appears. I think you can also set up a pivot table to give you the count.

Edit: I just noticed Normmmmm’s post. Either way should work.

system · February 7, 2014, 11:39am

clt recommends clt alt delete.

Honest_Niner · February 7, 2014, 12:16pm

A pivot table should do it.

cibik02 · February 7, 2014, 12:38pm

Nvmd

MKNiner · February 7, 2014, 1:09pm

[quote=“49r9r, post:3, topic:28586”]MK, is your kid doing this for an assignment in school? This way should work, but I’m sure there is an easier way.

I would merge the data, then sort, and then do a subtotal of the count for each name. This is one way: Copy the data in the first 2 columns and paste into 2 new columns (you may want to start a new sheet). Note: When specifying the data to copy, make sure you only copy from the first row of the data for these 2 columns to the last row of data and do not highlight the entire columns. Then copy and paste the data from the other 2 columns onto the end of the 2 new columns. Then sort by last name/first name. This should group any duplicate names together. Add a count field or a subtotal function to give the count for the number of times each name combination appears. I think you can also set up a pivot table to give you the count.

Edit: I just noticed Normmmmm’s post. Either way should work.[/quote]

Actually it’s my own assignment. LOL!

I did the pivot table and everything is grouped. Though I’m not sure how to add the count function that was mentioned to determine the number of times the same first name appears in the last name grouping. How do I do that?

Yes, I’m pretty excel illiterate.

C49er · February 7, 2014, 2:38pm

MK,

Here is what comes to mind. Let me know if you have any questions or if I’ve misunderstood.

If you have two sets of data (we’ll call set A and set B). Each set has a first name and a last name for each person. You want to see how many of the names (both first and last name) from set A appear in set B?

What I would do for this is:

[ul][li]To start, I would insert a column after C to give yourself some room.[/li]
[li]Then, CONCATENATE the first and last names for each set. So, in Cell C1, =CONCATENATE(A1," ",B1) and in Cell G1, =CONCATENATE(E1," ",F1) (Fill the formulas down)[/li]
[li]Now, you need to see if the name that is in Column C is in Column F. To do this, in Cell D1, =IF(ISNA(MATCH(C1,F:F,0)),"",1) (Fill the formula down)[/li]
[li]If the name in Set B is in Set A, the output will be a 1. Otherwise, it will be a blank.[/li]
[li]Then, you can do a simple count of Column D. Everywhere there is a 1, that means the name was a match. =COUNT(G:G)[/li][/ul]

C49er · February 7, 2014, 2:41pm

[table]
[tr][td]Column A[/td][td]Column B[/td][td]Column C[/td][td]Column D[/td][td][/td][td]Column E[/td][td]Column F[/td][td]Column G[/td][/tr]
[tr][td]Jamie[/td][td]Miller[/td][td]Jamie Miller[/td][td]1[/td][td][/td][td]Jamie[/td][td]Miller[/td][td]Jamie Miller[/td][/tr]
[tr][td]Adam[/td][td]Smith[/td][td]Adam Smith[/td][td][/td][td][/td][td]Ted[/td][td]Wilson[/td][td]Ted Wilson[/td][/tr]
[tr][td]Jason[/td][td]Brown[/td][td]Jason Brown[/td][td][/td][td][/td][td]Joani[/td][td]Merritt[/td][td]Joani Merritt[/td][/tr]
[tr][td]Ted[/td][td]Wilson[/td][td]Ted Wilson[/td][td]1[/td][td][/td][td]Wendy[/td][td]Emerson[/td][td]Wendy Emerson[/td][/tr]
[tr][td]Amanda[/td][td]Johnson[/td][td]Amanda Johnson[/td][td][/td][td][/td][td]James[/td][td]Dean[/td][td]James Dean[/td][/tr]
[tr][td]Katie[/td][td]Tyler[/td][td]Katie Tyler[/td][td]1[/td][td][/td][td]Billy[/td][td]Bryan[/td][td]Billy Bryan[/td][/tr]
[tr][td]Emily[/td][td]White[/td][td]Emily White[/td][td]1[/td][td][/td][td]Peter[/td][td]Pan[/td][td]Peter Pan[/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td]Emily[/td][td]White[/td][td]Emily White[/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td]Katie[/td][td]Tyler[/td][td]Katie Tyler[/td][/tr]
[/table]

C49er · February 7, 2014, 2:57pm

[table]
[tr][td]Column A[/td][td]Column B[/td][td]Column C[/td][td]Column D[/td][td][/td][td]Column E[/td][td]Column F[/td][td]Column G[/td][/tr]
[tr][td]Jamie[/td][td]Miller[/td][td]=CONCATENATE(A1," “,B1)[/td][td]=IF(ISNA(MATCH(C1,H:H,0)),”",1)[/td][td][/td][td]Jamie[/td][td]Miller[/td][td]=CONCATENATE(F1," “,G1)[/td][/tr]
[tr][td]Adam[/td][td]Smith[/td][td]=CONCATENATE(A2,” “,B2)[/td][td]=IF(ISNA(MATCH(C2,H:H,0)),”",1)[/td][td][/td][td]Ted[/td][td]Wilson[/td][td]=CONCATENATE(F2," “,G2)[/td][/tr]
[tr][td]Jason[/td][td]Brown[/td][td]=CONCATENATE(A3,” “,B3)[/td][td]=IF(ISNA(MATCH(C3,H:H,0)),”",1)[/td][td][/td][td]Joani[/td][td]Merritt[/td][td]=CONCATENATE(F3," “,G3)[/td][/tr]
[tr][td]Ted[/td][td]Wilson[/td][td]=CONCATENATE(A4,” “,B4)[/td][td]=IF(ISNA(MATCH(C4,H:H,0)),”",1)[/td][td][/td][td]Wendy[/td][td]Emerson[/td][td]=CONCATENATE(F4," “,G4)[/td][/tr]
[tr][td]Amanda[/td][td]Johnson[/td][td]=CONCATENATE(A5,” “,B5)[/td][td]=IF(ISNA(MATCH(C5,H:H,0)),”",1)[/td][td][/td][td]James[/td][td]Dean[/td][td]=CONCATENATE(F5," “,G5)[/td][/tr]
[tr][td]Katie[/td][td]Tyler[/td][td]=CONCATENATE(A6,” “,B6)[/td][td]=IF(ISNA(MATCH(C6,H:H,0)),”",1)[/td][td][/td][td]Billy[/td][td]Bryan[/td][td]=CONCATENATE(F6," “,G6)[/td][/tr]
[tr][td]Emily[/td][td]White[/td][td]=CONCATENATE(A7,” “,B7)[/td][td]=IF(ISNA(MATCH(C7,H:H,0)),”",1)[/td][td][/td][td]Peter[/td][td]Pan[/td][td]=CONCATENATE(F7," “,G7)[/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td]Emily[/td][td]White[/td][td]=CONCATENATE(F8,” “,G8)[/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td]Katie[/td][td]Tyler[/td][td]=CONCATENATE(F9,” ",G9)[/td][/tr]
[/table]

MKNiner · February 7, 2014, 6:46pm

[quote=“C49er, post:8, topic:28586”]MK,

Here is what comes to mind. Let me know if you have any questions or if I’ve misunderstood.

If you have two sets of data (we’ll call set A and set B). Each set has a first name and a last name for each person. You want to see how many of the names (both first and last name) from set A appear in set B?

What I would do for this is:

[ul][li]To start, I would insert a column after C to give yourself some room.[/li]
[li]Then, CONCATENATE the first and last names for each set. So, in Cell C1, =CONCATENATE(A1," ",B1) and in Cell G1, =CONCATENATE(E1," ",F1) (Fill the formulas down)[/li]
[li]Now, you need to see if the name that is in Column C is in Column F. To do this, in Cell D1, =IF(ISNA(MATCH(C1,F:F,0)),"",1) (Fill the formula down)[/li]
[li]If the name in Set B is in Set A, the output will be a 1. Otherwise, it will be a blank.[/li]
[li]Then, you can do a simple count of Column D. Everywhere there is a 1, that means the name was a match. =COUNT(G:G)[/li][/ul][/quote]

you da man C!!! Thanks man. Worked perfect.