Forum Discussion
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 example, the spreadsheet has 5 rows for A1234, each with a different date span, but there is no lapse in the dates:
There's actually no lapse between any of the dates in any of these rows, so I'd like it to look like this:
The data in the 2nd column (Desc1 or Desc2) and the last column should be blank, but if it was replaced with the data in the last original row, that would work too.
Is there any way to do this? It's important to note that if there is a lapse in dates, the rows should not be combined.
thanks!
Chris
=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.
5 Replies
This is incredible. Thank you so much. I did find a typo in the formula - COOSECOLS should be CHOOSECOLS, but it works perfectly with that fix.
=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.
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. :)
=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?
