HELP! is there a quick way to count / lookup

[ludo2016]

Hello

I have a dataset with about 200 different post codes spread across 6,000 company names.
I want to find out how many companies are in each post code.

how should i do it?
hopefully, my end product is a bell curve distribution chart..

 

[Fishboy]

Hello

I have a dataset with about 200 different post codes spread across 6,000 company names.
I want to find out how many companies are in each post code.

how should i do it?
hopefully, my end product is a bell curve distribution chart..

Hi ludo2016, welcome to the boards.

Assuming that you have a list of companies in column A (A2:A6001 for the sake of example), and their addresses in column B (B2:B6001 for the sake of example), the easiest thing to do would be to have another column somewhere with all of the postcodes you want to count, 1 postcode per row (C2:C201 for the sake of example). You could then use the following COUNTIF in cell D2 and drag-fill it down:

=COUNTIF($B$2:$B$6001,"*"&C2&"*")

The above would count each row where the specified postcode can be found as part of the address.

 

[ludo2016]

I don't have a column with a unique post code per row. I have a column with about 200 different codes across 6,000 rows.
In order to go thru with your method, first of all, how would I extrapolate the different codes and distribute them across 200 rows?
(Btw the codes are all 5 numerical digits)

 

[bhos123]

I don't have a column with a unique post code per row. I have a column with about 200 different codes across 6,000 rows.
In order to go thru with your method, first of all, how would I extrapolate the different codes and distribute them across 200 rows?
(Btw the codes are all 5 numerical digits)

1> copy the column and paste in a different worksheet
2>select the new column and press ' remove duplicates' in the DATA section
3> use the countif formula.

 

[ludo2016]

1> copy the column and paste in a different worksheet
2>select the new column and press ' remove duplicates' in the DATA section
3> use the countif formula.

ok thanks fishboy and bhos. its still not getting me a proper result.
removing duplicates worked but the countif suggestion is not working

as per fishboy's idea here is the formula I'm using:
=COUNTIF($A$5:$A$11477,"*"&Sheet1!B2&"*")

As I don't know what the asterix is for, I left it blank. The formula resulted in a blank which is not right but even if it did work, fishboy is suggesting dragging it down across the 200 or so rows of unique post codes... but don't I need to be dragging it down across the 600 or so rows of non-unique post codes?

(there are about 600 company names that I'm looking at having filtered the ones I want within A2:A11477).
And as per bhos instruction Sheet1!B2 is the first row of the unique post code column.

Not sure if people follow..Is there a way to attach a spreadsheet to illustrate? Would that help you help me?

 

[svendiamond]

Not sure if people follow..Is there a way to attach a spreadsheet to illustrate? Would that help you help me?

I'm so glad you asked that question. The best thing you could do is to get the Mr Excel HTML Maker from this forum post: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Follow the instructions to install this Excel Add-In and then use it to share your data on the forum. It will basically be like copying and pasting into the forum, except it will turn into HTML so that it will be formatted correctly. You will definitely increase your chances of getting a solution.

 

[Fishboy]

ok thanks fishboy and bhos. its still not getting me a proper result.
removing duplicates worked but the countif suggestion is not working

as per fishboy's idea here is the formula I'm using:
=COUNTIF($A$5:$A$11477,"*"&Sheet1!B2&"*")

As I don't know what the asterix is for, I left it blank. The formula resulted in a blank which is not right but even if it did work, fishboy is suggesting dragging it down across the 200 or so rows of unique post codes... but don't I need to be dragging it down across the 600 or so rows of non-unique post codes?

(there are about 600 company names that I'm looking at having filtered the ones I want within A2:A11477).
And as per bhos instruction Sheet1!B2 is the first row of the unique post code column.

Not sure if people follow..Is there a way to attach a spreadsheet to illustrate? Would that help you help me?

HERE is a simplified example of my suggestion. It is simplified by:

- Having 600 companies instead of 6000
- Having 20 postcodes instead of 200

Column A is company name
Column B is the address with postcode
Column C is the list of unique postcodes
Column D is where the count will go

The "*" in my formula were "wildcards" in case there are values before the postcodes (like the rest of an address for example). I realised that I didn't mean to put the closing &"*" in the formula. It should have read (shortened range to suit the size of my simplified example):

=COUNTIF($B$2:$B$601,"*"&C2)

In the linked workbook I have put the first formula in cell D2, but you will see you can just drag-fill it down the rest of the postcodes in column C

 

[bhos123]

ok thanks fishboy and bhos. its still not getting me a proper result.
removing duplicates worked but the countif suggestion is not working

as per fishboy's idea here is the formula I'm using:
=COUNTIF($A$5:$A$11477,"*"&Sheet1!B2&"*")

As I don't know what the asterix is for, I left it blank. The formula resulted in a blank which is not right but even if it did work, fishboy is suggesting dragging it down across the 200 or so rows of unique post codes... but don't I need to be dragging it down across the 600 or so rows of non-unique post codes?

(there are about 600 company names that I'm looking at having filtered the ones I want within A2:A11477).
And as per bhos instruction Sheet1!B2 is the first row of the unique post code column.

Not sure if people follow..Is there a way to attach a spreadsheet to illustrate? Would that help you help me?

the formula would be : B2=COUNTIF(sheet1!$A$5:$A$11477,A2) - Sheet1 is ur original column

 

[ludo2016]

Thanks Fishboy. Problem solved!
btw, I'm not using addresses, only postcodes, so don't think there was an absolute need to cater for wildcards? but anyhow.. it seems to have worked.
now I'm learning about bell curves from https://www.youtube.com/watch?v=_PqnDYMO3lw. Thanks once again Fishboy et al, and thanks Mr Excel!

 

[Fishboy]

Thanks Fishboy. Problem solved!
btw, I'm not using addresses, only postcodes, so don't think there was an absolute need to cater for wildcards? but anyhow.. it seems to have worked.
now I'm learning about bell curves from https://www.youtube.com/watch?v=_PqnDYMO3lw. Thanks once again Fishboy et al, and thanks Mr Excel!

Ah! Indeed, if there are no addresses just postcode then the whole wildcard bit is most definitely redundant and unnecessary.

Anyway, happy to help. Glad to hear you got it sorted.