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:

A1	Computer
A2	Phone
A2	Keyboard
A2	Cup
A3	Headphones
A3	Laptop

<tbody>
</tbody>

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

A1	irrelevant data
A2	irrelevant data
A3	irrelevant data

<tbody>
</tbody>

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.