Excel - Identify Rows Having Duplicates In 1st Column

Jerry L
Jerry L used Ask the Experts™
on
Values to be identified as duplicates will be in the second (B) column (with header). I will need to identify rows with duplicate values in that column, but preserve all values in the other columns of that row.

It would be good to insert the string, "Duplicate" (without quotes) in the column to the far left of the duplicates other than the 1st instance. For the 1st instance, mark as "First Instance", would be desirable. Then I can sort on that column. Non-duplicates should not be marked at all.

Spreadsheet has 75,000 rows.

Using an Inline Function would probably be preferred if possible, rather than a nested VBA loop type macro. But please do what you think best. I'm unable to find a reasonable solution searching the web. -- BTW:  CONDITIONAL FORMATTING hangs the program with too many ROWS.

Sample Content: (sorry, but the "Indent" tags are not working). The point is that the values in other columns of those rows do not affect the logic.
Column A is Blank

Column-B-Title   Column-C-Title   Other-Columns-w-Title
5555
Some-Value
5555
Some-Other
5555
Not Important
3333
Some
3333
Thing
XXXXXX
xyz
XXXXXX
abc
Anything
pqr
Else
stu

(Note: Sorry about all the edits. Couldn't find a "Preview" button prior to publishing. Not familiar with this new interface.)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
It would be extremely helpful to post a sample excel and how exactly you will manipulate the data...work on Excel or Access ?
Operations Manager
Commented:
SOLUTION
It seems there is a simple solution for what I'm trying to do.
Data > Data Tools > Remove Duplicates

0. This method is destructive. Make a copy of your data and save the original as read only.
1. Insert a column that contains a numbered count of all the rows, starting from 1, 2, 3, ..., 75000. (This will help to compare to the original and to confirm proper values across the rows.)
2. Select all the data in all columns and rows, including the numbered column.
3. Select "Data > Data Tools > Remove Duplicates". (Turns out this a native function in recent versions of MS Excel, but mine only appears as an icon in the toolbar ribbon with no descriptive text.)
4. In the dialog that appears, uncheck ALL the boxes (You can use the "Spacebar" to uncheck, then the "Down Arrow" key for sheets with many columns), but leave checked the column from which to remove duplicates. Select, "Columns have headers", if true.
5. You can do this for multiple columns if needed. For example, I had duplicate product codes in one column and duplicate product titles in another column.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial