Jul 10 2021 09:36 AM - edited Jul 10 2021 01:32 PM
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
Jul 10 2021 10:08 AM
Could you follow the guidelines from Welcome to your Excel discussion space! please
=> This will help to help you
Jul 10 2021 01:33 PM
Jul 10 2021 11:19 PM
SolutionA 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)
),
"Not found"
)
Corresponding sample attached
Jul 11 2021 04:50 AM
Jul 11 2021 05:23 AM
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 ;)
Jul 11 2021 01:00 PM
Jul 11 2021 11:12 PM
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
Jul 12 2021 03:28 AM
Jul 12 2021 07:09 PM
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
Jul 12 2021 10:55 PM
A comma was missing before INDEX(... This is fixed in the attached sample
Jul 13 2021 03:04 PM
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
Jul 13 2021 09:19 PM
Jul 15 2021 04:02 AM
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
Jul 15 2021 04:57 AM
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
Jul 15 2021 07:49 AM
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.
Jul 15 2021 09:52 AM
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
Jul 16 2021 04:40 AM
Jul 16 2021 04:56 AM
Jul 10 2021 11:19 PM
SolutionA 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)
),
"Not found"
)
Corresponding sample attached