Solved

# Multiple Columns Combined Into One New Unique Column

Posted on 2011-03-15
Medium Priority
452 Views
Hello Experts,
File attached.
I've browsed through the existing solutions and couldn't find what I was looking for, so I thought I'd ask.
Many were close, but didn't quite get it done for me(i.e. =COUNTIF(B:B,A1)=0)
I'm trying to compile a column of unique items given the contents of the other columns in the worksheet.  Forumula or Macro doesn't matter to me which (though I suspect it'll end up being a macro).
Additionally, I'd like them to remain in the order that they're in (this is the sticky part).
For instance, Plane, Ride, Walk, Sleep are the last 4 items in the rows and would need to remain so in the new Unique Column in the same worksheet.
PrussyCow,  PrussyCow2 and PrussyCow3 are newly introduced and would need to be placed in unique order in the new column.
Items are text items (although some do have numbers in them, they aren't specific enough to sort on).
Clear as mud?  Hope you can help.
Thanks

0
Question by:rhat
• 3
• 3

LVL 81

Expert Comment

ID: 35142774
The instructions below describe how to create a condensed list of unique text and numeric values with possible interspersed empty cells.

Name the source list "List" and the first cell in that list "ListFirstCell". Name the unique list "UniqueList". The unique list should contain the same number of cells as the source list. Name the first cell in that list "UniqueListFirstCell" and place this array formula in that cell:

=IF(SUM((FREQUENCY(IF(List<>"",MATCH(List,List,0)),ROW(List)-ROW(ListFirstCell)+1)>0)*1)<=ROW()-ROW(ListFirstCell)+1,"",INDEX(List,SMALL(IF(FREQUENCY(IF(List<>"",MATCH(List,List,0)),ROW(List)-ROW(ListFirstCell)+1),ROW(List)-ROW(ListFirstCell)+1),ROW()-ROW(ListFirstCell)+1)))

To enter an array formula press CTRL+SHIFT+ENTER. Copy the formula down to the end of the list.

Note that the values included can be conditional. To use a condition such as greater than some value replace the two occurances of "List<>""" with the condition.

Kevin
0

LVL 81

Expert Comment

ID: 35142784
Ignore my previous post. Not quite what you want.

Kevin
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 35142796
Use this UDF. See the attached workbook.

The function below returns an array of unique values found in the parameter SourceValues. It is a hybrid function that operates as a UDF and as a VBA function. If called as a UDF it will return an array of unique values in the same shape (rows and columns) as the calling range. If called from VBA the result is returned as a single dimension array. The parameter SourceValues can be a range reference or an array of any size and shape.

Public Function UniqueValues( _
ByVal SourceValues As Variant _
) As Variant

' Return an array of the unique values found in the parameter SourceValues.
' This is a hybrid function that operates as a UDF and as a VBA function.
' If called as a UDF it will return an array of unique values in the same
' shape (rows and columns) as the calling range. If called from VBA the
' result is returned as a single dimension array. The parameter SourceValues
' can be a range reference or an array of any size and shape.

Dim Result As Variant
Dim Value As Variant
Dim Work As New Collection
Dim Vertical As Boolean
Dim Row As Long
Dim Column As Long
Dim UDFMode As Boolean

' Determine if called from a cell formula or VBA
If TypeName(Application.Caller) = "Range" Then UDFMode = True

' Normalize the parameter SourceValues into an array
If TypeName(SourceValues) = "Range" Then
SourceValues = SourceValues.Value
End If

' Initialize the result array if called as a UDF
If UDFMode Then
ReDim Result(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
For Row = 1 To UBound(Result, 1)
For Column = 1 To UBound(Result, 2)
Result(Row, Column) = ""
Next Column
Next Row
End If

' Build list of unique values
On Error Resume Next
For Each Value In SourceValues
If Len(Value) > 0 Then Work.Add Value, CStr(Value)
Next Value
On Error GoTo 0

' Push the unique values from the collection to the result array
If UDFMode Then
If UBound(Result, 1) >= UBound(Result, 2) Then Vertical = True
On Error Resume Next
For Row = 1 To UBound(Result, 1)
For Column = 1 To UBound(Result, 2)
If Vertical Then
Result(Row, Column) = Work((Column - 1) * UBound(Result, 1) + Row)
Else
Result(Row, Column) = Work((Row - 1) * UBound(Result, 2) + Column)
End If
Next Column
Next Row
On Error GoTo 0
Else
ReDim Result(1 To Work.Count)
For Row = 1 To Work.Count
Result(Row) = Work(Row)
Next Row
End If

' If operating as a UDF and more values were found than there was room for,
' display an indicator in the last result element
If UDFMode Then
If Work.Count > UBound(Result, 1) * UBound(Result, 2) Then
Result(UBound(Result, 1), UBound(Result, 2)) = "More..."
End If
End If

' Return the result array
UniqueValues = Result

End Function

Kevin
0

Author Comment

ID: 35149827
OK it's working, but I'm having trouble implementing it.
Since I couldn't implement it on my existing spreadsheet,
I tried it on the one you attached (which is how I know it's working), but I get a "More..." on line 50 in the Unique Items Column when replacing the example items with the items from my spreadsheet.
when trying to fix it, I get an error that you can change part of an array.
0

Author Comment

ID: 35149971
Got it.
I needed to highlight the Unique Values column (J) and modify the bounds of the array
by hitting key SHIFT + CTRL + Enter, than locked it in by hiting enter again.
Just for clarification for others reading this later I highlighted column J all the way down to like line 300 (almost 3 times the length I'd thought I'd use).
0

Author Closing Comment

ID: 35150015
The solution was complete and accurate and fast.  Implementation instructions was a little off, but I got through it
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦