Forum Discussion

Copper Contributor

Apr 07, 2025

Solved

Merge rows with different dates but no date span

I have some spreadsheets with rows that have the same data in column A, different dates in the date columns, but no date span. I'd like to combine them into 1 row with a large date span. For exampl...

excel

OliverScheurich

Apr 10, 2025

=IFNA(REDUCE(HSTACK("ID","Desc","Date1","Date2","Comment"),UNIQUE(A2:A16),
LAMBDA(u,v,VSTACK(u,LET(filtered,FILTER(A2:E16,A2:A16=v),
IF(ROWS(filtered)=1,HSTACK(COOSECOLS(filtered,1),"",COOSECOLS(filtered,3,4)),
IF(AND(ROWS(UNIQUE(DROP(DROP(MAP(COOSECOLS(filtered,3),
VSTACK(0,COOSECOLS(filtered,4)),LAMBDA(a,b,a-1=b)),1),-1),FALSE))=1,
UNIQUE(DROP(DROP(MAP(COOSECOLS(filtered,3),
VSTACK(0,COOSECOLS(filtered,4)),LAMBDA(a,b,a-1=b)),1),-1),FALSE)=TRUE),
HSTACK(INDEX(filtered,1,1),"",
TAKE(COOSECOLS(filtered,3),1),TAKE(COOSECOLS(filtered,4),-1),""),
HSTACK(COOSECOLS(filtered,1),"",COOSECOLS(filtered,3,4)))))))),"")

This formula returns the desired output in my sample sheet. The formula is available with Excel for the web and Office 365.

OliverScheurich

Gold Contributor

Apr 10, 2025

=IFNA(REDUCE(HSTACK("ID","Desc","Date1","Date2","Comment"),UNIQUE(A2:A16),
LAMBDA(u,v,VSTACK(u,LET(filtered,FILTER(A2:E16,A2:A16=v),
IF(ROWS(filtered)=1,HSTACK(COOSECOLS(filtered,1),"",COOSECOLS(filtered,3,4)),
IF(AND(ROWS(UNIQUE(DROP(DROP(MAP(COOSECOLS(filtered,3),
VSTACK(0,COOSECOLS(filtered,4)),LAMBDA(a,b,a-1=b)),1),-1),FALSE))=1,
UNIQUE(DROP(DROP(MAP(COOSECOLS(filtered,3),
VSTACK(0,COOSECOLS(filtered,4)),LAMBDA(a,b,a-1=b)),1),-1),FALSE)=TRUE),
HSTACK(INDEX(filtered,1,1),"",
TAKE(COOSECOLS(filtered,3),1),TAKE(COOSECOLS(filtered,4),-1),""),
HSTACK(COOSECOLS(filtered,1),"",COOSECOLS(filtered,3,4)))))))),"")

This formula returns the desired output in my sample sheet. The formula is available with Excel for the web and Office 365.

ChrisC01

Copper Contributor

Apr 10, 2025

Hello. I have a follow up question on this. If I wanted to keep the data in the additional columns for the rows that did not need to be combined (the data in the Desc and Comment columns), could the formula be updated to do that? So the C8520 in the above example, which doesn't have a duplicate row, would still have the original Desc and Comment populated, but the ones that were combined would have it removed.

Thanks again. :)

OliverScheurich

Gold Contributor

Apr 11, 2025

=IFNA(REDUCE(HSTACK("ID","Desc","Date1","Date2","Comment"),
UNIQUE(A2:A20),LAMBDA(u,v,VSTACK(u,LET(filtered,FILTER(A2:E20,A2:A20=v),
IF(ROWS(filtered)=1,filtered,
IF(AND(ROWS(UNIQUE(DROP(DROP(MAP(CHOOSECOLS(filtered,3),
VSTACK(0,CHOOSECOLS(filtered,4)),LAMBDA(a,b,a-1=b)),1),-1),FALSE))=1,
UNIQUE(DROP(DROP(MAP(CHOOSECOLS(filtered,3),
VSTACK(0,CHOOSECOLS(filtered,4)),LAMBDA(a,b,a-1=b)),1),-1),FALSE)=TRUE),
HSTACK(INDEX(filtered,1,1),"",
TAKE(CHOOSECOLS(filtered,3),1),TAKE(CHOOSECOLS(filtered,4),-1),""),
filtered)))))),"")

You are welcome. Does this return the desired output?

ChrisC01
Copper Contributor
Apr 11, 2025
Yes it does. This is perfect. Thank you so much. :)