Multiple value return

[jason.stribling]

Seen a few threads that are similar but not quite as...well judge for
yourself.

I need to return (I assume by vlookup/index match) a number of values
from a data table. They need to return as a 'string' ie all available
answers, within a single cell.

Want to return in col b! DATA TABLE
ID CLASS DATA ID DESC CLASS
123 x, y, z (vlookup?) 123 s x
124 x, y 123 s y
125 x, y 123 s z
126 z 124 d x
124 d y
125 f x
125 f y
126 g z

The question, like the why would anyone want to do this??!!! is a long
story. But thanks in advance

J

 

[Dave Peterson]

How about a UserDefined Function?

Option Explicit
Function mvlookup2(lookupValue, tableArray As Range, colIndexNum As Long, _
Optional NotUsed As Variant) As Variant

Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim myStr As String
Dim initTableCols As Long
Dim i As Long

Set initTable = Nothing
On Error Resume Next
Set initTable = Intersect(tableArray, _
tableArray.Parent.UsedRange.EntireRow)
On Error GoTo 0

If initTable Is Nothing Then
mvlookup2 = CVErr(xlErrRef)
Exit Function
End If

initTableCols = initTable.Columns.Count

i = 0
Do
myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)

If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0) _
.Resize(initTable.Rows.Count - myRowMatch, _
initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop

If i = 0 Then
mvlookup2 = CVErr(xlErrNA)
Exit Function
End If

myStr = ""
For i = LBound(myRes) To UBound(myRes)
myStr = myStr & ", " & myRes(i)
Next i

mvlookup2 = Mid(myStr, 3)

End Function

It uses the almost the same syntax as the =vlookup() function. But it always
uses "false" as the 4th argument--no matter what you type.

Select a range (single column/single row) with enough cells to fill in your data
(any cells not used will appear empty).

Then type in your formula:

=mvlookup2(a1,sheet2!$a$1:$c$999,3,false)
(mvlookup2 = multiple Vlookup)
(2 because this one is different from my original. You can change it (all
spots) if you want to.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side. Paste the code
there.

Then close the VBE and back to excel to test it out.

 

[Jay]

Thanks for reply D, getting a #name? error despite matching cell ref
for cell ref/code for code. Probably something simple but will have to
understand further in order to apply to 'real' project (bigger
spreadsheet/more data).

Many thanks so far

 

[Dave Peterson]

Make sure you put the code in a general module.

Excel isn't finding the function.

 

[Jay]

Thanks D...unfortunately the good old cell #ref! error popped up now.
Security setting was the issue above.
Thanks for the tuition...I'll get there in the end

 

[RagDyeR]

Have you thought about using the "brute force" joining of multiple
functions within the cell?

=VLOOKUP(G1,$A$1:$E$25,2,0)&","&VLOOKUP(G1,$A$1:$E$25,3,0)&","&VLOOKUP(G1,$A
$1:$E$25,4,0)&","&VLOOKUP(G1,$A$1:$E$25,5,0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks D...unfortunately the good old cell #ref! error popped up now.
Security setting was the issue above.
Thanks for the tuition...I'll get there in the end

 

[JulieD]

hi Jason

i found the example you posted a bit confusing ... maybe due to this medium
.... however, if you have
........A...............B
1....ABC.........123
2....DEF..........123
3....ABC..........456
4.....ABC.........789

and you want all the values associated with ABC in one cell (e.g. 123, 456,
789) then AFAIK you'll need to use code to achieve this e.g.
Sub concatvals()
Dim strvalue As String
Dim strsearch As String
strsearch = InputBox("What code do you want to search for?")
For Each c In Range("A1:A100")
If c.Value = strsearch Then
If Len(strvalue) < 1 Then
strvalue = c.Offset(0, 1).Value
Else
strvalue = strvalue & ", " & c.Offset(0, 1).Value
End If
End If
Next
Range("D1").Value = strvalue
End Sub


Cheers
JulieD

 

[JulieD]

oops forgot to say if you need help implementing the code please post back

 

[Jay]

Thanks JulieD, just playing with it to see if I can get it to run
'auto' down the whole sheet of data. Works spot on though, and just the
direction I needed. the problem was the duplicate 'primary' had
different values in the second column (but the same column!) which is
some solutions didn't work. Thanks guys.