SOLVED

Merge Data From Two Sheets in Same Workbook and Sort

Copper Contributor

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 (in this case, the date)?  I would like for this to update on the Summary sheet automatically as values are added/removed/edited on any of the source sheets.

 

The data on each sheet is organized something like this...

 

 

Screen Shot 2021-07-04 at 2.03.00 PM.png

 

Thank you, 

 

Joseph

 

 

 

 

15 Replies

@JosephA1915 

If your version of Excel supports dynamic arrays that could be done using them. Especially if you organize your data as structured tables.

Thank you Sergei! How would I know if my version of Excel supports dynamic arrays? I'm sorry, I'm not very experienced with those areas of Excel.

@Sergei Baklan 

 

It appears to be available.

 

 

Screen Shot 2021-07-05 at 12.57.09 PM.png

best response confirmed by JosephA1915 (Copper Contributor)
Solution

@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

image.png

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.

@Sergei Baklan 

 

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.

@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.

Thank you! Where exactly does the LET formula live on the sheet?

@JosephA1915 

In A1 of Summary sheet

@Sergei Baklan 

 

When I click on A1, all I see is this in the formula bar...

 

 

Screen Shot 2021-07-06 at 8.41.54 AM.png

@JosephA1915 

Please expand formula bar. Use Ctrl+Shift+U or arrow at the right

image.png

@Sergei Baklan 

 

Thank you.  It looks like this is going to work.  It seems there is some sort of glitch.  Take a look at the first two rows.  It doesn't seem like they're sorted correctly (by date).

 

Thank you, 

 

Joseph

 

 

@JosephA1915 

Sorry, I didn't catch. What I see in file 

image.png

May 2020 is earlier than June 2020 and both are earlier than Jan 2021.

Sorry, I didn't notice that those two dates were 2020. Thank you for pointing that out and thank you for all of your help on this. It works great!!
1 best response

Accepted Solutions
best response confirmed by JosephA1915 (Copper Contributor)
Solution

@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

image.png

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.

View solution in original post