Extract unique items from a Table and place in a column

**Evilshrew** · 10-09-2007, 12:00 PM

All,

I feel cheated having to write this but I need some help.

I want to create a numerical list (automatically) from a table.

Simple............

What I have is:

5 5 5
2 2 2
1 1 6
7 3 4
81 45 82

What I want it

1
2
3
4
5
6
7
45
81
82

I am being a total dufus and cant get this worked out.

I have tried pivots, if and buts but would possibly sell my soul, possibly a kidney to sort this one.

I know it will be a simple one but right now I cant see the forest for the trees and my VB skills are sorely lacking.

Anything would be gratefully recieved.

Regards

Stony

ps Sorry if this has already been sorted in the forum but I could not see it.

**VBA Noob** · 10-09-2007, 12:03 PM

This will get you close

=SMALL($A$1:$C$5,ROW(A1))

VBA Noob

**NBVC** · 10-09-2007, 12:44 PM

If you download and install the free Morefunc utility from this site: http://xcell05.free.fr/english/ then you can use the Uniquevalues() function to do exactly what you want with one formula.

If your data is in A1:C5, then first select a minimum of 15 cells (to cover possibility of all entries being unique) within one column where you want the results to appear. Then in the formula bar at the top of the Excel spreadsheet, enter formula: =UNIQUEVALUES(A1:C5,1) and hold the CTRL+SHIFT keys down while pressing ENTER.... you should see your sorted unique value list appear.

**Evilshrew** · 10-26-2007, 04:30 AM

Chaps

That is great but I work in an enviroment that will not allow any additions to a program. God Love IT.

Is there a way of completing the list with a vb program that I can add to a macro?

Cheers

Stony

**VBA Noob** · 10-26-2007, 04:46 PM

See if this helps. I named the range but you can use a range like

Please Login or Register  to view this content.

Code goes in a regular module and will create the list in the next column

http://www.contextures.com/xlvba01.html#Regular

Please Login or Register  to view this content.

VBA Noob

**jindon** · 10-28-2007, 02:36 AM

Originally Posted by Evilshrew

What I have is:

5 5 5
2 2 2
1 1 6
7 3 4
81 45 82

What I want it

1
2
3
4
5
6
7
45
81
82

try

Please Login or Register  to view this content.

Extract unique items from a Table and place in a column

LinkBack

Thread Tools

Rate This Thread

Display

Extract unique items from a Table and place in a column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions