# Thread: Formula to Extract Unique Data Values (2007)

1. ## Formula to Extract Unique Data Values (2007)

I have three lists of data One in Col A, another in Col B and the third one in Col C. I would like a formula to extract all the unique data so that the appear in Col D

See Attached sample

Your assistance will be most appreciated

Regards

Howard

2. ## Re: Formula to Extract Unique Data Values (2007)

Does it have to be an XL2007 solution or is the dataset unique to XL2007?

If not could you save a copy as an earlier version for those of us without XL2007 (and either using versions where the converter does not work or is not allowed to do since a work computer does not grant admin access to install programs)?

Steve

3. ## Re: Formula to Extract Unique Data Values (2007)

Hi Steve

Any version would be fine

Regards

Howard

4. ## Re: Formula to Extract Unique Data Values (2007)

You can add this code to module:

<pre>Option Explicit
Function UniqueList(rng As Range) As Variant
Dim sNames() As String
Dim rCell As Range
Dim colUnique As New Collection
Dim x As Integer
Dim y As Integer
Dim sTemp As String
Dim sTemp2 As String
Dim AWF As WorksheetFunction

Set AWF = Application.WorksheetFunction
ReDim sNames(1 To rng.Count)
For x = 1 To rng.Count
sNames(x) = ""
Next
On Error Resume Next
For Each rCell In rng
Next
On Error GoTo 0

For x = 1 To colUnique.Count - 1
For y = x + 1 To colUnique.Count
If colUnique(x) > colUnique(y) Then
sTemp = colUnique(x)
sTemp2 = colUnique(y)
colUnique.Remove x + 1
colUnique.Remove y + 1
End If
Next
Next

For x = 1 To colUnique.Count
sNames(x) = colUnique(x)
Next
UniqueList = AWF.Transpose(sNames)
Set rCell = Nothing
Set rng = Nothing
Set AWF = Nothing
End Function</pre>

Then you can select D37 (or an appropriately large range) and enter:
=uniquelist(A3:C5)
and confirm with ctrl-shift-enter. The names will be sorted. [Note I added trimming the text string or else some of the names were not unique. One of the Peter Lyons has a dbl-space between first and last names and one of he Howard Davids has several trailing spaces]

If the range you select is more than the number of cells in the selected range then those cells will have a #N/A error. cells will have the null if they are for > than the number of unique names but < still within the rangecount. If your range is not large enough you will only get the number of unique items in the range defined by the formula.

Steve

5. ## Re: Formula to Extract Unique Data Values (2007)

Hi Steve

Thanks for the help

Regards

Howard

6. ## Re: Formula to Extract Unique Data Values (2007)

The OP wanted a formula to extract all the unique data

A quite Challenge to have a formula way in remove duplicate at 3 columns datas

Please enter the array formula at D2 and copy down

{=IF(OR((A\$2:C\$10<>"")*(COUNTIF(D\$11,A\$2:C\$10)=0)),INDIRECT(TEXT(MIN(IF((A\$2:C\$10<>"" )*(COUNTIF(D\$11,A\$2:C\$10)=0),ROW(A\$2:C\$10)*1000+COLUMN(A:C))),"R 0C???"),),"")}

Regards
Bosco

7. ## Re: Formula to Extract Unique Data Values (2007)

Hi Bosco

Thanks for the help. Quite a complex formula that you have set up

Regards

Howard

8. ## Re: Formula to Extract Unique Data Values (2007)

The formula can shorten to :

{=INDIRECT(TEXT(MIN(IF((A\$2:C\$10<>"")*COUNTIF(D\$11,A\$2:C\$10),6000,ROW(\$2:\$10)*1000+{1,2,3})),"R0C?? ?"),)&""}

Regards
Bosco

9. ## Re: Formula to Extract Unique Data Values (2007)

Hi Bosco

Thanks for the help. What does ROC??? mean in the formula

Regards

Howard

10. ## Re: Formula to Extract Unique Data Values (2007)

R0C??? <--- R1C1 reference style

Excel indicates the location of a cell with an "R" followed by a row number

and a "C" followed by a column number

R1C1 <--- A1

R2C1 <--- A2

Example :

In Cell D2, the formula is :

{=INDIRECT(TEXT(MIN(IF((A\$2:C\$10<>"")*COUNTIF(D\$11,A\$2:C\$10),6000,ROW(\$2:\$10)*1000+{1,2,3})),"R0C?? ?"),)}

=INDIRECT(TEXT(MIN({2001,2002,2003;3001,3002,3003; 4001,4002,4003;5001,5002,5003;6001,6002,6003;7001, 7002,7003;8001,8002,8003;9001,9002,9003;10001,1000 2,10003}),"R0C???"),)

=INDIRECT(TEXT(2001,"R0C???"),)

=INDIRECT("R2C001",) <--- cell A2

="Howard Davids"

Regards
Bosco

11. ## Re: Formula to Extract Unique Data Values (2007)

It's not ROC??? (with a letter O) but R0C??? (with the digit zero). It's the format string for the TEXT function; it formats the first argument as an R1C1 style cell reference.

Note: this also means that the formula may not work in other language versions of Excel without modification, since they might use other letters than R and/or C for Row and Column.

12. ## Re: Formula to Extract Unique Data Values (2007)

Hi Bosco

Thanks or the explanation-its much clearer now

Regards

Howard

13. ## Re: Formula to Extract Unique Data Values (2007)

{=INDIRECT(TEXT(MIN(IF((A\$2:C\$10<>"")*COUNTIF(D\$11,A\$2:C\$10),6000,ROW(\$2:\$10)*1000+{1,2,3})),"R0C?? ?"),)}

Two more points of the formula to be cleared that

1st point (column numbers of the data range) :

{=………..+{1,2,3})),"R0C???"),)}

This part …...{1,2,3}…….,<------ that mean the data ranges, were located at "Column A to Column C"

If the data ranges were located at Column E to Column K

then, the formula will be modified to

…...{,5,6,7,8,9,10,11}…….

or, replaced by :

…...COLUMN(E:K)….

2nd point ( error control )

{=................IF(.........,6000,.........),... ......}

This part …...6000…….,<------ that mean, the result will return to A6000 (giving BLANK) by the IF function evaluates to TRUE

Regards
Bosco

14. ## Re: Formula to Extract Unique Data Values (2007)

Hi Bosco

Thanks for the info

Regards

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•