Forum Discussion
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) ), "Not found" )Corresponding sample attached
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
- You're welcome. Glad you have it working now & Thanks for providing feedback. Nice day...
- 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. 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
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.
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
- 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
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