How to classify multible entries in excel?

Question

I have an excel sheet with around 30,000 entries with the following columns, Postcode Northing and easting.

The spreadsheet is a list of peoples postcodes and location and i need to classify the entries as follows. If there are 3 or more duplicate postcodes or northing&easting to mark in a new column as 1, so down the postcode column if there are 5 identical postcodes in a new column mark 1. However I havent a clue which tool or command or method should be used.

Thanks for the help.

Answer 1

I don't understand the northing & easting bit, but you can use COUNTIF to identify more than four duplicate postcodes. In a blank column put

=IF(COUNTIF(A$1:A$30000,A1)>4,1,"")

and drag this down. I'm sure you can figure out how to add the northing & easting to this formula.

Answer 2

Hello,



I'm guessing this is a GIS question due to your Nothings and Eastings... I've been in the same boat for a while and have found an easy way around this.



The tool I would use to do this is a downloadable add-in from excel from DigDB.



It is a free trial for 15 days, if these add-ins are worth your while (and I think a few of them are fantastic) it'll cost you $39 for half a year.



What this program does is gives you another menu option on Excel (named as "DigBB"). From here it's a simple as going DigBB > Table > Filter Uniques By Group



You can choose to filter by any column or by all to get your desired result.



This will give you a grouped list, with a new column generated that shows the amount of duplicates. It is then very easy to group from these values.



Another tool that this program has after grouping these is to delete any hidden columns (hidden columns will include anything that has already been duplicated).



Of course it is possible to create a visual basic file that will do this, but if you're strapped for time as most GIS workers are I'd recommend checking this out.



Cheers,

Answer 3

You could look at pivot tables, that might do it. They create a new sheet and depends what you select it will list the selection and can be used to count the number of occurances.



So if the post code AB12 3CD occurs 3 times, column B can say "3"



In column C - which should be blank type in the formula, say in cell C5, "=IF(B1>1,1,0)" - this will put a 1 in the column if that post code is used more than once (Cell A5 is the top of the pivot table data). Call this sheet "Pivottable"



Go back to the first table now, assuming Postcode is column, A, Northing B and Easting is in C. Type this in column D, assuming A1 is the first post code use the vlookup formula,

"=VLOOKUP(A1,pivottable!A:C,3,false)"



This should now put a 1 or a 0 in column D depending if the post code is used more than once. You can do the same with Northing or Easting if you want.





-EDIT-

I have been an idiot and forgot to mention the COUNTIF function! that answer is easier and quicker than mine.



(in my defence, Pivot tables are worth learning to use though)

Answer 4

Try using the Consolidate command in the Data Tools group on the Data tab.