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

D

dakke

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.
 
C

Charles Williams

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
 
D

dakke

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?
 
S

Stefi

There IS a solution if you accept using VBA!

Regards,
Stefi

„dakke†ezt írta:
 
C

Charles Williams

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
 
L

Lori

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 Edit>Replace "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]
 
Top