Unique text values times two

[wyrm76]

Hello to all,

I just stumbled on this site in desperation because I am fighting with Excel all morning. I hope someone can help me.

I have a huge datasheet from which I am trying to make some graphs. Here's the challenge I am facing:

Column AZ holds the name of a customer, column BC the location of the customer. Names of the customers can be listed multiple times, and I am searching for a way in which I can see how many customers there are for a location. This would lead me to a graph that shows the ammount of active customers per location.

customer| location
a | New york
a | New York
b | New York
c | New York
d | Paris
d | Paris
e | Amsterdam

The Result should be:
New York: 3
Paris: 1
Amsterdam: 1

To make it even more difficult. Some fields contain just Amsterdam, others Amsterdam-west. So searching for texts containing Amsterdam would be great.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). In your description, should New York=4 and Paris=2?

 

[wyrm76]

Thank you for replying. I know it's difficult to help when you don't actually see the sheet. I have uploaded an example here: customer.xlsx

The text in red should be the calculated result by exel.

 

[Fluff]

Hi & welcome to MrExcel.
How about

customer.xlsx
	A	B	C	D	E	F	G	H
1	Customer	Location of customer
2	asd	Amsterdam West					Number of unique customers
3	Pieters	Paris				Amsterdam	3
4	Jansen	Amsterdam				Barcelona	1
5	Willemsen	Barcelona				New York	2
6	Jansen	Amsterdam				Paris	3
7	Freriks	New York
8	Pieters	Paris
9	Jansen	Amsterdam
10	Veenstra	Paris
11	Boonstra	New York
12	Kamperman	Amsterdam
13	Van Halen	Paris
14
Blad1

Cell Formulas
Range	Formula
G3:G6	G3	=SUM(--(FREQUENCY(IF(LEFT($B$2:$B$13,LEN(F3))=F3,MATCH($A$2:$A$13,$A$2:$A$13,0)),ROW($A$2:$A$13)-ROW($A$2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[wyrm76]

That's it! Thank you!!! Now I have to translate it to my Dutch version of Excel 2013. I have used an online formula translator for that. Until now without any luck, but I think I'll get there.

Thanks again!

 

[Fluff]

In Dutch it's

=SOM(--(INTERVAL(ALS(LINKS($B$2:$B$13;LENGTE(F3))=F3;VERGELIJKEN($A$2:$A$13;$A$2:$A$13;0));RIJ($A$2:$A$13)-RIJ($A$2)+1)>0))

I use this site for translating formulae when needed.

Translator

The online tool for the translation of Excel formulas and functions from one language to another. Supports the replacement of argument separators, 35 languages and much more.

en.excel-translator.de

 

[wyrm76]

Tried the exact same. For some strange reason it doesn't work on this end. Example 2

 

[Fluff]

You need to get rid of the colon (and extra spaces) from the values in col F

 

[wyrm76]

Ofcourse! Stupid me! Thsnks

Tried it, no luck yet however...

 

[Fluff]

Did you confirm it with Ctrl Shift Enter?