Google Sheets - Concatenate multiple cells in a row based on date/quantity and with line breaks

itsjesse 0
Viewed 33

I was hoping someone could assist with this. I'm looking for formulas for Column H and Column I - where it would (1) output the date and quantity respectively if there's only one instance of it, or (2) output the concatenate of dates and the concatenate of quantity with line breaks. I've been trying variations of FILTER, SEARCH, JOIN, REGEX, etc. but haven't found anything that works.

Screenshot of example

Thanks in advance.

formula string google-sheets-formula google-sheets
1 Answers

H2:

=ARRAYFORMULA(IFNA(VLOOKUP(ROW(A2:A10)&"", 
 TRIM(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(FILTER(
 IF(A2:F10="",,COLUMN(A2:F10)&"♦"&ROW(A2:F10)&"♠♦"&TO_TEXT(A2:F10)&"×"), 
 MOD(COLUMN(A2:F10)-1, 2)=0)), "♦"), 
 "select max(Col3) where Col3 is not null group by Col1 pivot Col2"),,9^9)), "♠")), 
 "× |×$", CHAR(10))), 2, 0)))

I2:

=ARRAYFORMULA(IFNA(VLOOKUP(ROW(A2:A10)&"", 
 TRIM(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(FILTER(
 IF(A2:F10="",,COLUMN(A2:F10)&"♦"&ROW(A2:F10)&"♠♦"&TO_TEXT(A2:F10)&"×"), 
 MOD(COLUMN(A2:F10), 2)=0)), "♦"), 
 "select max(Col3) where Col3 is not null group by Col1 pivot Col2"),,9^9)), "♠")), 
 "× |×$", CHAR(10))), 2, 0)))

enter image description here

player00
Related