Solved

Multiple Columns Combined Into One New Unique Column

Posted on 2011-03-15
6
415 Views
Last Modified: 2012-05-11
ExampleSpreadsheetForExperts.xls
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
Comment
Question by:rhat
  • 3
  • 3
6 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:zorvek (Kevin Jones)
ID: 35142784
Ignore my previous post. Not quite what you want.

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 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
ExampleSpreadsheetForExperts.xls
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rhat
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

by:rhat
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

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now