DELETE Duplicate Values

[poizones]

Hello.

I just need some help here. I can't figure out what formula would I use to eliminate duplicate values reflected from above data. For a clear presentation I have a sample excel file attached (not the actual file I am using but with the same situation).

Thank you for your help.

[chandy123987]

use the option Data , Remove Duplicates

[poizones]

I don't know how to use the formula. Can you help me how?

I need the formula in each cells below not to be deleted also. But the Data option and Remove Duplicates, also eliminate the formula.

The values in column B may change or may be added from its another source.

Thank you.

[Sam Capricci]

chandy123987 is recommending you go to the data tab at the top, highlight the column (or columns) you want to remove the duplicates from, use the remove duplicates function under data tools.
if you need a formula you can use =COUNTIF($B$4:B4,B4) if the duplicates are in column D and drag down. Where the numbers change from 1 to 2 you have a duplicate, filter ascending then remove all rows with values greater than 1.
edited to use column B instead.

[poizones]

It is not what I really need to be the result. The data in column B are irregular. We cannot make a permanent reference to names which are duplicated in column D using the formula =COUNTIF($B$4:B4,B4). What I need is a formula that will only delete the duplicate values in column D but not the formula since the reference values in column B might change anytime depending on the other workbook or source.

[Sam Capricci]

well a formula itself will not delete duplicates, for that you need VBA, but it can hide them. try this instead in column D and dragged down...
=IF(COUNTIF($B$2:B2,B2)=1,B2,"")

[63falcondude]

Try this in D2:

=IFERROR(INDEX(B$2:B$12,MATCH(0,COUNTIF(D$1:D1,B$2:B$12),0)),"") Ctrl Shift Enter

Drag the formula down as far as needed.

This will give you a unique list of the values from column B.

[poizones]

Sam,

You're brilliant. Thank you so much.

I am so happy and satisfied with the result.

MORE POWER to you.

[63falcondude]

Thanks for the rep!

[poizones]

Falcon,

Thanks. I appreciate this. At least I have options on which formula I will be using.

[sandy666]

If you want result like this

ID	NAMES
542644	JAMES V. CORCIEGA
154489	BORGIE Q. FILLAS
602216	JANNETTE S. GALAMO
894330	FELIX D. QUIPO
577812	JORDAN LYN F. NADDIE
110239	KRISTINE F. VILLASECA

use PowerQuery - it will eliminate duplicates and group the rest together (in a separate table)