Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 968
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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?
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,646
Default 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?

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 968
Default 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?





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 272
Default 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]


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare data in 2 columns for unique values Steve C[_2_] Excel Discussion (Misc queries) 2 November 29th 07 05:26 PM
Identify & List unique values from a list using functions/formulas momtoaj Excel Worksheet Functions 3 May 31st 07 06:18 PM
Count unique values and create list based on these values vipa2000 Excel Worksheet Functions 7 August 5th 05 01:17 AM
create list of unique values from a column with repeated values? Chad Schaben Excel Worksheet Functions 1 July 8th 05 10:25 PM
How do I get unique values from 2 columns? akmccarthy Excel Discussion (Misc queries) 2 December 13th 04 11:47 PM


All times are GMT +1. The time now is 01:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"