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]

No problem - Take as long as you need and remember if you have any problems Im here to help!

Hello Karen
Heres a link to the workbook. It might be easier for you to work with.

https://app.box.com/s/7v3nweds6czn6ccz2shm

sheet1a shows data missing, sheet1b shows data added.

HTH
Hercules

 

[CYDSRQ]

hello! Can you tell me how to import a view of the spreadsheet like you did earlier? I am receiving "N/A" and I believe I have followed your instructions... if I could import a view of the spreadsheet, perhaps you could find my error? Thank you! Karen

 

[Hercules1946]

hello! Can you tell me how to import a view of the spreadsheet like you did earlier? I am receiving "N/A" and I believe I have followed your instructions... if I could import a view of the spreadsheet, perhaps you could find my error? Thank you! Karen

Is that using my link in post #11? If so,you go to www.box.com, register and then upload your spreadsheet. Its free if your not bothered about adverts, and you dont need a lot of storage. Once youve uploaded you take the share option and it will give you the code to paste in your message.

If your still having problems check your PMs in a few minutes

Hercules

 

[CYDSRQ]

I could not open the box.com link - it is blocked by the security software... will wait for PM Thanks!

 

[Hercules1946]

I could not open the box.com link - it is blocked by the security software... will wait for PM Thanks!

You should have it by now

 

[Hercules1946]

Hello Karen
I think Ive found the problem. The matching process works by comparing the first and last mames ifor equality between the two data sets. It can't match anything
because one set is upper case and the other lower. If you look at my example you will see that where I have altered the bottom table to lower case, its finding a match
except where the bottom table has different names. Because the same tests are used for every cell, the problem prevents any matching. You can have all upper or all
lower, but not one of each!

Excel 2010
	A	B	C	D	E	F	G
1
2	FName	Lname	StudentNumber	#ofEvents	Active	ZipCode
3	Mariangel	Acosta	#N/A	1	Yes	34240
4	Susan	Acosta	#N/A	3	Yes	34240
5	Brynn	Adams	N330494	9	Yes	34287
6	Akiyah	Agel	#N/A	4	Yes	34234
7	Jonathan	Aguilar	#N/A	1	No
8	Laurdes	Aguilera	#N/A	1	Yes	34286
9	Hannah	Ailshire	#N/A	1	Yes	34287
10	Dustin	Alexander	#N/A	2	Yes	34287
11	Jasmine	Alexander	#N/A	2	Yes	34239
12	Symphony	Alexander	N342177	1	Yes	34288
13	Mosley	Alexandria	#N/A	5	Yes	34232
14	Giulia	Alledi	#N/A	2	No	34229
15	Antonia	Allen	N371406	10	Yes	34243
16	Courtavia	Allen	N353842	7	Yes	34234
17
18
19
20
21
22	FName	Lname	StudentNumber	#ofEvents	Active	ZipCode
23	TY	ABBOTT	N401388
24	Brynn	Adams	N330494
25	ALEXIS	ALBERO	N366056
26	ANTHONY	ALBRECHT	N394986
27	Symphony	Alexander	N342177
28	Antonia	Allen	N371406
29	Courtavia	Allen	N353842
30	DEVON	ALLEN	N094146
31	JEREN	ALMEIDA	N181820
32	MARTIN	ALTMANN	N120767
33
NameNumber

 

[CYDSRQ]

Such an obvious problem! Thanks for finding it - I will work on the rest and see if I can move ahead with your formula. One more question - the "0" at the end of the formula - is that to indicate to return value to current cell?

 

[CYDSRQ]

Ok, I tried to replicate that in my file, but it returns "N/A" - I replaced text so they matched to all Capitals, and then to Sentence Case and it did not change the results..... can I use the handle to copy the formula to all of the cells? Or do I need to enter in each lookup cell?

 

[Hercules1946]

Ok, I tried to replicate that in my file, but it returns "N/A" - I replaced text so they matched to all Capitals, and then to Sentence Case and it did not change the results..... can I use the handle to copy the formula to all of the cells? Or do I need to enter in each lookup cell?

Not sure what you mean by the handle ??
1 To check its not the names, just ensure that where is the same name in both sets that they are identical (FName and LName.)
2. Pick a cell with a formula that says N/A, click on it, and look at the formula bar to see the formula. Write down this formula and type it into your reply to me and tell me which cell its in. make sure you type it correctly.
3. Did you try putting the corrections into the examples you sent me and did that work?
4 I think I will need to see some of your actual file

 

[CYDSRQ]

after I changed the font so that all names matched (sentence case), I put the formula from your earlier post in cell C3 =INDEX(C$23:C$32,MATCH($A3&$B3,$A$23:$A$32&$B$23:$B$32,0)) . . . Next, I put cursor in the formula bar and hit "Ctrl" "Shift" and "enter" to get the brackets {} to appear around the formula. The "handle" is the small black box that appear in the lower right hand corner of the cell - when you click and drag down or across, it will copy the contents (or the formula) into the adjacent cells. I used this method to copy into the cells between C4 and C16. Here is the formula as it appears in cell C5 =INDEX(C$23:C$32,MATCH($A5&$B5,$A$23:$A$32&$B$23:$B$32,0)) That returned the "N/a". I am using the same set of data that I sent to you to see if I could replicate before trying with the original file.