Forum Discussion

JoelBauer's avatar
JoelBauer
Copper Contributor
Jul 10, 2021
Solved

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: ...
excel
  • Lorenzo's avatar
    Lorenzo
    Jul 11, 2021

    JoelBauer 

    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

JoelBauer's avatar
JoelBauer
Copper Contributor
Jul 10, 2021
Thanks L z. See my edited message above. Hope that's what you need. Joel
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 11, 2021

    JoelBauer 

    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

    • JoelBauer's avatar
      JoelBauer
      Copper Contributor
      Jul 11, 2021
      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
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 11, 2021

        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 https://answers.microsoft.com/en-us/msoffice/forum/all/use-tables-not-ranges/992a6e69-9c06-4b01-9230-1e12a87b81b2

         

        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 😉

         

         

Resources