Counting strings

[MR_MKOOL]

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>

 

[MattH1]

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.

 

[MR_MKOOL]

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

 

[MattH1]

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!

 

[Eric W]

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.

 

[MattH1]

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!

 

[MR_MKOOL]

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

 

[Aladin Akyurek]

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.

 

[MR_MKOOL]

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>	members	joined
Amsterdam	230	102
Noordeloos	62	94
The Hague	188	0

<tbody>
</tbody>