Totaling comma separated values in a range of cells

[amytrez]

Hi everyone!

I am trying to write a formula that will take a range of cells (ex. D2:H2), count the total number of comma separated values, and then eliminate duplicates. I've tried using the LEN function, ARRAYFORMULA function, UNIQUE function, in a variety of ways and cannot come up with the correct values! When I actually get a value returned, it's always zero. The values themselves are times (ex. 9:30, 11:00) but the cells are all treated as plain text.

Has anyone ever used a similar formula they can share? Bonus points if it also works in Google Sheets!

 

[duggie33]

Hi amytrez,

See if this works for you...

Book1
	D	E	F	G	H
2	9:30,11:30,11:45	8:30,11:30,13:45	19:30,11:30,11:45	14:30,11:32,11:48	9:31,11:30,11:45
3
4	10
Sheet1

Cell Formulas
Range	Formula
D4	D4	=COUNTA(UNIQUE(TEXTSPLIT(TEXTJOIN(",",TRUE,D2:H2),,",",TRUE,0)))

I know not how to get your bonus points

Hope that helps,

Doug

 

[Peter_SSs]

Welcome to the MrExcel board!

count the total number of comma separated values, and then eliminate duplicates.

Does that mean you are looking for two different results, one for the count and one for the eliminations?
How would the result(s) with the duplicates eliminated look? All in one cell? Something else?

If there were three 11:30 times, would there be
- one 11:30 remaining because it is only the second & third ones that you consider duplicates, or
- no 11:30 remaining because you consider all are duplicates and therefore removed?

A few samples and the expected results with XL2BB would help considerably.

 

[amytrez]

Welcome to the MrExcel board!


Does that mean you are looking for two different results, one for the count and one for the eliminations?
How would the result(s) with the duplicates eliminated look? All in one cell? Something else?

If there were three 11:30 times, would there be
- one 11:30 remaining because it is only the second & third ones that you consider duplicates, or
- no 11:30 remaining because you consider all are duplicates and therefore removed?

A few samples and the expected results with XL2BB would help considerably.

If there were three 11:30s, I would only count 1. The reason is that I'm totalling up intervals, and there is only one 11:30 interval per day but multiple events could happen during that interval and have it listed.

 

[Peter_SSs]

If there were three 11:30s, I would only count 1.

Thanks. That leaves these still unanswered though.

Does that mean you are looking for two different results, one for the count and one for the eliminations?
How would the result(s) with the duplicates eliminated look? All in one cell? Something else?

A few samples and the expected results with XL2BB would help considerably.

 

[amytrez]

Thanks. That leaves these still unanswered though.

Here's an example:

Data set 1	Data Set 2	Result
1:30, 2:00, 4:30	1:30, 3:00, 4:30, 5:00	5
(counts 3 values)	(counts four values, eliminates 1:30 and 4:30)	(returns 5 values total)

 

[Peter_SSs]

Here's an example:

Thanks. Then in that case don't you already have your answer in post #2? Have you tried it?

 

[amytrez]

The formula in post 2 didn't work in google sheets, but I just came up with the answer and it's way simpler than I was making it lol

Write a formula that adds multiple single-cell LEN functions together!
=((LEN(TRIM(M2))-LEN(SUBSTITUTE(TRIM(M2),",",""))+1)+(LEN(TRIM(N2))-LEN(SUBSTITUTE(TRIM(N2),",",""))+1))

But here's my next issue...the LEN function returns a value of "1" for empty cells, I think because it's inserting a zero. What do I add to the formula to remove them?

 

[Peter_SSs]

The formula in post 2 didn't work in google sheets,

Well, you have asked the question in the Excel Questions forum and you did state that working in Google sheets was for 'bonus points' not a basic requirement.
If this is a Google Sheets question then it is in the wrong forum.

but I just came up with the answer and it's way simpler than I was making it lol

But that formula does nothing to eliminate duplicates and does not produce the expected result of 5 for your sample data. So as far as I can see it has little to do with your original question.

23 09 11.xlsm
	M	N	O
2	1:30, 2:00, 4:30	1:30, 3:00, 4:30, 5:00	7
Count times

Cell Formulas
Range	Formula
O2	O2	=((LEN(TRIM(M2))-LEN(SUBSTITUTE(TRIM(M2),",",""))+1)+(LEN(TRIM(N2))-LEN(SUBSTITUTE(TRIM(N2),",",""))+1))

If you just want to count the values, and some cells could be empty, then try this. I have assumed at least one cell will have a value.

23 09 11.xlsm
	L	M	N	O
4	1:30, 2:00, 4:30		1:30, 3:00, 4:30, 5:00	7
Count times

Cell Formulas
Range	Formula
O4	O4	=LET(t,TEXTJOIN(",",1,L4:N4),LEN(t)-LEN(SUBSTITUTE(t,",",""))+1)

For the unique count, does this also work in Google Sheets?

23 09 11.xlsm
	L	M	N		P
4	1:30, 2:00, 4:30		1:30, 3:00, 4:30, 5:00		5
Count times

Cell Formulas
Range	Formula
P4	P4	=ROWS(UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(",",1,L4:N4),",","</c><c>")&"</c></p>","//c")))