# Counting strings

#### MR_MKOOL

##### New Member
Hey Hey,

I have an excel sheet with members. Looking like this;

 place date of birth etc. amsterdam dd-mm-yyyy x The Hague dd-mm-yyyy x

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 118px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
 dd-mm-yyyy

<tbody>
</tbody>
</body>For every member I have a different row. So I have 230 rows Amsterdam and 188 rows the hague. I would like to create a new sheet with unique values with frequency. so something like this..
 place date of birth etc. fr. amsterdam dd-mm-yyyy x 230 The Hague dd-mm-yyyy x 188

<tbody>
</tbody>

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
If you list Amsterdam and The Hague on another sheet you can find the frequency using:

Cell D1 (frequency of Amsterdam): =COUNTIF(A:A, "amsterdam")
Cell D2 (frequency of The Hague): =COUNTIF(A:A, "The Hague")

Assuming your place is in Column A on the original sheet. Unless we have more details and a visual, it's tough to determine exactly how you want this to play out.

Thanks Matth,

that is looking good. I realise now I have not given you guys all the details.

There are about 130 different municipalities and boroughs. So that would mean 130 adjustments...? and I still do not have unique values for my place of origin...? Am I right? If yes I was wondering if there is a more elegant way??

Now for every member I have a different row. So I have 230 rows/members Amsterdam and 188 rows/members the hague. I would like to create a new sheet with unique values for the place of origin with the frequency. This new sheet will be exported as .csv for further analysis outside excel

One way of doing this off the top of my head is such:
Copy the entire Column A into another sheet. Highlight it and remove duplicates (if you don't know how to do that, https://support.office.com/en-us/ar...b-bbe1-8daaec1e83c2#bmremove_duplicate_values) Another way to remove the duplicates once you've copied it into another cell is via Alt Keys. Alt + A + M and then click Remove Duplicates.
Then, use the following function in Cell D1 and drag down: =COUNTIF(Sheet1!A:A, \$A1)
Edit Sheet1 to be the name of the current worksheet you're using. This should give you all the unique municipalities/boroughs and then COUNT each one.

Let me know how that goes, best of luck!

Last edited:
You could also use a Pivot Table. If your columns are A:C, select those columns, go to the Insert tab and click Pivot Table. Click OK (for on a new sheet). Then on the right side of the window it will say PivotTable Fields. Drag "place" down to the Rows box. Then drag it again to the Values box. That's it and only takes a few seconds.

Good luck.

You could also use a Pivot Table. If your columns are A:C, select those columns, go to the Insert tab and click Pivot Table. Click OK (for on a new sheet). Then on the right side of the window it will say PivotTable Fields. Drag "place" down to the Rows box. Then drag it again to the Values box. That's it and only takes a few seconds.

Good luck.

This is probably the much better option, thanks Eric! Didn't know if he was willing to use pivot tables or not but definitely worth mentioning!

Thanks guys it solved my problem...
I was already afraid you would mention the pivot table.... to be honest I did that before! if you know a good tutorial I am happy to hear about that.

place date of birth etc. fr.
amsterdam dd-mm-yyyy x 230
Noordeloos
The Hague dd-mm-yyyy x 188

Thanks guys it solved my problem...
I was already afraid you would mention the pivot table.... to be honest I did that before! if you know a good tutorial I am happy to hear about that.

place date of birth etc. fr.
amsterdam dd-mm-yyyy x 230
Noordeloos
The Hague dd-mm-yyyy x 188

This can be done with a pivot table, but also with formulas.

Hey guys
I was still busy writing.. when I accidentally posted it. As I said I dont have a clue about pivot... so I choose the other approach. Now a new problem has risen. I wonder can you solve it in a pivot immediately...

The problem is the following I counted all the members. Then I counted all the kids that joined the jamboree(this was a separate file). In the second file e.g.; nobody from the borough noordeloos came. So Noordeloos is not in the second file so the value of The Hague got printed in Noordeloos. I was wondering if the was a way to automatically match this.
 place

<tbody>
</tbody>
membersjoined
Amsterdam230102
Noordeloos6294
The Hague1880

<tbody>
</tbody>

Replies
1
Views
1K
Replies
3
Views
358
Replies
1
Views
362
Replies
0
Views
532
Replies
2
Views
1K

1,203,460
Messages
6,055,556
Members
444,797
Latest member
18ecooley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back