SOLVED

# Combine Matching Rows in two spreadsheets

Occasional Contributor

# Combine Matching Rows in two spreadsheets

In Excel how do you combine like rows in two different spreadsheets?

Say spreadsheet one has three columns A. First Name, B. Last Name, and C. Town of Residence.

Spreadsheet two has three columns: A. Phone number, B. First Name, and C, Last Name.

I would like to match on First and Last Names and add phone number to column D of spreadsheet one.   Thanks, Joel.

Device: PC/Windows 10

Microsoft Office Home and Business 2013

Excel Product ID: 00196-20943-85146-AA714

18 Replies

# Re: Combine Matching Rows in two spreadsheets

Thanks L z. See my edited message above. Hope that's what you need. Joel
best response confirmed by JoelBauer (Occasional Contributor)
Solution

# Re: Combine Matching Rows in two spreadsheets

A small representative sample would have been appreciated

With Table1 in one sheet, Table2 in another. In Table1 cell D3:

``````=IF(COUNTIFS(Table2[First Name],A3, Table2[Last Name],B3),
INDEX(Table2[Phone number],
AGGREGATE(15,6,ROW(Table2[Last Name])-ROW(Table2[#Headers]) / COUNTIFS(A3,Table2[First Name], B3,Table2[Last Name]),1)
),
)``````

Corresponding sample attached

# Re: Combine Matching Rows in two spreadsheets

Thanks L z.
I appreciate and I am somewhat in awe of your efforts. Also in awe of all the capabilities of excel that I do not know. I am going to play with your formula and see if it works for me.
Does all data have to be in ‘Tables’ for this formula to work? (Hope you understand the question.)
I am a one day member of this ‘forum’. I don’t know much. For instance, how did you make and post this sample? (Your posted samples are right to the point).
Is there documentation on using this ‘forum’?
Thanks again, Joel

# Re: Combine Matching Rows in two spreadsheets

Does all data have to be in ‘Tables’ for this formula to work? (Hope you understand the question.)

This can work with Ranges instead of Tables. However, Tables makes it easier/more clear and have a number of other advantages. I suggest to take a few minutes to read Use Tables not Ranges

how did you make and post this sample?

When you create a post/reply, at the bottom of the window there's:

Is there documentation on using this ‘forum’?
Not that I'm aware of. IHMO the User Interface is quite intuitive, it's all about reading

# Re: Combine Matching Rows in two spreadsheets

Next Question: How do I get your formula "=(IF(COUNTFS(Table2...." into cell D3? When I cut and paste it goes into manty cells. I cannot even type it in your nicely structured format. Thanks Joel

# Re: Combine Matching Rows in two spreadsheets

Copy/Paste or Cut/Paste cells is a source of issues when you don't know exactly what you do/want to do. So, I suggest you forget about this method for now

Assuming you opted for Tables (instead of ranges) and your Table1 starts in row 2 (the Table header) as in the sample I shared:

- In my sample: Go to cell D3

- Click in the Excel formula bar > Ctrl+A > Ctrl+C

- Go to Table1, cell D3

- Click in the Excel formula bar > Ctrl+V > Enter

# Re: Combine Matching Rows in two spreadsheets

What you could do, is have a series of VLOOKUP commands on Table1 to lookup the values of Table2. Though I wouldn't recommend this for large datasets (in excess of 3000 rows) due to the time it would take to recalculate.

# Re: Combine Matching Rows in two spreadsheets

@L z.

In cell D3 I typed in your formula/program, all on one line. Nothing happened. I get a formula error. I checked, but perhaps I typed something wrong. I don’t know how to type the formula on different lines as you did. I am enclosing my workbook. (I had to add an asterisk before the equal sign else I could not save the file. Just remove it to duplicate where I got to.) Thanks for all your help.

Joel

# Re: Combine Matching Rows in two spreadsheets

A comma was missing before INDEX(... This is fixed in the attached sample

# Re: Combine Matching Rows in two spreadsheets

SUCCESS!! Thank you! Thank you! Thank you!

I haven’t acted on your last reply, but it finally occurred to me to paste the formula/program you sent on July 10 into the formula bar instead of into the cell D3.. Worked like a charm. Then I added a birthday column to table2, changed your formula/program a bit and was able to copy his field into column E of table1. So for now at least, I think I am where I need to be.

# Re: Combine Matching Rows in two spreadsheets

You're welcome. Glad I could help & when you have a minute... On each reply you get on this site, there a link called (something like) mark as best reponse => Can help those who search for existing solutions. Thanks

# Re: Combine Matching Rows in two spreadsheets

Woops, somehow the adapted formula does not work with my real data.

I am trying to match on member ID.

Column1 is supposed to be Date Joined. Instead it just shows the formula.

Column 2 is supposed to be dob (birthday). I got a formula error when entering, so I prefixed it with an asterisk.

Thanks as Always. Joel

# Re: Combine Matching Rows in two spreadsheets

In short:

- Wrong Table name (I renamed the Tables according to the sheet/tab names)

- No reference to the [Member ID] field (the one you want to match)

- Parenthese not at the right place

- You referred to a non-existing field in the other Table

# Re: Combine Matching Rows in two spreadsheets

Thanks L z.,

Unfortunately I am not understanding some items which may be the reason I cannot replicate your work here on live data.

In short:

- Wrong Table name (I renamed the Tables according to the sheet/tab names)

H’mmm. The Tabs are Table1 & Table2. The formula has references only to Table2. For example after ‘COUNTFIS’ and ‘15,6,Row’.

- No reference to the [Member ID] field (the one you want to match)

Got it.

- Parenthese not at the right place

Got it.  ‘ID],1)’ should be ‘ID]),1)’

- You referred to a non-existing field in the other Table

What field was that? Is the other table ‘Table2’?

My original file is attached

Appreciate your help as always. Joel.

# Re: Combine Matching Rows in two spreadsheets

Re. Table names

Look at the name of the Table in sheet/tab named Table2. The actual name of the table is Table1:

Re. You referred to a non-existing field in the other Table

i.e. in the portion of the formula that says: AGGREGATE(15,6,ROW(Table2[Last Name]).... Assuming the other table was actually named Table2, there isn't a field [Last Name] in that Table but a [Last Name2]

Hope this clarifies things

# Re: Combine Matching Rows in two spreadsheets

Thanks L z.
I am now able to apply your formula/program to live data.
In the six days we have been going at this I have learned a lot.
Again, I greatly appreciate your help, Joel

# Re: Combine Matching Rows in two spreadsheets

You're welcome. Glad you have it working now & Thanks for providing feedback. Nice day...