Mark duplicates with a Y?

[2013-10-22]

I have a database table with lots of duplicate words in it and have exported the data to Excel.

Below is an extract - there are over 4,000 rows in total, where the duplicate count ranges from 8 down to 2.

The table is sorted by count_if and then word.

I'd like to find a way to mark all rows with a 'y' in the "del" column for all lines in each block of words (except the first line), so that I can filter by the y value in that column, copy the IDs and then enter in them into my database via SQL to delete the duplicates, e.g.

DELETE FROM my_table WHERE id IN (23633, 24551, 25629...);

Is that possible?

Any help would be much appreciated.

Thanks

id	word	cat	count_if	del
21790	absolute	1	8
23633	absolute	3	8	y
24551	absolute	3	8	y
25629	absolute	3	8	y
26089	absolute	3	8	y
27020	absolute	3	8	y
27506	absolute	3	8	y
28029	absolute	3	8	y
22147	shell	33	8
24162	shell	3	8	y
25246	shell	3	8	y
26006	shell	3	8	y
27444	shell	3	8	y
27939	shell	3	8	y
28347	shell	3	8	y
28682	shell	3	8	y
11038	string	22	7
22339	string	3	7	y
22794	string	3	7	y
24196	string	3	7	y
24406	string	3	7	y
24531	string	3	7	y
25597	string	3	7	y

<tbody>
</tbody>

 

[Joe4]

Say that your first row of actual data is on row 2, and your "word" column is column B, and your "del" column is column E.
Then enter this formula in cell E2 and copy down for all rows:

=IF(COUNTIF(B$2:B2,B2)>1,"y","")

That should return "y"s in that column exactly as you want.

 

[mikerickson]

in E2 you could put the formula =IF(MATCH(B2, B:B, 0)<>ROW(B2), "y", "") and drag down.

 

[gaz_chops]

Try

=IF(COUNTIF($B$2:B2,B2)>1,"Y","")

 

[steve the fish]

This sort of formula dragged down:

=IF(COUNTIF($B$2:B2,B2)>1,"Y","")

 

[bob33]

I'm sorry, I don't understand what you want to happen here: "I'd like to find a way to mark all rows with a 'y' in the "del" column for all lines in each block of words (except the first line)"
There are already some y's on the page; are you needing a way to indicate rows where the ID is a duplicate of the ID of another row?
What "block of words" ?
Might really be easy but can't tell from what I read so far.

 

[2013-10-22]

Fixed - thank you to all!