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]

In your Data set 2, Its probably a typo, but begs a question. As you have some of the same data in both lists, what would you want to do if they conflict ?
I think you should be able to resolve with INDEX/MATCH, but first you need to produce a list of unique headings for set 1, that include all the extra columns that could be found
in set 2.
Then ensure that every heading in Set 2 exists in set 1 and place a formula in the Set 1 table (where data is absent)
to check for information in Set 2.

I hope Ive explained that clearly.

 

[Jeffrey Green]

I am not an MVP or anything, but I would do this, what I call, "the poor man's" way.

Take the names on the second list of people and paste it to the bottom of list 1.
take the fields on list 2 and add them to list 1.
do a VLOOKUP to look the values in all missing fields.

Obviously the first time fields will be blank for the names added to the bottom, since they weren't on the list originally.

again, just a poor man's way of doing things.

 

[CYDSRQ]

Thank you - I think it's clear, but I don't know what INDEX/MATCH refers to.... sorry I'm a newbie to this!

 

[CYDSRQ]

Thanks - I will look at this. I don't know much about VLOOKUP - will that keep the data in the records as I need to manipulate it after I get the complete set of data......

 

[Hercules1946]

Thank you - I think it's clear, but I don't know what INDEX/MATCH refers to.... sorry I'm a newbie to this!

If you can give me a bit of time, I'll put something together using INDEX/MATCH, and post the formula.

 

[CYDSRQ]

Hi Hercules1946 - thank you ! I wanted to add that after I have the one list, I need to manipulate the data (pivot tables) -- another post suggested using VLOOKUP and after researching, that would not allow me to work with the data. Thanks for any assistance! Karen

 

[Hercules1946]

Karen
Here is my example. I was hoping to post some images but I cant work out how to do it. Heres a shot of the sample workbook. Ive assumed that we can extend the first data set, but you can
create an entirely new one if you like. Ive added the extra columns from Set 2 to it, and the formulae are a bit easier to organise if the columns you want to work with are common to both sets,
(e.g) DOB data is in Col E for both sets.
The formula is set for entry in Cell C3 which in the example is the first cell where we need to search in Set 2 for extra info. All such cells are coloured green, and Ive included some examples
of the formulae in the screenshot. The formula is an array one and when you copy the first one into your sheet, you need to do an extra couple of steps
1. Click in the formula bar to put the formula in edit mode of press F2 while the cell is selected.
2. Click the key combination CTRL+SHIFT+ENTER (All at the same time)

If alls well you will see that Excel has placed {} characters around the formula. Before doing this it will probably say #VALUE!, but this
will be replaced by the correct info after step 2 above.
When this is completed it can then be copied to any of the other green cells and it will be adjusted to suit.

Heres the formula: =INDEX(C$18:C$27,MATCH($A3&$B3,$A$18:$A$27&$B$18:$B$27,0)) (Set for C3)

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Data Set 1
2	Fname	Lname	Gender	Race	DOB	Email	Events Att.	Active	Data2
3	Jane	Doe	F	W	14/08/1960	Jane@MyEmail.com	6	Y	718 Shropshire Boulevard
4	Sam	Roberts	F	W	20/01/1984	Sam@MyEmail.com	2	Y	1705 Shropshire Boulevard
5	John	Jones	M	B	17/01/1975	John@MyEmail.com	0	N	605 Shropshire Boulevard
6	Roselyn	Millhouse	F	W	09/09/1991	Roselyn@MyEmail.com	3	Y	630 Shropshire Boulevard
7	Patrick	Smith	M	W	12/02/1987	Patrick@MyEmail.com	0	Y	1057 Shropshire Boulevard
8	Latosha	Sagucio	F	B	11/01/1978	Latosha@MyEmail.com	1	N	1615 Shropshire Boulevard
9	Blossom	Wingfield	F	B	03/12/1967	Blossom@MyEmail.com	4	Y	710 Shropshire Boulevard
10	Tereasa	Fullen	F	W	14/12/1982	Tereasa@MyEmail.com	2	N	1431 Shropshire Boulevard
11	Tami	Tremblay	F	B	12/02/1946	Tami@MyEmail.com	1	N	197 Shropshire Boulevard
12	Sherry	Smith	F	W	17/06/1995	Sherry@MyEmail.com	13	Y	1018 Shropshire Boulevard
13
14
15
16	Data Set 2						Events
17	Fname	Lname	Gender	Race	DOB	Email	Attended	Active
18	Tami	Tremblay	F	B	12/02/1946		1	N
19	Jane	Doe	F	W	14/08/1960	Jane@MyEmail.com	6	Y
20	Blossom	Wingfield	F	B	03/12/1967		4	Y
21	John	Jones	M	B	17/01/1975	John@MyEmail.com	0	N
22	Tereasa	Fullen	F	W	14/12/1982		2	N
23	Roselyn	Millhouse	F	W	09/09/1991		3	Y
24	Sam	Roberts	F	W	20/01/1984	Sam@MyEmail.com	2	Y
25	Latosha	Sagucio	F	B	11/01/1978		1	N
26	Patrick	Smith	M	W	12/02/1987		0	Y
27	Sherry	Smith	F	W	17/06/1995		13	Y
Sheet1

Cell Formulas
Range	Formula
C3	{=INDEX(C$18:C$27,MATCH($A3&$B3,$A$18:$A$27&$B$18:$B$27,0))}
E3	{=INDEX(E$18:E$27,MATCH($A3&$B3,$A$18:$A$27&$B$18:$B$27,0))}
F3	{=INDEX(F$18:F$27,MATCH($A3&$B3,$A$18:$A$27&$B$18:$B$27,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Obviously, the formula will also need to be amended to suit the size of your data set, but don't worry too much at this stage, as Im here to
help with that. Set it up as an example first to get used to how it works and let me know how you get on.

 

[CYDSRQ]

THANK YOU SO MUCH!!!! I will try it and let you know what happens..... we are off for the next few days, so please be patient with my response as it may be next week. Many thanks again!!Karen

 

[Hercules1946]

THANK YOU SO MUCH!!!! I will try it and let you know what happens..... we are off for the next few days, so please be patient with my response as it may be next week. Many thanks again!!Karen

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