Remove Duplicates from One Field in Excel Spreadsheet & Delete that Entire Row

[lyndy256]

Hi,
I have a data set of villages. There are more than one village per village tract, but I want the data set to contain only 1 village per village tract. It doesn't matter what village in the village tract I keep. I just want to reduce the data set by eliminating multiple villages per village tract.

I know I can use Vlookup to remove duplicates from one column, but I would like to also remove all the records associated with the duplicate village tracts so that I end up with a smaller data set that has one village per village tract.

The field of village tracts = vt.

Any guidance would be really appreciated as I am trying to do this manually and it's taking way to long. There must be an easier way.

See attached.

THanks,
Lyndy

[ramananhrm]

Hi Lyndy,

Please try these options or you can use Pivot table to generate Unique list.

Note: Ctrl+Shift+Enter is required for both options

Option1:
=INDEX($F$2:$F$831,MATCH(0,COUNTIFS($L$1:L1,$F$2:$F$831),0))

Option2:
=INDEX($F$2:$F$831,SMALL(IF(FREQUENCY(IF($F$2:$F$831<>"",MATCH($F$2:$F$831,$F$2:$F$831,0)),ROW($F$2:$F$831)-ROW($F$2)+1),ROW($F$2:$F$831)-ROW($F$2)+1),ROWS($M$2:M2)))

[lyndy256]

Majorly Awesome! Thanks!