Deleting rows (lots of rows data)

[jennajen]

Hey everyone,
Newbie here using Excel 2007.

I've googled so many different ways, tried VBA codes, tried making match case formulas, and nothing seems to work since most compare columns.

Scenario:

- have a sheet with several columns (headers: account names, IDs, revenue, margins, etc.)
- need formula or VBA code/macro to delete rows within sheet only if column D (IDs) contains duplicates (case sensitive must be enabled)

I understand excel has a feature to remove duplicates, but it is not case sensitive.

For example (information made used for simplicity):


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]ID[/TD]
[TD]revenue[/TD]
[TD]row[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yap[/TD]
[TD]1dog[/TD]
[TD]10k[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GOO[/TD]
[TD]1Dog[/TD]
[TD]30k[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yap[/TD]
[TD]1dog[/TD]
[TD]10k[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hee[/TD]
[TD]1laz[/TD]
[TD]90k[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hee[/TD]
[TD]1laz[/TD]
[TD]90k[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Haa[/TD]
[TD]1laZ[/TD]
[TD]100k[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the example above I want the formula to keep "Yap", "Hee", "Haa", "GOO", based on the column containing duplicates.
I don't want it to retain two "Yap"s or two "Hee"s because they are the same account (based on ID "1dog" "1laz" being identical in casing).

Essentially what my file/report does is it shows how much revenue I should allocate to different venues and I'd like to figure a way to remove rows if there is duplicate data in a specific column.
Again, case sensitive values must be picked up.

I looked through threads here for solutions, but didn't find any to fix my specific concern. If anyone can help out I'd greatly appreciate it!
Thank you!

 

[mikerickson]

You could use a helper column with the formula
=D1&SUMPRODUCT((EXACT(MID(D1,{1,2,3,4,5,6,7},1),MID(UPPER(D1),{1,2,3,4,5,6,7},1)))*(10^{6,5,4,3,2,1,0}))

and then remove duplicates based on that column