List Data From Duplicates

[Kumara_faith]

Hi ,

I have a spreadsheet with a list of reference numbers that is duplicated. I need to create a formula that would read from that list and populate a single reference number only from that list.

So,if the list has 5 reference number, then those five reference number will need to populate in another table excluding all the duplicates.

In the attached example, the original date is in the table titled "Original Data" and the desired result from the formula is listed in the table titled "Result". Appreciate help how this can be done.

Appreciate your time and patience.

[Paul]

Hi Kumara, there are many posts and sites that show you how to do this.

Here are some examples not using formulas:
http://www.mrexcel.com/tip138.shtml

If you must use formulas, I'd recommend:
1. Assuming your original data is in B4:B31, in C4 enter the formula:
=IF(COUNTIF($B$4:$B4,B4)=1,B4,"")
2. Fill that formula down to C31.
3. In D4 put the array formula:

Please Login or Register  to view this content.

After typing (or copying/pasting) that formula, you must press CTRL+SHIFT+ENTER, not just ENTER. If done correctly Excel will automatically insert braces, {}, around the formula. Don't add braces manually, it won't work that way.
4. Fill that formula down to cell D31.
5. You can hide column C if you prefer not to see that list.

[Kumara_faith]

Thank you pjoaquin. That worked and the link was very useful.

Thank you for your time and patience.