|
Lookup Sizes and Concatenate
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 |
Lookup Sizes and Concatenate
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 Mr Jelly wrote: 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 |
Lookup Sizes and Concatenate
Hi,
Download and install the following add-in (http://download.cnet.com/Morefunc/30...-10423159.html) and then use the mconcat function. Suppose the data is in range B8:E26. In B29:D29, enter 3, Shirt and In Stock. In E29, enter the following array formula =TRIM(MCONCAT(IF((B9:B26=B29)*(C9:C26=C29)*(D9:D26 =D29)=0,"",E9:E26)," ")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mr Jelly" wrote in message ... 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 |
Lookup Sizes and Concatenate
Input as detailed below and formula is returning #VALUE!
Have double checked i have put in the right cells and the right values.. "Ashish Mathur" wrote: Hi, Download and install the following add-in (http://download.cnet.com/Morefunc/30...-10423159.html) and then use the mconcat function. Suppose the data is in range B8:E26. In B29:D29, enter 3, Shirt and In Stock. In E29, enter the following array formula =TRIM(MCONCAT(IF((B9:B26=B29)*(C9:C26=C29)*(D9:D26 =D29)=0,"",E9:E26)," ")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mr Jelly" wrote in message ... 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 |
Lookup Sizes and Concatenate
Works ok thanks. forgot to shift ctrl and enter...... many thanks for help "Mr Jelly" wrote: Input as detailed below and formula is returning #VALUE! Have double checked i have put in the right cells and the right values.. "Ashish Mathur" wrote: Hi, Download and install the following add-in (http://download.cnet.com/Morefunc/30...-10423159.html) and then use the mconcat function. Suppose the data is in range B8:E26. In B29:D29, enter 3, Shirt and In Stock. In E29, enter the following array formula =TRIM(MCONCAT(IF((B9:B26=B29)*(C9:C26=C29)*(D9:D26 =D29)=0,"",E9:E26)," ")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mr Jelly" wrote in message ... 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 |
Lookup Sizes and Concatenate
you are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mr Jelly" wrote in message ... Works ok thanks. forgot to shift ctrl and enter...... many thanks for help "Mr Jelly" wrote: Input as detailed below and formula is returning #VALUE! Have double checked i have put in the right cells and the right values.. "Ashish Mathur" wrote: Hi, Download and install the following add-in (http://download.cnet.com/Morefunc/30...-10423159.html) and then use the mconcat function. Suppose the data is in range B8:E26. In B29:D29, enter 3, Shirt and In Stock. In E29, enter the following array formula =TRIM(MCONCAT(IF((B9:B26=B29)*(C9:C26=C29)*(D9:D26 =D29)=0,"",E9:E26)," ")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mr Jelly" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com