Combining Like Data in Excel

So here is my situation: I need to take two spreadsheets in excel and combine the data together so that any additional data is paired up with common data between the cells. Here's an example of what I mean.

Sheet 1
1234567, JOHN, DOE, 1234567.JPG

Sheet 2
JOHN, DOE, 6634

First and Last names are common data, but the number in the second sheet does not exist in the first. The user list in both sheets are slightly different from each other so I can't simply alphabetize the names and move the additional column over. I have about 500 users to go through and may have to use what ever solution I come up with for similar lists of users.

Any assistance would be great.

Answers


There's various techniques you can use to combine data but you'll have to be a bit more specific. For example is there a fixed number of columns that sheet 1 doesn't have that sheet 2 does?

The basic technique would be to create some sort of unique identifier, perhaps by concatanating the names together in both sheets? that way you can use VLOOKUP to put all the missing data in one sheet into the other


Not sure I understood "I cant alphabetize the names". However, if the names have the same spelling i.e John is John in both sheets you can concatenate John and Doe in sheet 1 and do same in sheet 2 and use a vlookup function. Something like

A=cellcontainingJohn&CellcontainingDoe in sheet1 B=cellcontainingJohn&CellcontainingDoe in sheet2

C= Vlookup(A,rangeforB,columnnumber)


Here's what I would do:

Select the sheet into which you want to pull data from the other. I'll assume we're pulling data from sheet 2 into sheet 1.

In sheet 2, insert a column to the left of what you have already. JOHN is now in column B, DOE in column C, and 6634 in column D.

In sheet 2, column A, row 2 (assuming you have a row of column headers) which is currently empty, use the formula

=CONCATENATE(B2,C2)

Now, head back over to sheet 1. Let's assume you also have a row of column headers in sheet 1, so the cell immediately to the right of your 1234567.jpb is E2 and it's empty. In E2, use the following formula

=IFERROR(VLOOKUP(B2&C2,'Sheet 2'!$A:$D,4,FALSE),"")

That should give you what you're asking for, if I understand your question correctly.


Need Your Help

Are self-described / auto-descriptive services loosely or tightly coupled in a SOA architecture?

web-services soa esb loose-coupling tightly-coupled-code

I consider a self-described / auto-descriptive service as a good thing in a SOA architecture, since (almost) everything you know to call the service is present in the service contract (such a WSDL).

Auto-creating User details with Grails and LDAP

java grails ldap spring-security

I'm using the Acegi Security plugin for Grails, and authentication via LDAP.