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
Solved

Multiple Columns Combined Into One New Unique Column

Posted on 2011-03-15
6
418 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

791 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