SOLVED

Combine Matching Rows in two spreadsheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2535015%22%20slang%3D%22en-US%22%3ECombine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535015%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20Excel%20how%20do%20you%20combine%20like%20rows%20in%20two%20different%20spreadsheets%3F%3C%2FP%3E%3CP%3ESay%20spreadsheet%20one%20has%20three%20columns%20A.%20First%20Name%2C%20B.%20Last%20Name%2C%20and%20C.%20Town%20of%20Residence.%3C%2FP%3E%3CP%3ESpreadsheet%20two%20has%20three%20columns%3A%20A.%20Phone%20number%2C%20B.%20First%20Name%2C%20and%20C%2C%20Last%20Name.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20match%20on%20First%20and%20Last%20Names%20and%20add%20phone%20number%20to%20column%20D%20of%20spreadsheet%20one.%3C%2FP%3E%3CP%3EThanks%2C%20Joel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2535015%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2535070%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100287%22%20target%3D%22_blank%22%3E%40Barnert%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20follow%20the%20guidelines%20from%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_blank%22%3EWelcome%20to%20your%20Excel%20discussion%20space!%3C%2FA%3E%20please%3CBR%20%2F%3E%3D%26gt%3B%20This%20will%20help%20to%20help%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2535269%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535269%22%20slang%3D%22en-US%22%3EThanks%20L%20z.%20See%20my%20edited%20message%20above.%20Hope%20that's%20what%20you%20need.%20Joel%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2535659%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535659%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100287%22%20target%3D%22_blank%22%3E%40JoelBauer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20small%20representative%20sample%20would%20have%20been%20appreciated%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294842iB49BEBF564E3A9E9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20Table1%20in%20one%20sheet%2C%20Table2%20in%20another.%20In%20Table1%20cell%26nbsp%3B%3CSTRONG%3ED3%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(COUNTIFS(Table2%5BFirst%20Name%5D%2CA3%2C%20Table2%5BLast%20Name%5D%2CB3)%2C%0A%20%20%20%20INDEX(Table2%5BPhone%20number%5D%2C%0A%20%20%20%20%20%20%20%20AGGREGATE(15%2C6%2CROW(Table2%5BLast%20Name%5D)-ROW(Table2%5B%23Headers%5D)%20%2F%20COUNTIFS(A3%2CTable2%5BFirst%20Name%5D%2C%20B3%2CTable2%5BLast%20Name%5D)%2C1)%0A%20%20%20%20)%2C%0A%20%20%20%20%22Not%20found%22%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECorresponding%20sample%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2535929%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535929%22%20slang%3D%22en-US%22%3EThanks%20L%20z.%3CBR%20%2F%3EI%20appreciate%20and%20I%20am%20somewhat%20in%20awe%20of%20your%20efforts.%20Also%20in%20awe%20of%20all%20the%20capabilities%20of%20excel%20that%20I%20do%20not%20know.%20I%20am%20going%20to%20play%20with%20your%20formula%20and%20see%20if%20it%20works%20for%20me.%3CBR%20%2F%3EDoes%20all%20data%20have%20to%20be%20in%20%E2%80%98Tables%E2%80%99%20for%20this%20formula%20to%20work%3F%20(Hope%20you%20understand%20the%20question.)%3CBR%20%2F%3EI%20am%20a%20one%20day%20member%20of%20this%20%E2%80%98forum%E2%80%99.%20I%20don%E2%80%99t%20know%20much.%20For%20instance%2C%20how%20did%20you%20make%20and%20post%20this%20sample%3F%20(Your%20posted%20samples%20are%20right%20to%20the%20point).%3CBR%20%2F%3EIs%20there%20documentation%20on%20using%20this%20%E2%80%98forum%E2%80%99%3F%3CBR%20%2F%3EThanks%20again%2C%20Joel%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2535964%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100287%22%20target%3D%22_blank%22%3E%40JoelBauer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EDoes%20all%20data%20have%20to%20be%20in%20%E2%80%98Tables%E2%80%99%20for%20this%20formula%20to%20work%3F%20(Hope%20you%20understand%20the%20question.)%3C%2FEM%3E%3C%2FP%3E%3CP%3EThis%20can%20work%20with%20Ranges%20instead%20of%20Tables.%20However%2C%20Tables%20makes%20it%20easier%2Fmore%20clear%20and%20have%20a%20number%20of%20other%20advantages.%20I%20suggest%20to%20take%20a%20few%20minutes%20to%20read%20%3CA%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fall%2Fuse-tables-not-ranges%2F992a6e69-9c06-4b01-9230-1e12a87b81b2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20Tables%20not%20Ranges%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3Ehow%20did%20you%20make%20and%20post%20this%20sample%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3EWhen%20you%20create%20a%20post%2Freply%2C%20at%20the%20bottom%20of%20the%20window%20there's%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20377px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294857i48C74E736A1424AB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIs%20there%20documentation%20on%20using%20this%20%E2%80%98forum%E2%80%99%3F%3CBR%20%2F%3ENot%20that%20I'm%20aware%20of.%20IHMO%20the%20User%20Interface%20is%20quite%20intuitive%2C%20it's%20all%20about%20reading%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536395%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536395%22%20slang%3D%22en-US%22%3ENext%20Question%3A%20How%20do%20I%20get%20your%20formula%20%22%3D(IF(COUNTFS(Table2....%22%20into%20cell%20D3%3F%20When%20I%20cut%20and%20paste%20it%20goes%20into%20manty%20cells.%20I%20cannot%20even%20type%20it%20in%20your%20nicely%20structured%20format.%20Thanks%20Joel%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536962%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536962%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100287%22%20target%3D%22_blank%22%3E%40JoelBauer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECopy%2FPaste%20or%20Cut%2FPaste%20cells%20is%20a%20source%20of%20issues%20when%20you%20don't%20know%26nbsp%3B%3CU%3Eexactly%3C%2FU%3E%20what%20you%20do%2Fwant%20to%20do.%20So%2C%20I%20suggest%20you%20forget%20about%20this%20method%20for%20now%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20you%20opted%20for%20Tables%20(instead%20of%20ranges)%20and%20your%20Table1%20starts%20in%20row%202%20(the%20Table%20header)%20as%20in%20the%20sample%20I%20shared%3A%3C%2FP%3E%3CP%3E-%20In%20my%20sample%3A%20Go%20to%20cell%26nbsp%3B%3CSTRONG%3ED3%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E-%20Click%20in%20the%20Excel%20formula%20bar%20%26gt%3B%20Ctrl%2BA%20%26gt%3B%20Ctrl%2BC%3C%2FP%3E%3CP%3E-%20Swith%20to%20your%20workbook%3C%2FP%3E%3CP%3E-%20Go%20to%20Table1%2C%20cell%26nbsp%3B%3CSTRONG%3ED3%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E-%26nbsp%3BClick%20in%20the%20Excel%20formula%20bar%20%26gt%3B%20Ctrl%2BV%20%26gt%3B%20Enter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can't%20make%20it%20work%20please%20attach%20your%20workbook%20to%20your%20next%20reply%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2537636%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2537636%22%20slang%3D%22en-US%22%3EWhat%20you%20could%20do%2C%20is%20have%20a%20series%20of%20VLOOKUP%20commands%20on%20Table1%20to%20lookup%20the%20values%20of%20Table2.%20Though%20I%20wouldn't%20recommend%20this%20for%20large%20datasets%20(in%20excess%20of%203000%20rows)%20due%20to%20the%20time%20it%20would%20take%20to%20recalculate.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2540915%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Matching%20Rows%20in%20two%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20D3%20I%20typed%20in%20your%20formula%2Fprogram%2C%20all%20on%20one%20line.%20Nothing%20happened.%20I%20get%20a%20formula%20error.%20I%20checked%2C%20but%20perhaps%20I%20typed%20something%20wrong.%20I%20don%E2%80%99t%20know%20how%20to%20type%20the%20formula%20on%20different%20lines%20as%20you%20did.%20I%20am%20enclosing%20my%20workbook.%20(I%20had%20to%20add%20an%20asterisk%20before%20the%20equal%20sign%20else%20I%20could%20not%20save%20the%20file.%20Just%20remove%20it%20to%20duplicate%20where%20I%20got%20to.)%20Thanks%20for%20all%20your%20help.%3C%2FP%3E%3CP%3EJoel%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@JoelBauer 

Could you follow the guidelines from Welcome to your Excel discussion space! please
=> This will help to help you

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

@JoelBauer 

A small representative sample would have been appreciated

 

Demo.png

 

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)
    ),
    "Not found"
)

Corresponding sample attached

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

@JoelBauer 

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:

Demo.png

 

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

 

 

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

@JoelBauer 

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

- Swith to your workbook

- Go to Table1, cell D3

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

 

If you can't make it work please attach your workbook to your next reply

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.

@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

@JoelBauer 

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

@L z. 

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.

Greatly appreciate your help, Joel

 

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

@L z. 

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

Hi @JoelBauer 

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

Your updated file is attached 

@L z. 

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.

 

@JoelBauer 

 

Re. Table names

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

 

Demo.png

 

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

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

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