Count unique string variants

Question

There could be quite a simple solution to this, but I am trying to find the number of times a unique variant (i.e. non-duplicates) of a string appears in a column. However this string is only part of the text contained in a cell, and not the entire cell. To illustrate:

EuropeSpainMadrid
EuropeSpainBarcelona
AsiaChinaShanghai
AsiaJapanTokyo
EuropeEnglandLondon
EuropeSpainMadrid

I would like to find how many unique instances there are of a string that contains "EuropeSpain". So using this example, I would find that a variant of "EuropeSpain" appears only twice (given that the second instance of "EuropeSpainMadrid" is a duplicate).

A solution to this is to use pivots to summarise the data and remove duplicated; however given that my underlying dataset changes often this would require manual adjustments and corrections. I would therefore like to avoid adding any intermediate steps (i.e. PivotTables, other data sets etc) between my data and the counts.

UPDATE: I now understand to use wildcards to solve the first part of my question (counting the occurrences of "EuropeSpain"), however I am not yet clear on the second part of my question (how to find the number of unique occurrences).

Is there a formula or VBA code that could do this?

You can use wildcards with COUNTIF see link
– zx8754
Commented Jul 31, 2014 at 14:19 — zx8754, Commented Jul 31, 2014 at 14:19

zx8754 · Accepted Answer · 2014-07-31 14:16:26Z

2

Using wildcards:

=COUNTIF(A1:A6,"="&"*"&C1&"*")

enter image description here

answered Jul 31, 2014 at 14:16

zx8754

56.6k12 gold badges129 silver badges229 bronze badges

Thanks! This worked perfectly for the first part of my question. How would I then count the unique instances of EuropeSpain (i.e. to take into account that "EuropeSpainMadrid" appears twice, the end result should be 2 rather than 3)?
– Nat Aes
Commented Jul 31, 2014 at 14:23
Manually copy to another column and remove duplicates then use the same formula again.
– zx8754
Commented Jul 31, 2014 at 14:26
I appreciate that that would do the trick, however given the dataset I am using changes often this would be quite manual. Is there no formula that would work?
– Nat Aes
Commented Jul 31, 2014 at 14:28
There might be VBA solution, add VBA tag to your post.
– zx8754
Commented Jul 31, 2014 at 14:29
1

Thanks. I will give you an uptick and will wait for other answers before accepting anything...
– Nat Aes
Commented Jul 31, 2014 at 14:32

Add a comment |

pnuts · Accepted Answer · 2014-07-31 14:54:25Z

1

For without VBA but with some versatility, I suggest with Text in ColumnA (labelled), ColumnB labelled Flag and EuropeSpain in C1:

=FIND(C$1,A2)

in B2 copied down.

Then pivot A:B with Flag for FILTERS (and 1 selected), Text for ROWS and Count of Text for Sigma VALUES.

Apply Distinct Values if required (and available!), alternatively a formula of the kind:

=MATCH("Grand Total",E:E)-4

would count uniques.

edited Jul 31, 2014 at 14:54

answered Jul 31, 2014 at 14:44

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

Thanks - unfortunately I am using Excel 2010, so Distinct Values is not available. However in any case I am looking to avoid adding any intermediate steps (i.e. pivot tables, other data sets etc) between my data and the counts. My original solution involved a combination of pivots and concatenated strings, but required manual adjutment each time the dataset changed (which is often).
– Nat Aes
Commented Jul 31, 2014 at 14:50
Fair enough. Let me amend the question to make it clearer. With regards to the =MATCH("Grand Total",E:E)-4 formula, how does this count uniques? How does this formula work? Understanding it will allow me to adapt it to my exact dataset.
– Nat Aes
Commented Jul 31, 2014 at 15:00

Add a comment |

Collectives™ on Stack Overflow

Count unique string variants

2 Answers 2

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related