Forum Discussion
Merge Data From Two Sheets in Same Workbook and Sort
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.
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.
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 , you are welcome
- 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!!
Sorry, I didn't catch. What I see in file
May 2020 is earlier than June 2020 and both are earlier than Jan 2021.
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
In A1 of Summary sheet
- Thank you! Where exactly does the LET formula live on the sheet?