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

CYDSRQ

New Member
Joined
Nov 26, 2013
Messages
35
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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. :)
 
Upvote 0
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.
 
Upvote 0
Thank you - I think it's clear, but I don't know what INDEX/MATCH refers to.... sorry I'm a newbie to this! :)
 
Upvote 0
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......
 
Upvote 0
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
 
Upvote 0
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
ABCDEFGHI
1Data Set 1
2FnameLnameGenderRaceDOBEmailEvents Att.ActiveData2
3JaneDoeFW14/08/1960Jane@MyEmail.com6Y718 Shropshire Boulevard
4SamRobertsFW20/01/1984Sam@MyEmail.com2Y1705 Shropshire Boulevard
5JohnJonesMB17/01/1975John@MyEmail.com0N605 Shropshire Boulevard
6RoselynMillhouseFW09/09/1991Roselyn@MyEmail.com3Y630 Shropshire Boulevard
7PatrickSmithMW12/02/1987Patrick@MyEmail.com0Y1057 Shropshire Boulevard
8LatoshaSagucioFB11/01/1978Latosha@MyEmail.com1N1615 Shropshire Boulevard
9BlossomWingfieldFB03/12/1967Blossom@MyEmail.com4Y710 Shropshire Boulevard
10TereasaFullenFW14/12/1982Tereasa@MyEmail.com2N1431 Shropshire Boulevard
11TamiTremblayFB12/02/1946Tami@MyEmail.com1N197 Shropshire Boulevard
12SherrySmithFW17/06/1995Sherry@MyEmail.com13Y1018 Shropshire Boulevard
13
14
15
16Data Set 2Events
17FnameLnameGenderRaceDOBEmailAttendedActive
18TamiTremblayFB12/02/19461N
19JaneDoeFW14/08/1960Jane@MyEmail.com6Y
20BlossomWingfieldFB03/12/19674Y
21JohnJonesMB17/01/1975John@MyEmail.com0N
22TereasaFullenFW14/12/19822N
23RoselynMillhouseFW09/09/19913Y
24SamRobertsFW20/01/1984Sam@MyEmail.com2Y
25LatoshaSagucioFB11/01/19781N
26PatrickSmithMW12/02/19870Y
27SherrySmithFW17/06/199513Y
Sheet1
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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! :)
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top