Lookup Sizes and Concatenate

[Mr Jelly]

Looking for a formula to use to establish sizes in stock by just using the
item number. Would need to LOOKUP and CONCENATE to just one cell.

Sample Table Below.

ITEM NAME STOCK Size
1 Jumper In Stock. Small
1 Jumper In Stock. Medium
1 Jumper In Stock. Large
1 Jumper In Stock. X Large
1 Jumper In Stock. XX Large
2 Trousers In Stock. 32 Inch
2 Trousers In Stock. 34 Inch
2 Trousers In Stock. 36 Inch
2 Trousers In Stock. 38 Inch
2 Trousers No Stock. 40 Inch
3 Shirt No Stock. 15 Inch
3 Shirt In Stock. 15 1/2 Inch
3 Shirt In Stock. 16 Inch
3 Shirt In Stock. 16 1/2 Inch
3 Shirt No Stock. 17 Inch
4 Tie In Stock. Small
4 Tie In Stock. Medium
4 Tie In Stock. Large

Result

Item Name Sizes Available
3 Shirt 15 1/2 Inch / 16 Inch / 16 1/2 Inch

 

[Franz Verga]

It seems a good situation to use a Pivot Table...

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy

 

[Ashish Mathur]

Hi,

Download and install the following add-in
(http://download.cnet.com/Morefunc/3000-2077_4-10423159.html) and then use
the mconcat function.

Suppose the data is in range B8:E26. In B2929, enter 3, Shirt and In
Stock. In E29, enter the following array formula

=TRIM(MCONCAT(IF((B9:B26=B29)*(C9:C26=C29)*(D926=D29)=0,"",E9:E26)," "))



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mr Jelly]

Input as detailed below and formula is returning #VALUE!

Have double checked i have put in the right cells and the right values..

 

[Mr Jelly]

Works ok thanks. forgot to shift ctrl and enter......

many thanks for help

 

[Ashish Mathur]

you are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com