Combining multiple cell data in Excel based on date and quantity with line separators

Other Questions Google Sheets
1

Hey Excel gurus! I’m stuck on a tricky problem. Need help with formulas for two columns.

Here’s what I want:

  1. If there’s only one instance, show the date or quantity as is.
  2. If there are multiple, combine them with line breaks.

I’ve tried messing around with FILTER, SEARCH, JOIN, and REGEX, but no luck so far. Any ideas?

Here’s a quick example of what I’m after:

Date       Quantity   Combined Date   Combined Quantity
2023-05-01 10         2023-05-01      10
2023-05-02 15         2023-05-02      15
2023-05-02 20         2023-05-02      35
2023-05-03 5          2023-05-03      5

For the 2nd of May, it should show both dates and add up the quantities.

Anyone know how to make this work? Thanks!

2

hey mikezhang, i think i got a solution for ya. try this formula for the Combined Date column:

=TEXTJOIN(CHAR(10),TRUE,UNIQUE(A2:A5))

and for Combined Quantity:

=SUMIF(A2:A5,A2,B2:B5)

hope this helps! let me know if u need more explanation

3

I’ve encountered a similar issue in my work. Here’s an approach that should solve your problem:

For the Combined Date column, use this array formula:

=TEXTJOIN(CHAR(10),TRUE,IF(COUNTIF(A:A,A2:A5)>1,A2:A5,“”))

For the Combined Quantity column:

=SUMIF(A:A,A2,B:B)

These formulas will aggregate multiple dates with line breaks and sum quantities for matching dates. Remember to press Ctrl+Shift+Enter if you’re using an older Excel version.

This method is efficient and doesn’t require helper columns. Let me know if you need any clarification on how these formulas work.

4

I’ve dealt with similar Excel challenges before, and here’s a solution that might work for you:

For the Combined Date column, try this formula:

=IF(COUNTIF(A:A,A2)>1,TEXTJOIN(CHAR(10),TRUE,FILTER(A:A,A:A=A2)),A2)

For the Combined Quantity column, use:

=SUMIF(A:A,A2,B:B)

These formulas should handle both single and multiple instances as you described. The date formula combines multiple occurrences with line breaks, while the quantity formula sums up all matching values.

One thing to keep in mind: make sure your cells are formatted to wrap text, otherwise you might not see the line breaks in the date column. Also, you may need to adjust the cell references based on your actual data range.

Give it a shot and let us know if it works for your specific dataset!