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

 

[CYDSRQ]

Hercules - I really appreciate your help on this, but I cannot send the entire file - it is confidential data. It seems your assessment is correct - that there is a formatting issue - can text be formatted? When I used the error checking features, it showed that it pulled the right data (names from the lower set of data) but would not return the final piece (the StudentNumber) I appreciate any other ideas you have..... Thanks again! Karen

 

[Hercules1946]

I sussed it out from you example!!!!!!!!
In the bottom table of NameNumber the names in A and B are formatted differently from the top table
in the bottom the cells are a fixed number of characters with spaces at the end (A= 12Chrs, B =17 chrs)
in the top theres no spaces so the computer is trying to match "ANTONIA^^^^^ALLEN^^^^^^^^^^^^" With: "ANTONIAALLEN"

 

[CYDSRQ]

exactly!!!! I am just working to reformat in Word then bring back --- will report back!!! Thank you so much!!!

 

[Hercules1946]

There is no need to send the whole file, and I think we have explained it now. To see the problem:

Paste this into cell G28 and then copy to G15: =A28&B28 Double click at the top of col G to resize the column width.
It should be possible to modify the formula to adjust for it.

 

[CYDSRQ]

YES!!! That worked!!!! now, for the real data..... I will keep you posted as I begin working on the entire spreadsheet. I am so thankful for your willingness to work on this!! Many thanks - I'll keep you posted on the progress, and I hope you don't mind if I call on you with other questions..... ??? Thanks - Karen

 

[Hercules1946]

Karen
Ive adjusted the formula so that it will work with the longer names and upper case. Copy this into C3 0f NameNumber, click in the formula and press Ctrl+Shift+Enter.
Then copy it down column C top table and that should work. However, it isn't foolproof if people devise new ideas with text!
We will still need to adjust it for whichever is the first cell on your main data, and if you need my help with that, Ill need to know the cell addresses of your tables.

Copy this into C3 and down: =INDEX(C$23:C$32,MATCH($A3&" "&$B3,TRIM(PROPER($A$23:$A$32&$B$23:$B$32)),0))

 

[Hercules1946]

YES!!! That worked!!!! now, for the real data..... I will keep you posted as I begin working on the entire spreadsheet. I am so thankful for your willingness to work on this!! Many thanks - I'll keep you posted on the progress, and I hope you don't mind if I call on you with other questions..... ??? Thanks - Karen

No of course I dont mind - after all if you cant get it working to your satisfaction then my time is wasted!

 

[CYDSRQ]

Hercules - I have been able to use the formula for every aspect of the data I need to compare - am running Pivot Tables now to analyze. Thank you again!!

 

[Hercules1946]

Hercules - I have been able to use the formula for every aspect of the data I need to compare - am running Pivot Tables now to analyze. Thank you again!!

That great! I know you had some concerns about the pivot tables. They aren't a strong point with me, so I hope they are OK.
Looks like we made it in the end. Thanks for letting me know.