Forum Discussion

JosephA1915's avatar
JosephA1915
Copper Contributor
Jul 04, 2021
Solved

Merge Data From Two Sheets in Same Workbook and Sort

macOS Catalina/Excel for Mac 16.50   Hello.  What is the best way to merge data from two (or more) sheets in the same workbook onto a "Summary" sheet and then sort by the data in the first column (...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 05, 2021

    JosephA1915 

    I don't know how exactly your data is structured, for that better to have sample file. Assume you have two structured tables, they could be in different sheets. If ranges, you may convert them to tables by Ctrl+T or use named ranges. Something like this

    To join these tables you may use formula as

    =LET(
  rowsOne, ROWS(Table1),
  rowsTwo, ROWS(Table2),
  c,       SEQUENCE(,COLUMNS(Table1)),
  hMerge, LET(
   k, SEQUENCE(rowsOne+rowsTwo),
   IF(k<=rowsOne,
      INDEX(Table1, k, c),
      INDEX(Table2, k-rowsOne, c))
 ),
 SORT(hMerge)
)

    and instead of Table1, Table2 use names of your actual tables or ranges.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 05, 2021

JosephA1915 

I don't know how exactly your data is structured, for that better to have sample file. Assume you have two structured tables, they could be in different sheets. If ranges, you may convert them to tables by Ctrl+T or use named ranges. Something like this

To join these tables you may use formula as

=LET(
  rowsOne, ROWS(Table1),
  rowsTwo, ROWS(Table2),
  c,       SEQUENCE(,COLUMNS(Table1)),
  hMerge, LET(
   k, SEQUENCE(rowsOne+rowsTwo),
   IF(k<=rowsOne,
      INDEX(Table1, k, c),
      INDEX(Table2, k-rowsOne, c))
 ),
 SORT(hMerge)
)

and instead of Table1, Table2 use names of your actual tables or ranges.

  • JosephA1915's avatar
    JosephA1915
    Copper Contributor
    Jul 05, 2021

    SergeiBaklan 

     

    Thank you Sergei.  I have included the file here.  Each additional sheet will have the same rows/columns.  There will be more sheets added in the future.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 06, 2021

      JosephA1915 

      Formula could be 

      =LET(
  cols, 8,
  c,    SEQUENCE(,cols),
  rowsLOJ,  XMATCH(,LOJ!A:A)-1,
  arrayLOJ, LOJ!A1:INDEX(LOJ!A:H, rowsLOJ, cols),
  rowsREG,  XMATCH(,REG!A:A)-1,
  arrayREG, REG!A1:INDEX(REG!A:H, rowsREG, cols),
  rowsJH,   XMATCH(,JH!A:A)-1,
  arrayJH,  JH!A1:INDEX(JH!A:H, rowsJH, cols),
 k, SEQUENCE(rowsLOJ + rowsREG + rowsJH),
 array,
    IF( k <= rowsLOJ,
         INDEX(arrayLOJ, k, c),
    IF( k <= rowsLOJ+rowsREG,
          INDEX(arrayREG, k-rowsLOJ, c),
          INDEX(arrayJH, k -rowsLOJ - rowsREG, c))),
 SORT(FILTER(array, INDEX(array,0,1)>1))
)

      Please check in attached file.

      • JosephA1915's avatar
        JosephA1915
        Copper Contributor
        Jul 06, 2021
        Thank you! Where exactly does the LET formula live on the sheet?

Resources