Jul 04 2021 02:09 PM
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...
Thank you,
Joseph
Jul 04 2021 11:35 PM - edited Jul 04 2021 11:35 PM
If your version of Excel supports dynamic arrays that could be done using them. Especially if you organize your data as structured tables.
Jul 05 2021 10:36 AM
Jul 05 2021 12:35 PM
Please check if this LET function - Office Support (microsoft.com) available.
Jul 05 2021 12:59 PM
Jul 05 2021 02:02 PM
SolutionI 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.
Jul 05 2021 02:19 PM
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.
Jul 06 2021 01:17 AM
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.
Jul 06 2021 08:07 AM
Jul 06 2021 08:35 AM
In A1 of Summary sheet
Jul 06 2021 08:43 AM
Jul 06 2021 04:30 PM
Jul 08 2021 08:01 AM
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
Jul 09 2021 12:31 AM
Sorry, I didn't catch. What I see in file
May 2020 is earlier than June 2020 and both are earlier than Jan 2021.
Jul 12 2021 09:34 AM
Jul 12 2021 10:21 AM
@JosephA1915 , you are welcome
Jul 05 2021 02:02 PM
SolutionI 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.