Identifying Unique Cells in Excel column. 1

[realjd]

I'm looking for a macro or something that will let me scan a column of cells and highlight or maybe indicate in a seperate columns a unique record. There are many duplicate entries in the column but I would like to mark which records are only listed once in the column. Any help would be appreciated. TIA

 

[Zathras]

You don't need VBA.

From the Excel menu:
Data/Filter/Advanced Filter...

Select Copy to another location
Enter your column in the List Range: (e.g. E1:E9999)
Enter an empty column in the Copy to: (e.g. AA1)
Check Unique records only
Click OK

 

[realjd]

Thanks for the reply. Yes, it does work, but I've got a huge list of records. I want to see where in the list the unique record is located. When I paste to a new column, it indicates all the unique records, but they're just filled in consecutively.

IE: 400 records in a column, with 5 unique records. When I paste only row 1-5 are filled with the records.

 

[Zathras]

Sorry, I misread your request. Here is what you want:
Assuming your data are in column A, rows 1 to 400, put this formula in cell B1:
[blue]

  =COUNTIF($A$1:$A$400,A1)

[/color]
and copy down to B400. Unique values wil have a count of 1.

You can take it a step further by selecting column A and put in conditional formatting (Format/Conditional Formatting...) and enter a formula of
[blue]

   =OFFSET(A1,0,1)=1

[/color]
then choose a pattern color to highlight the cells with unique values. (Click "Format..." and select the Patterns tab then choose a color.)

Sorry for the wrong answer before.

 

[SBendBuckeye]

All the credit goes to Zathras because I just piggy-backed on his work above, but you can combine everything into a conditional formatting formula as below if the only thing you want to do is highlight those which are unique:

=(COUNTIF($A$1:$A$400,A1)=1)

Have a great day!

 

[realjd]

Very cool, thx!

I plan on incorporating this into a macro.

 

[realjd]

Is there a code to check the entire column A?

I want to use a macro to check the column A, but the number of records could change. So instead of A$400, Can I put a variable to check the entire column until it reaches empty cells?

 

[SBendBuckeye]

Try =(COUNTIF($A:$A,A1)=1). I'm at work and we have Excel97 so I can't test this. I believe it should work though. Please repost if it does not.

Also, look at the Excel object SpecialCells. It returns all kinds of interesting information, one of which is the last row of a sheet (equivalent to Ctrl+End).

Excel has very strong macro recording features. Assuming your data in column A begins in row 1 and has no empty cells, let it record the VBA code for you. Turn on the macro recorder, select A1, hit <End><Down> and then shut off the recorder. Paste that code into your macro and add the following line just after it:

lngRow = Selection.Row

Now you have the row number for the last data row in column A so you can build your string from there.

Good LucK!