Consolidating data from multiple spreadsheets...

[Morgan DeMarco]

I have data in 4 different spreadsheets. My main sheet has data organized as
lastname, firstname and then relevant data accross the row.
for example:

smith, john, 012345, 78914
smith, randy, 014652, 87263

I'd like to combine the data on the other sheets for each person, so that
sheet #2's data for john smith is added after the 78914 cell, and then sheet
#3's data and so on, while removing data from sheet's 2, 3 and 4 that doesn't
correspond to the names in the main sheet... is this possible? And is there
an easy way to do this other than copying and pasting everything into one
sheet and then going line by line and deleting rows that don't match up?...

Any help would be very much appreciated.

 

[JLatham]

There are a couple of ways to attack the problem. One way, that we will just
mention and move on from for now would be to use VBA code to do the work.

The second way would be to consider each of the other sheets as a large
lookup table and work from there. One question: is there some information on
all the sheets that is truly unique to each person such as their social
security number? That's more reliable than looking up by name where you may
have several people with the same name.

If you're going to have to use names, I'd start by inserting a new column A
on each sheet and set up a formula in it to create a full name for each
person. Assuming data starts on row 2 on each sheet, the formula in the new
A2 on each sheet would be:
=B2 & C2
The names will be run together like smithjohn, but that's just fine as long
as all names are spelled the same for each person on all sheets (no exta
spaces or misspellings).

For arguments sake we will say that column F is empty on Sheet1 (main sheet)
and that the other sheets now go out to column H: new column A, LName, FName
and up to 5 items of data. They also go from row 2 to row 101.

In F2 on the main sheet you could put a formula like:
=VLOOKUP(A2,Sheet2!$A$2:$H$101,4,False)
in G2 the formula would be
=VLOOKUP(A2,Sheet2!$A$2:$H$101,5,False)
repeat for H, I and J, increasing the next to last value by 1 in each formula.

At this point you'd start a similar formula in K to refer to the data on
Sheet3, and once those formulas are set up (presumably in K thru O) you'd
finally set up similar formulas beginning in column P for Sheet4. Once
they're set up, just fill them down the main sheet to the end of the list on
it. The data for each person should now be on the main sheet. At this point
the other sheets have to remain in the workbook because they are the source
of the new data on the main sheet.

You could use the COUNTIF() function in a column on Sheets 2, 3 and 4 to
determine if names on those sheets match an entry on the main sheet. On
those other sheets, in an empty column start entering this formula on row 2:
=COUNTIF(Sheet1!A:A,A2)
and fill it down the sheet. Names that don't match entries on Sheet1 will
have a count of zero. If the name appears on the main sheet the count will
be something greater than zero, hopefully just 1 (one). I'll tell you this,
if the count is greater than 1, then the VLOOKUP() formulas will NOT have
returned the results hoped for because they will have stopped at the first
match of the name and won't have seen any duplicate names below the first
one. You can then filter the data on those sheets to just display rows with
Zero in the COUNTIF() column and delete those rows.

Do all of this on a copy of your workbook so that if you or I have made a
boo-boo, you won't have lost any data, especially in that last step of
filtering and deleting rows.

Hope this helps at least a little.

 

[Max]

One way is via index/match

I'll assume that Sheet2 and Sheet3's col C houses the key IDs* -- uniques
which could be used for the matching, and that cols D & E contain the info
that you wish to bring over to the main Sheet1

*IDs are the text numbers in col C in Sheet1 that you posted, viz.:

012345
014652

(it's better to match based on unique numbers rather than using names)

In your main Sheet1,
In E2
=IF(ISNA(MATCH($C2,Sheet2!$C:$C,0)),"",INDEX(Sheet2!D,MATCH($C2,Sheet2!$C:$C,0)))
Copy E2 to F2. This'll bring in cols D & E from Sheet2.

In G2
=IF(ISNA(MATCH($C2,Sheet3!$C:$C,0)),"",INDEX(Sheet3!D,MATCH($C2,Sheet3!$C:$C,0)))
Copy G2 to H2. This'll bring in cols D & E from Sheet3.
Then just select E2:H2, copy down as far as required.
Unmatched cases, if any, will return blanks: ""

---