VLOOKUP Unique Values

[chirayu]

Hi All,

I would like help modifying the below code to only pick unique values within a multiple value list.

I have researched already for other solutions such as IF(ISERROR(INDEX(SMALL(ROW(COUNTIF etc... however the unique list is generated by dragging the formulas in different cells rather than within the same cell itself automatically as with the below code

I have obtained the code from the VBA Ninja website

Public Function VLOOKALL(MatchWith As String, TRange As Range, returncol As Double, fakeFALSE) As String
  For i = 1 To TRange.Rows.Count
  If TRange(i, 1) = MatchWith Then
  If VLOOKALL <> "" Then
  VLOOKALL = VLOOKALL & ","
  End If
  VLOOKALL = VLOOKALL & TRange(i, returncol)
  End If
  Next i
End Function

 

[Hui]

If you simply want a list of the unique values in a range
Assuming the range is A2:A100

I'd suggest

Public Sub List_Unique_Values()

ActiveSheet.Range("A2:A65536").AdvancedFilter _
  Action:=xlFilterCopy, _
  CopyToRange:=ActiveSheet.Range("B2"), _
  Unique:=True

End Sub

 

[NARAYANK991]

Hi ,

I am not sure I have understood you ; the code you have posted has nothing to do with the requirement that you have explained. Anyway see if this is what you were looking for :

Public Function CONCATENATEUNIQUEITEMS(TRange As Range, Optional Separator As String = ",") As String
      For i = 1 To TRange.Rows.Count
          CurrItem = TRange(i)
          If CONCATENATEUNIQUEITEMS = vbNullString Then
              CONCATENATEUNIQUEITEMS = CurrItem
          Else
              If InStr(1, CONCATENATEUNIQUEITEMS, CurrItem) = 0 Then
                CONCATENATEUNIQUEITEMS = CONCATENATEUNIQUEITEMS & Separator & CurrItem
              End If
          End If
      Next i
End Function

Narayan

 

[chirayu]

Thanks @Hui @NARAYANK991 - Basically I needed a VLOOKUP that can return multiple unique values. I found a solution by using an IF formula in an adjacent column in the dump sheet to only show the unique values in the sorted data and then using VLOOKALL function

 

[Johnnaz]

HUI, ref your reply: 7994. I need to have it look on sheet A and output to sheet J. I attempted to modify the code and failed. My input list has blanks and I am using version 2003. Am I out of luck? Thanks.

 

[Hui]

Can you please post a sample file with a sample of data and what you expect as the solution

 

[Johnnaz]

This is an extract of the data in actual size that I use to mange my CEF portfolio. The Equity sheet is populated by an extract from Morningstar. The sector is ticker specific and rarely changes, but it does change. There are several of these in my sheet (Group, Style, etc.), the Sector has the least data. I read all the forum responses and links and still could not grasp the answer. I am running Vista premium w/sp2 and Excel 2003. I saw VBA solutions but was hoping to avoid. Thanking you in advance, johnnaz.

 

[Johnnaz]

HUI, I forgot to show this formula. It worked like I wanted except it always is short the last value.

INDEX(EquityGroup, MATCH(0, IF(MAX(NOT(COUNTIF(J$5:J5, EquityGroup))*(COUNTIF(EquityGroup, ">"&EquityGroup)+1))=(COUNTIF(EquityGroup, ">"&EquityGroup)+1), 0, 1), 0))

 

[Johnnaz]

HUI, I solved it using normal excel advanced filter and sort. Thanks for your time.