|
|
|
date: Thu, 2 Jul 2009 13:01:03 -0700,
group: microsoft.public.excel
back
Re: Lookup Mulitple Values, Return Results in One Cell
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.
--
Biff
Microsoft Excel MVP
"Jim" wrote in message
news:9D1D63F3-7065-4347-8C6A-E96FBDEF167A@microsoft.com...
> 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...
date: Thu, 2 Jul 2009 16:28:43 -0400
author: T. Valko
Re: Lookup Mulitple Values, Return Results in One Cell
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" wrote:
> 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.
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Jim" wrote in message
> news:9D1D63F3-7065-4347-8C6A-E96FBDEF167A@microsoft.com...
> > 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...
>
>
>
date: Thu, 2 Jul 2009 13:37:01 -0700
author: Jim
Re: Lookup Mulitple Values, Return Results in One Cell
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.
--
Biff
Microsoft Excel MVP
"Jim" wrote in message
news:C2E854EE-846D-432F-80CD-7676B897FA96@microsoft.com...
> 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" wrote:
>
>> 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.
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Jim" wrote in message
>> news:9D1D63F3-7065-4347-8C6A-E96FBDEF167A@microsoft.com...
>> > 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...
>>
>>
>>
date: Thu, 2 Jul 2009 17:48:18 -0400
author: T. Valko
Re: Lookup Mulitple Values, Return Results in One Cell
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Jim" wrote in message
news:9D1D63F3-7065-4347-8C6A-E96FBDEF167A@microsoft.com...
> 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...
date: Thu, 2 Jul 2009 18:24:10 -0500
author: Don Guillett
Re: Lookup Mulitple Values, Return Results in One Cell
"T. Valko" wrote...
>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.
date: Thu, 2 Jul 2009 23:01:36 -0700 (PDT)
author: Harlan Grove
|
|
