Excel 2010 Windows 7 - create new list from different data sets

[CYDSRQ]

Thank you for a great resource! I have combed through the posts on combining data, but could not find one that addressed this issue:

Data set 1 includes fname, lname, and 10 other fields;

Fname	Lname	Gender	Race	Data1	Data2	Data3	Data4	Data5
Jane	Doe	F	W	xxx	xx
Sam	Roberts	F	W	xx	xx
John	Jones	M	B	x	x

<tbody>
</tbody>

Data set 2 has fname, lname, and 4 different fields (not in the other set).

Fname	Lname	Gender	Race	DOB	EventsAttended	Active
Jane	Doe	M	W	Xx/xx/xxxx	xx	Y

<tbody>
</tbody>

I want to combine the lists so all data from Jane Doe appears in one record.

Fname	Lname	Gender	Race	Data1	Data2	Data3	Data4	Data5	DOB	Active
Jane	Doe	F	W	xxx	xx				Xx/xx/xxxx	Y
John	Jones	M	B	xx	x	x	x	x	Xx/xx/xxx	N

<tbody>
</tbody>

NOTE: Some names might not be on both lists

I am not that proficient with Excel, but can pick up quickly. Thank you for your help! Karen

 

[Hercules1946]

I think that we are getting somewhere.
The problem is that I tried to give you a formula that would adjust itsself when copy/pasted. But.... The first time that you place it in the sheet it has to be adjusted, because obviously I don't know which will be the first cell.
Once the first cell is working you can the copy and paste to other locations. I didn't use the handle approach because I only put formulae in the empty places that were missing info.
OK can I just confirm - the sheet your working on is from post #11 with the sheets 1A and 1B ? If so get Sheet 1A ready and I will give instructions on what to do. I can see that the one you have in C3 isn't right so that will be the problem.

 

[Hercules1946]

Hi
Heres some instructions to try again from scratch on my example:

Assuming you are sitting with Sheet1A in front of you, copy the formula below into Cell C3. (the "gender" column)
It should give you a #VALUE! error.

=INDEX(C$18:C$27,MATCH($A3&$B3,$A$18:$A$27&$B$18:$B$27,0))

Click in the formula bar, and hit Ctrl+Shift+Enter. If alls well the error will disappear and be replaced with an F from C19.
In the formula bar you should now have the curly brackets as well.
If you look closely at the formula you can see its INDEXING C18:C27 (Gender list) ;
Its matching one combined name A3&B3 with the combined name block A18:B27 set 2
Whichever row is the same as A3&B3, it then returns that row in Col C (C19 = "F" )

With one formula working, save the spreadsheet
Next copy from C3 to C11 (no need for C+S+E) - If thats OK paste into the other yellow cells.

Another tip:
Click the Formula Ribbon and then click C3. On the Ribbon Click Evaluate Formula.
A window opens to take you through what excel does with a formula. Click Evaluate to see the steps.

Let me know how you get on. If you can't get that first cell sorted, wait for me, Ill be online tomorrow.

 

[CYDSRQ]

Hello - thank you so much for your patience with this. I copied your data from post #8 - the formula worked fine. I then opened the sample data I had sent to you - it did not work, returning "N/A". The tip on the "evaluate formula" was interesting - it helped me see what the data was.... I checked to see that the cell references matched the data set - still N/A

 

[Hercules1946]

Hello - thank you so much for your patience with this. I copied your data from post #8 - the formula worked fine.

So on Sheet1A you copied the formula to all the yellow cells starting with C3 and it worked OK?

 

[CYDSRQ]

yes

 

[Hercules1946]

Thats good news then. Now lets turn to the NameNumber example the one wirh 32 rows. get that on the screen and look at Cell C5 (If unaltered from mine it will say #N/A.
The formula in C5 should be: {=INDEX(C$23:C$32,MATCH($A5&$B5,$A$23:$A$32&$B$23:$B$32,0))}
The Names in A5 and B5 should be Brynn Adams.
If unaltered, A24 and B24 should be BRYNN ADAMS.

If thats all correct,
Copy Cells A5:B5, Click A24 and paste.

 

[CYDSRQ]

That worked!!! Let me copy all names from A3:B3 to A16:B16 and see if that changes anything

 

[CYDSRQ]

I mean where there is a match

 

[CYDSRQ]

Ok - so that worked. I went back to the document that I copied NameNumber page from.... it did not work until I copied the name Brynn Adams from A5:B5 and pasted to the lower set of data.... ???? Then it returned StudentNumber....???

 

[Hercules1946]

So what that means is that although you tried to correct the upper/lower case problem ther was still a difference. The slightest thing will mmake it fail.
When will your main data be ready ? If you can give me the cell ranges I can adjust the formula to suit and make it a bit easier for you.

If you have some examples where both are lower case and you still get N/A I'd like to see them and Ill try to figure it out.