I've searched far and wide for a solution to my problem... over several long weeks now. I've come up a partially working solutions, which I'll include at the bottom for those who might know how to modify/extend them to resolve the problem.
Here's what I'm trying to accomplish (the following descriptions are in reference to this screen capture http://imgur.com/oTkbjrw):
1) I am looking to match values from Column G with Column A ($G2 with $A$2:$A$10).
2) If the values match, then extract unique values (excluding blanks) from adjacent Columns B, C, and D, and return them across columns (H through M).
So, as shown in the 'desired results 1' table (G1:M10), if G2 (Vancouver) is found in A2:A10, then extract indexed values from columns B,C, and D where Vancouver is a match (i.e., Blue, ,Green,Green,Red,Yellow,Teal,Green, , , ,Pink,Pink,Pink, ,) and return only the unique values (excluding blanks) across columns H through L (blue,Green,Red,Yellow,Teal,Pink).
I've been looking for a formula solution that can be popped into H2 and then dragged horizontally to M2, to reveal the results. I'm not married to this solution, however. An alternative that I've also considered is to return the unique values in comma delimited format rather than across columns(see 'desired Results 2'). If this is easier to accomplish, I'm all for it.
Note: I will be running this formula on 10,000+ rows. A lean/efficient solution is desirable if possible.
PARTIAL SOLUTIONS I'VE PUT TOGETHER:
1) {Array formula 1}, results can be seen here:
=IFERROR(INDEX($B$2:$B$10, SMALL(IF(COUNTIF($G2,$A$2:$A$10), MATCH(ROW($B$2:$B$10), ROW($B$2:$B$10)), ""), COLUMN(A1))),"")
This formula is only able to index column B, but it successfully matches and returns values across columns. Unfortunately, it doesn't extract unique values and returns blank cells.
2) Re: Justin's comment about a likely VBA solution, figure I should pop up one of the VBA solutions I came across.
Function UNIQUE_PH(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = Lookupvalue Then
For J = 1 To i - 1
If LookupRange.Cells(J, 1) = Lookupvalue Then
If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then
GoTo Skip
End If
End If
Next J
Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
Skip:
End If
Next i
UNIQUE_PH = Left(Result, Len(Result) - 1)
End Function
'It takes 3 arguments as inputs:
'1. Lookupvalue – A string that we need to look-up in a range of cells.
'2. LookupRange – An array of cells from where we need to fetch the data
'3. ColumnNumber – It is the column number of the table/array from which matching value is to be returned (e.g. 2 for second column).
This basically does what the above formula accomplishes, with the exception that it successfully identifies unique values. It presents the results in one cell as comma delimited (similar to 'desired Results 2'). This isn't able to search through multiple columns and doesn't remove blank cells. I also tried to run it on 10,000 rows of data and it was very slow. I know very little about VBA, so I'm not sure what could be contributing to the lag.
