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. ;)

This macro will do it. It has two SET lines, I activated the one that
lets you select a range first. You can deactivate that line and activate
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 a
'user-defined columnar range

Dim SearchRng As Range
Dim ResultRng As Range
Dim Cel As Range
Dim iRow As Long

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

Set SearchRng = Selection
Do
Set ResultRng = Application.InputBox("Select results columnar
range", _
"Write Unique Values", Type:=8)
Loop Until ResultRng.Columns.Count = 1

iRow = 0
For Each Cel In SearchRng
If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0
Then
'This value doesn't already exist
iRow = iRow + 1
If iRow ResultRng.Rows.Count Then
MsgBox "Not enough rows in result range to write all unique
values", _
vbwarning, "Run terminated"
Exit Sub
Else
ResultRng(iRow).Value = Cel.Value
End If
End If
Next Cel

'sort result range
ResultRng.Sort ResultRng

End Sub
============

Is this something you can work with?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582

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" wrote:


This macro will do it. It has two SET lines, I activated the one that
lets you select a range first. You can deactivate that line and activate
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 a
'user-defined columnar range

Dim SearchRng As Range
Dim ResultRng As Range
Dim Cel As Range
Dim iRow As Long

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

Set SearchRng = Selection
Do
Set ResultRng = Application.InputBox("Select results columnar
range", _
"Write Unique Values", Type:=8)
Loop Until ResultRng.Columns.Count = 1

iRow = 0
For Each Cel In SearchRng
If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0
Then
'This value doesn't already exist
iRow = iRow + 1
If iRow ResultRng.Rows.Count Then
MsgBox "Not enough rows in result range to write all unique
values", _
vbwarning, "Run terminated"
Exit Sub
Else
ResultRng(iRow).Value = Cel.Value
End If
End If
Next Cel

'sort result range
ResultRng.Sort ResultRng

End Sub
============

Is this something you can work with?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582

Rob Drummond, Jr;178219 Wrote:
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.
No, the macro works as designed, so no "editing" should've been
required.

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


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

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582

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" wrote:


Rob Drummond, Jr;178219 Wrote:
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.
No, the macro works as designed, so no "editing" should've been
required.

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


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

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582

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

If the internet wraps line oddly, I would just merge a red line with
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/9aubqz

Rob Drummond, Jr;179845 Wrote:
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"?

+-------------------------------------------------------------------+
|Filename: UniqueValues.xls |
|Download:
'http://www.thecodecage.com/forumz/attachment.php?attachmentid=62|'
(http://www.thecodecage.com/forumz/at...chmentid=62%7C)
+-------------------------------------------------------------------+



--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582

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


--
Rdrummond
------------------------------------------------------------------------
Rdrummond's Profile: http://www.thecodecage.com/forumz/member.php?userid=92
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582

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?

"Rdrummond" wrote:


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


--
Rdrummond
------------------------------------------------------------------------
Rdrummond's Profile: http://www.thecodecage.com/forumz/member.php?userid=92
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582