how to bring in all the matching rows from a different sheet?

[bigdan]

EDIT - I can't use VBA at work so I'd appreciate a non-VBA option please


I have two workbooks that I need to play with.

Both have an Opportunity ID field. I'm using the first one for most of my work, but the second one has a Products field that I need. Each Opp ID is associated with one or more Products.

How do I bring in the various Products associated with an Opp ID?


Here's the second spreadsheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]Computer[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Phone[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Keyboard[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Cup[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Headphones[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Laptop[/TD]
[/TR]
</tbody>[/TABLE]


So in the first spreadsheet, when I start with one row for A2, and I don't know how many associated rows there are in the second one, how would I approach this?

 

[Special-K99]

Need to know where the data relating to A2 is going to, what cells etc.

 

[bigdan]

This is what the first spreadsheet would have

[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]irrelevant data[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]irrelevant data[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]irrelevant data[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have to create a third spreadsheet, mainly with information copy/pasted from the first one, but then have to break it up by product, which I would extract from the second one. It could've been as easy as a vlookup if each Opp ID had a single product but unfortunately that's not the case. And we also don't know how many products are associated with each Opp ID, though I believe it's always between 1 and 6.

 

[Special-K99]

So where would Phone keyboard and cup go against A2 in your first spreadsheet, ie what are you expecting the output to look like?

 

[bigdan]

In the third spreadsheet phone and keyboard and cup would all be alongside A2. So there would have to be 3 separate rows for A2. Which is the problem, I wouldn't know how many rows to create for each Opp ID. I suppose I could create 6 rows for each Opp ID and then delete the unused ones.