Hi All,
I am currently working currently with a custom function, which works very nicely for what I've asked it to do, which is to namely show me all sets of results when an ISBN (which I use for the lookup) appears more than once in a sequence of ISBNs and Qtys, basically the two sets of data I look at are identical when it comes to the ISBN columns, I just have to look up one against another to give me the correct Qtys. So for example:
Worksheet 1 - is where I am pulling the lookup data from:
ISBN QTY
9781234567890 1
9788545161601 1
9781234567890 2
I then open a second sheet that has the same ISBNs and use the following custom function:
Function SingleCellExtract(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
SingleCellExtract = Left(Result, Len(Result) - 1)
End Function
Which would give me an output when I look up against the other data of:
WorkSheet 2 - where i'd enter the lookup formula
ISBN QTY LOOKUP COLUMN (WHERE I WOULD ENTER THE Singlecellextract Formula)
9781234567890 1 1,2
9788545161601 1 1
9781234567890 2 1,2
Which up to now has served me well. However what I'd really like to do now is progress it one stage further, where basically the formula will enter the formula in the same sequence that the lookup data is in, in the original sheet. So when I do the lookup on the second sheet it would present the data in the same sequence:
ISBN QTY
9781234567890 1
9788545161601 1
9781234567890 2
Obviously if I use the regular VLOOKUP formula I would only ever get the top result, which is not any good for my current needs with this.
Any help with this much appreciated!
Many thanks
Chriz
Bookmarks