Macro vs Formula

[JaipalS]

Here is the problem I'm having and not sure what approach to use. I'm not a frequent excel user, so bear with me if this sounds basic to a lot of you.

I have three input worksheets.
First one has two columns,

Col1 - List of things(say boxes)
Col2 - List of things in each box (comma separated)

second worksheet has again two columns
Col1 - List of boxes
Col2 - Id for each box

third worksheet has again two columns
Col1 - List of things in the boxes
Col2 - Id for each thing

I need an output sheet that can give me

Col1 - Id for each box
Col2 - Id's fr all the things in the box comma separated.

 

[Robert Mika]

Hi jaipal
Welcome to the board

Should the ID boxes match with the ID of the thing or you just need to have columns with those Id?
In thi case simple:
=Sheet2!B2

for the boxes ID would work

 

[JaipalS]

Hi Robert,

Thanks for the quick response.

I will make my problem a little more understandable.

Sheet 1 has 3500 rows and two columns.

Col1 - Designer Name (LV, DKNY)
Col2 - Sizes available (42,44,46,48) {Comma separated) - Can be more than one}

Sheet 2 could have more or less number of rows.

Clo1 - Designer Name (LV, DKNY)
Col 2 - Designer Id's (1001, 1002)

Sheet 3 again could have any number of rows

Col1 - Size (42, 44, 46, 48)
Col 2 - Size Id (2001, 2002, 2003)

I need an output where I have

Col1 - DesignerId
Col2 - Available Size id's (comma separated)

I need to import this into another system that will not accept names, but just Id's.

Also, if there is something in sheet one but cannot be found in one of the other two sheets, it can be replaced by NA in the O/P sheet so that I can manually find those ID's.

I hope this clears up. Sorry again for the weird formatting of the original question.

 

[Robert Mika]

Hi jaipal,
There is a away to get that but...
You can copy all designer ID to sheet 4 and then using lookup:
Look at Sheet2 to get their name, having their name look at their sizez available and here is a problem - you can look at Sheet 3 to get their Id but only when the sizes are unique for each size ID.
If you have more than one sizes of the same sequence(42,44,46,48) or other you will not get the correct ID.