# Extract unique values from a range in excel

• Hi to everyone, I need your help. I have two columns like this:

Col A (table) Col B (name)
1 Jack
1 Jack
1 Jack
1 Jack
2
2
2
2 Ben
3 Ben
3
3
3
... ...

I need to create a new list in which I resume the data like this:

Col A (table) Col B (name)
1 Jack
2 Ben
3 Ben
... ...

So I need to delete duplicates and write 2 new columns in which there are the name of the person and his table. Is it possible? Maybe with a macro?

Thanks for the help

• Re: Extract unique values from a range in excel

C1=SUM(IF(FREQUENCY(IF(\$A\$1:\$A\$32&\$B\$1:\$B\$32<>"",MATCH("~"&\$A\$1:\$A\$32&\$B\$1:\$B\$32,\$A\$1:\$A\$32&\$B\$1:\$B\$32&"",0)),ROW(\$A\$1:\$A\$32)-ROW(\$A\$1)+1),1)) with C+S+E

and in D1=IF(ROWS(E\$8:E8)>\$C\$1,"",INDEX(\$A\$1:\$A\$32&"-"&\$B\$1:\$B\$32,SMALL(IF(FREQUENCY(IF(\$A\$1:\$A\$32&\$B\$1:\$B\$32<>"",MATCH("~"&\$A\$1:\$A\$32&\$B\$1:\$B\$32,\$A\$1:\$A\$32&\$B\$1:\$B\$32&"",0)),ROW(\$A\$1:\$A\$32)-ROW(\$A\$1)+1),ROW(\$A\$1:\$A\$32)-ROW(\$A\$1)+1),ROWS(E\$8:E8)))) with C+S+E and fill down

• Re: Extract unique values from a range in excel

or

or

• Re: Extract unique values from a range in excel

Great! The VBA code was perfect, and works great! :wowee: Thanks a lot!

I have one more question. I know thats it's quite difficult but I want to try. Now I have this situation.

Col A (table) Col B (name)
1 Jack
1 Jack
1 Jack
1 Jack
2 Jack
2
2
2 Ben
3 Ben
3 Ben
3
3
... ...

Col D (table) Col E (name)
1 Jack
2 Jack
2 Ben
3 Ben
... ...

Is there a way to create even a third column where there are the number of times that the Name appears in each group of table (every table has 4 seats), I want to obtain something like this

Col D (table) Col E (name) Col F (Seats)
1 Jack 4
2 Jack 1
2 Ben 1
3 Ben 2
... ... ...

• Re: Extract unique values from a range in excel

Try

• Re: Extract unique values from a range in excel

Hi ginobuonpanino,
You just need to add another dimension to the array like..

• Re: Extract unique values from a range in excel

if you need to sort on table number you can add the bubble sort

• Re: Extract unique values from a range in excel

OK, Pike's code, seems to work better, just a 2 last question. As you can see, in the new table the title of the third row is 1, can I write seats instead of 1? and why it is considered the last table (280), even if there is nothing on his cells? I give you a screen of the new table generated by the code of Pike

Thanks again to everyone for the help..

## Images

• Re: Extract unique values from a range in excel

no screen shoots example workbook

you can take out the sort stuff .. it sorts by Table number then Name then seats

• Re: Extract unique values from a range in excel

try.. this one for speed

• Re: Extract unique values from a range in excel

Ok, I made some changes, because I don't need to sort results, that's what I'm using now:

And that's what I found with this code. As You can see It works perfectly, but I can't understand why it continues to write even a blank cell at table 280, I check this cells and they are the same of the others. Every cells in A column contains this formula, in A3, for example, I have put =IF(Name!C5<>0;Name!C5;""), to avoid zero value, I don't if it's because of this formula.

[TABLE="width: 500"]

[tr]

[td]

Name

[/td]

[td]

Table

[/td]

[td]

Seats

[/td]

[/tr]

[tr]

[td]

Jake

[/td]

[td]

1

[/td]

[td]

8

[/td]

[/tr]

[tr]

[td]

Jake

[/td]

[td]

1

[/td]

[td]

1

[/td]

[/tr]

[tr]

[td]

Mark

[/td]

[td]

2

[/td]

[td]

2

[/td]

[/tr]

[tr]

[td]

Peter

[/td]

[td]

2

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td][/td]

[td]

280

[/td]

[td]

8

[/td]

[/tr]

[/TABLE]

• Re: Extract unique values from a range in excel

VBA says that myArray in Next myArray it's a not defined variable.

Quote from pike;751479

try.. this one for speed

• Re: Extract unique values from a range in excel

hi ginobuonpanino

with the formula there is no need to look in the mycell offset (One column to right)
Try this it look in the mycell for each value in column A

• Re: Extract unique values from a range in excel

Yessss! Now it works as I want. Thank you so much!!!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!