List unique Values from different columns: How to...
 

I have been looking for an entire day to find a solution to this. Found
'some' answers but not the thing I need.

I got a few workbooks with a lot of worksheets. For almost every workbook
there needs to be a list of unique values coming from different worksheet
(always in the same workbook). I would like to get a clue on how to do that.

So suppose worksheet 1, and worksheet 2 I would like to create a list on
worksheet 3 gathering all unique values from columns B (of both worksheet 1
and 2).

So far I have been directed towards VBA, some commercial add-ins and the
classic copy and paste.
But you can imagine that I do not like to pay for such a (in my opinion)
basic function, nor copy over 20.000 rows (risking a lot of errors and
'forgotten' rows) and VBA well... I'm not good at it...

I tried to filter data, but can only do that for a single column. And I
really need a list, so dropdown won't do...

Suggestions very much appreciated.

List unique Values from different columns: How to...
 

I would use a 3 stage process with Data--Filter--Adavnced Filter--Unique
values only--Copy to another place

On each sheet use this to make a list of the unique data on that sheet

Then move the list of unique data from each sheet to the final destination
sheet, appending it so you only have one list.

Then reapeat the advanced filter unique values on the destination sheet to
remove an common cross-sheet values

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"dakke" wrote in message
...
I have been looking for an entire day to find a solution to this. Found
'some' answers but not the thing I need.

I got a few workbooks with a lot of worksheets. For almost every workbook
there needs to be a list of unique values coming from different worksheet
(always in the same workbook). I would like to get a clue on how to do
that.

So suppose worksheet 1, and worksheet 2 I would like to create a list on
worksheet 3 gathering all unique values from columns B (of both worksheet
1
and 2).

So far I have been directed towards VBA, some commercial add-ins and the
classic copy and paste.
But you can imagine that I do not like to pay for such a (in my opinion)
basic function, nor copy over 20.000 rows (risking a lot of errors and
'forgotten' rows) and VBA well... I'm not good at it...

I tried to filter data, but can only do that for a single column. And I
really need a list, so dropdown won't do...

Suggestions very much appreciated.

List unique Values from different columns: How to...
 

This copy/paste does not really solve the issue, since it will end up doing
some more manual work. It will most likely reduce the errors though.

So there is no other solution than to copy/paste?

List unique Values from different columns: How to...
 

There IS a solution if you accept using VBA!

Regards,
Stefi

€ždakke€� ezt Ã*rta:

This copy/paste does not really solve the issue, since it will end up doing
some more manual work. It will most likely reduce the errors though.

So there is no other solution than to copy/paste?

List unique Values from different columns: How to...
 

Well you either do it manually or automate it using VBA (as Stefi said).

If you don't want to use VBA you have to do it manually ...

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"dakke" wrote in message
...
This copy/paste does not really solve the issue, since it will end up
doing
some more manual work. It will most likely reduce the errors though.

So there is no other solution than to copy/paste?

List unique Values from different columns: How to...
 

Another option is to use the Data Consolidate command...
Input the ranges with all boxes checked and 'Count' selected.

When it's run, resize columns then use EditReplace "COUNT(" with "N(" on
numeric columns or "T(" on text columns.

Now you can copy these rows to a new worksheet using Alt+;

[Note: to avoid blanks you can use use 'lookup(9e99,' or 'lookup("zzzzz",'
for the replacements instead]

List unique Values from different columns: How to...
 

Or use Pivot Table.
No VBA or formulas required
if your data is numeric.
If it is text, use the method shown.
All automatic, except PT refresh.
http://www.freefilehosting.net/download/3c3e6