Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Multiple Columns Combined Into One New Unique Column

Posted on 2011-03-15
6
Medium Priority
?
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
ExampleSpreadsheetForExperts.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

604 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