Lookup Mulitple Values, Return Results in One Cell

[Jim]

Hello ~

I'm trying to create spreadsheet that looks up the criteria in column A and
return the results (and there could be multiple results) from column B and
put it all into one cell.

I found this on the Microsoft site:
http://office.microsoft.com/en-us/excel/HA012260381033.aspx and, although
it's sort of what I'm looking for, I'm not sure how or if I can alter it to
get the results I want.

I've attached an example of what the data would look like and what the
(hopeful) results would be.

ANY help would be much appreciated!

Thank you!

Jim

Example:

Our Store Their Store
16 1
14 2
14 4
3 5
17 6
14 343
15 10
17 12
16 37
3 536
etc....

I would like the result to look like this:

Our Store Being Sent From Stores:
3 5, 536
14 2, 4, 343
16 1, 37
etc...

 

[T. Valko]

If you are able to use an add-in...

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternative download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

Then you can use an array formula** like this:

=SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$11=E2,B$2:B$11,"")&" "))," ",",")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Where:

A2:A11 = your store
B2:B11 = their store

E2 = your store number 3

Note however, this formula is limited to a return of no more than 255 total
characters including the comma delimiters.

 

[Jim]

Biff ~

Thank you for that. Unfortunately I'm on a company PC and don't have admin
rights to install ANYTHING. Dang. I will, however, download it at home and
see how it works!

Jim

 

[T. Valko]

You're only other option is to get someone to write you a UDF (user defined
function). If you want to check that out I'd suggest posting this in the
programming forum.

 

[Don Guillett]

Could be cleaned up a bit more but will suffice. Assumes data in col A & B
and col J available

Sub PutInOneCell()
Application.ScreenUpdating = False
'make unique list in col I
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:a" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
'Sort col J
slr = Cells(Rows.Count, "j").End(xlUp).Row
Range("J2:J" & slr).Sort Key1:=Range("J1"), Order1:=xlAscending, _
Orientation:=xlTopToBottom
'Get data from col B if match col J in col A
mc = "j"
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
ms = Cells(i, mc) & " "
Set mv = Cells(i, mc)
For j = 2 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(j, "a") = mv Then
ms = ms & "," & Cells(j, "b")
End If
Next j
'Put in one cell in col J
Cells(i, mc) = ms
Next i
Application.ScreenUpdating = True

End Sub

 

[Harlan Grove]

You're . . .
Your

. . . only other option is to get someone to write you a UDF . . .

Been addressed before in this newsgroup. Google Groups search may be
even more useful, and it's certain to produce quicker results. For
example, the mcat udf in

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/94456a9e326b19a6

which could be used with the OP's data in array formulas like

=SUBSTITUTE(TRIM(mcat(" "&IF($A$2:$A$11=D2,$B$2:$B$11,"")))," ",", ")

where the source data is in A1:B11 and D2 contains the first 'Our
Store' entry.

One HUGE advantage of udfs over Longre's MOREFUNC (or any other XLL
add-in) is that MOREFUNC's MCONCAT can return strings only up to 255
characters in length. UDFs can return strings up to 32767 chars,
though Excel can't display that many chars.

 

[Jim]

Thanks, all. I will try these. I appreciate your help!