Functions to filter and sum delimited incoming data

Question

I have the following incoming data:

Date	Data	ID
03/21	Red: 10	01
03/21	Blue: 5	02
04/21	Red: 20, Blue: 15	03
04/21	Green: 7	04
04/21	Red: 13, Green: 15	05

I have managed to separate the data into its components as shown below:

Date	Data	ID	Color 1	Value 1	Color 2	Value 2
03/21	Red: 10	01	Red	10
03/21	Blue: 5	02	Blue	5
04/21	Red: 20, Blue: 15	03	Red	20	Blue	15
04/21	Green: 7	04	Green	7
04/21	Red: 13, Green: 15	05	Red	13	Green	15

I want to create a function that looks at each month and sums the values for each color. An example is below:

Date	Red	Blue	Green
03/21	10	5	0
04/21	13	0	22

I have absolutely no control over the incoming data. Any help would be greatly appreciated.

A function to this effect: =LEFT(IFERROR(LEFT(E342,SEARCH(",",E342)-1),E342),SEARCH(":",IFERROR(LEFT(E342,SEARCH(",",E342)-1),E342))-1) — Sam Anderson, Apr 03 '21 at 17:22
Starting with your incoming data, I would use Power query to separate your data into `Date`, `Color` and `Value` columns. Then `Group` by `Date` and `Color` with the `Sum` operation. Finally Pivot on the color column with a `Sum` aggregation — Ron Rosenfeld, Apr 03 '21 at 17:22

score 0 · Answer 1 · answered Apr 04 '21 at 02:56

With Excel O365 you can directly achieve your desired output without any helper table. Use nested FILTERXML() formula with Textjoin(). As per my below screenshot use below formula to F2 cell.

=SUM(IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(", ",TRUE,IF($A$2:$A$6=$E2,$B$2:$B$6,"")),", ","</s><s>")&"</s></t>","//s[contains(., '"&F$1&"')]"),": ","</s><s>")&"</s></t>","//s[last()]"),0))

For date column to extract unique dates/month you can use UNIQUE() function to E2 cell,

=UNIQUE(A2:A6)

Here is detailed explanation of FILTERXML() function from @JvdV.

Functions to filter and sum delimited incoming data

1 Answers1