Counting Unique Number based on Substring

Question

I'm wondering if there was a formula to compare up to the first "_" substring and find the count of the number of unique values and to ignore any blank values. I couldn't figure out it attempting to use it with FREQ, LEN, MATCH but failed miserably. I'm not an expert on functions in excel so any help or guide would be appreciated!

A1_fdsafa
A2_asdfa
A2_sfda
A3_fdsa
A3_fdsa
A3_asssf
A123_fdsa

Should output: 4

As per Excel O365 it's relatively easy using `=COUNTA(UNIQUE(LEFT(A1:A7,FIND("_",A1:A7))))` — JvdV, Jul 17 '20 at 09:31

score 0 · Answer 1 · answered Jul 28 '15 at 18:47

0

I just ended up creating another column using the delimiter: LEFT(A1,FIND("_",A1)-1 and then ended up finding the unique number that way since I still failed at creating a single formula. Will work on it and will update!

answered Jul 28 '15 at 18:47

Bryan

1,438
3
15
24

I was at the same place, still working on a single-formula solution for you. – David G Jul 28 '15 at 18:51
Maybe this isn't possible. There is no function for counting unique values: http://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel which makes it super laggy. Would love input from excel expert! – Bryan Jul 28 '15 at 19:01
I abandoned as well. I believe there is no fix. Question remains open, I guess? It is entirely doable in VBA, for sure, but for a built-in excel formula I don't think so (which is a damn shame if you ask me...). – David G Jul 28 '15 at 19:06

score 0 · Answer 2 · answered Jul 28 '15 at 20:14

Array formula**:

=SUM(IF(FREQUENCY(IF(A1:A7<>"",MATCH(LEFT(A1:A7,FIND(CHAR(95),A1:A7)),LEFT(A1:A7,FIND(CHAR(95),A1:A7)),0)),ROW(A1:A7)-MIN(ROW(A1:A7))+1),1))

Apologies - I had to use CHAR(95) in place of "_" (which are equivalent in Excel terms), since the post editor evidently considered the latter to be an HTML instruction of some sort.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Counting Unique Number based on Substring

2 Answers2