Count Unique accross mulitple columns

[Rob Drummond, Jr]

I have tried everything I can think to do and I can not seem to get the
results I want. What I have is a spreadsheet with several columns and I want
to have excel count the unique values across multiple columns. The columns I
want counted are part number columns that are separated by cost columns. I
can get excel to show the unique part numbers for a given column, but it is
possible for the same part number to be in more than one column. Here is the
basic layout....

part# cost part# cost part# cost
123 456 789
456 789
789
123 789

I can get excel to look at column 1 (or 3 or 5) and tell me each unique part
number.

Column 1
123
456
789

What I want is for it to look across column 1, 3, and 5 and give me a single
list of all unique part numbers instead of a list for each of the columns.
In other words, I want the list to be like the above instead of...

Column1 Column 2 Column 3
123 456 789
456 789
789

The end result will be, once the list is created, to have excel count how
many times total each part number appears in the three columns (I can do that
part). If this question makes no sense, that is probably the reason I can't
do what I want.

 

[JBeaucaire]

This macro will do it. It has two SET lines, I activated the one tha
lets you select a range first. You can deactivate that line and activat
the line above which will cause the macro to ask for the search range
too

============
Sub ListUniqueValues(

'lists the unique values found in a user-defined range into
'user-defined columnar rang

Dim SearchRng As Rang
Dim ResultRng As Rang
Dim Cel As Rang
Dim iRow As Lon

'Set SearchRng = Application.InputBox("Select search range",
' "Find Unique Values", Type:=8

Set SearchRng = Selectio
D
Set ResultRng = Application.InputBox("Select results columna
range",
"Write Unique Values", Type:=8
Loop Until ResultRng.Columns.Count =

iRow =
For Each Cel In SearchRn
If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) =
The
'This value doesn't already exis
iRow = iRow +
If iRow > ResultRng.Rows.Count The
MsgBox "Not enough rows in result range to write all uniqu
values",
vbwarning, "Run terminated
Exit Su
Els
ResultRng(iRow).Value = Cel.Valu
End I
End I
Next Ce

'sort result rang
ResultRng.Sort ResultRn

End Su
===========

Is this something you can work with

 

[Rob Drummond, Jr]

Once I got this macro edited (copy and paste put a few <enter> and odd things
that excel didn't like) so it would run, I got "Unable to get CountIf proerty
of the worksheet function class" error on the line...

If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then

any guess what I did wrong? I wish I was better with macros. My work with
Access has helped but I am still very weak when it comes to macros.

 

[JBeaucaire]

Once I got this macro edited (copy and paste put a few <enter> and od
thing
that excel didn't like) so it would run, I got "Unable to get CountI
proert
of the worksheet function class" error on the line..

If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 The

any guess what I did wrong? I wish I was better with macros. My wor
wit
Access has helped but I am still very weak when it comes to macros

No, the macro works as designed, so no "editing" should've bee
required

Here's a sample of both versions for you to look at, maybe straigh
cut-n-paste

+-------------------------------------------------------------------
|Filename: UniqueValues.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=62
+-------------------------------------------------------------------

 

[Rob Drummond, Jr]

When I tried the copy and paste from your original post, it had several lines
in red and when I ran the macro, each of those lines caused an error. I will
try it again and see if I can get it to work. Am I correct in thinking I
should be copying from "Sub" to "Sub End"?

 

[JBeaucaire]

What site are you reading this through? Perhaps you are having troubl
seeing the sample workbook I posted so you could just cut-n-paste th
formula out of the book. The link is below

If the internet wraps line oddly, I would just merge a red line wit
the line following and try Compiling again, the lines should make sense
Read this thread on TheCodeCage.com if that makes it easier

http://tinyurl.com/9aubq

 

[Rdrummond]

Ok, now that Ihave registered and can actually see the sampl
spreadsheet, the macro does what I need. I have saved a copy of it so
can use it for the spreadhseet I am creating/modifying. Thank you s
much for the help. I know I must have frustrated the dickens out o
you. I guess it is about time for me to take the Access level 2 clas
so I can get some training on macros

 

[Rob Drummond, Jr]

One last thing (I promise), it seems my result range has to equal the total
number of cells in the inquiry. For example, if each column has 20 cells and
there are three columns, I need a results range of 60 cells, even though only
7 unique values are found/counted. Am I doing something wrong or is this
just an inherent requirement of the macro?