Excel formula help !

MehulLakhani

MehulLakhani

MehulLakhani

Associate
Joined
12 Jan 2003
Posts
1,227
Location
Watford
Hi All

I have been asked to work on a spreadsheet at work, and need some help ! Essentially, I need to count up the number of unique address from a spreadsheet, like the one below.





So what I need is a forumla that will count the number of unique entries in one column only, if that makes sense, to work out the number of househols that we need to mailshot. There are blank rows inbetween , and I need to make sure that they are not counted.

Any forumla ideas gratefully recieved !

Thanks

mehul
 
Probably an easier way, but i think i'd use a VLookup. Not got time to explain now in detail sadly, but suffice to say have all the data in a sheet, ranked in ascending order for the address.

Actually, unless you have a list of ALL the addresses to put a qty to the number of appearances next to this wont help you!
 
Last edited:
Does it need to be a single formula that will be continually used or do you just need to find out the answer as a one off?

As a one off I would sort the column (which will put all duplicate entries together) then in the next column put a formula along the lines of =IF(A1=A2,0,1) in each cell, then sum that column to get an answer.

You may also have to take account of how clean the data is (this will depend on where it's originated from), for example is there any chance that you may have the same address twice but with slightly different formatting or spacing? If so this will make your task a lot harder. If they're all UK addresses you may be best off trying to extract house numbers and postcodes in a consistent format for example.
 
vLookup is probably the most sensible way, you might be able to do it with a pivot table and the sum function in that but I'm not sure how well it handles spaces (if at all) and I don't have Excel on this machine to test it.
 
Personally I would use a pivot table. Spaces you can just untick.

Street name in column field, street name as a count field also in value section if you need to know how many of each there are.
 
I have looked into VLOOKUP- but looks like I would need to create a formula for each address ? Unless I am reading the instructions wrong ?

With 3000 addresses , that is going to be difficult !

Any other ideas ?

R

Mehul
 
Use the advanced filter to create unique addresses in another column. Then use a CountIf forumula against each of these using the original column of data as the range to tell you how many of each you have.

Obviously the number of addressed returned by the "unique" advanced filter is the number of unique addresses!

Or you could just pivot the lot and see how many elements there are in the pivot table.
 
Another option would be to sort the data by the address column, and use a double countif.

After sorting your adress's in ascending order, lets say they start in cell B3 and go downwards.

In Cell C2 I would have the formula:-

=COUNTIF(C3:$C$3000,0)
Click to expand...

Then in cell C3 downwards, I would drag the formula down:-

=COUNTIF(B4:$B$3000,B3)
Click to expand...

Sorted.

So for example, if B3->B10 were filled as follows, you would have the below results:-


Code: 
[FONT="Courier New"]
  A         B         C
 1          
 2                     5
 3          a          1
 4          a          0
 5          b          0
 6          c          2
 7          c          1 
 8          c          0
 9          d          0
10          e          0
[/FONT]


C2 correctly giving the results of 5 unique different address's in column B
 
You must log in or register to reply here.
Back
Top Bottom