Hi all,
I have 2 worksheets containing data that I need to combine, by creating rows
WS1 has product data in the range of:
Product_ID Name price Options_Ref
1 Product 1 10.00 18
2 Product 2 15.00 25
3 Product 3 5.00 18
4 Product 4 20.00 7

WS2 has the option list data such as:
Options_Ref Option_Description
7 Red
7 Blue
18 12"
18 14"
18 16"
25 White
25 Black
25 Grey
25 Cream
25 Red

What I need is to create a new worksheet that will create rows for each
option, by looking up the data. Some products use the same options so would
need to lookup,so for example:
Product_ID Name price Options_Ref Option_Description
1 Product 1 10.00 18 12"
1 Product 1 10.00 18 14"
1 Product 1 10.00 18 16"
2 Product 2 15.00 25 White
2 Product 2 15.00 25 Black
2 Product 2 15.00 25 Grey
2 Product 2 15.00 25 Cream
2 Product 2 15.00 25 Red
3 Product 3 5.00 18 12"
3 Product 3 5.00 18 14"
3 Product 3 5.00 18 16"


I hope I have explained my needs OK, hope somebody can help me

This procedure repeats a row a given, variable amount of times,
using reverse Pivot Table.
Assume your data and headers are arranged like this, starting at A1

Prod_ID Prod_NM Price Opt_Ref Prod_ID2
1 Prod1 10 18 1 0 0 12" 14" 16" 0 0 0 0 0
2 Prod2 15 25 2 0 0 0 0 0 Wt Blk Gry Crm Red
3 Prod3 5 18 3 0 0 12" 14" 16" 0 0 0 0 0
4 Prod4 20 7 4 Rd Bu 0 0 0 0 0 0 0 0

Opt_ref2 Opt_des2
7 Rd
7 Bu
18 12"
18 14"
18 16"
25 Wt
25 Blk
25 Gry
25 Crm
25 Red

Notice that column E is a repeat of A.
In F2:O2 enter this array formula:
=TRANSPOSE(IF(Opt_ref2=D2,Opt_des2,0))
and fill down.
Use reverse Pivot Table on E1:O5. See:
http://j-walk.com/ss/excel/usertips/tip068.htm
In the resulting Row/Column/Value table, insert 2 more blank Columns
and delete zeros with
Edit > Go To > Special > Constants > Numbers
(or Logicals if ,0 is omitted in the above formula)
Delete > Shift up > Entire Row
Fill the first row of the 3 blank columns with this array formula
with this format:
=VLOOKUP(......,A25,{2,3,4})
and fill down.

Many thanks for your reply Herbert.
I have been trying your suggestion & have been unable to get it working.
I am having trouble with the IF statement in the TRANSPOSE formula.
In your reply you have referred to Opt_ref2 & Opt_des2, I know what these
are referring to but in excel do I need to declare the cell range inplace of
these ie A1:A7 etc or are they variable that the data range needs declaring
in?

Many thanks
Richard

"Herbert Seidenberg" wrote:

> This procedure repeats a row a given, variable amount of times,
> using reverse Pivot Table.
> Assume your data and headers are arranged like this, starting at A1
>
> Prod_ID Prod_NM Price Opt_Ref Prod_ID2
> 1 Prod1 10 18 1 0 0 12" 14" 16" 0 0 0 0 0
> 2 Prod2 15 25 2 0 0 0 0 0 Wt Blk Gry Crm Red
> 3 Prod3 5 18 3 0 0 12" 14" 16" 0 0 0 0 0
> 4 Prod4 20 7 4 Rd Bu 0 0 0 0 0 0 0 0
>
> Opt_ref2 Opt_des2
> 7 Rd
> 7 Bu
> 18 12"
> 18 14"
> 18 16"
> 25 Wt
> 25 Blk
> 25 Gry
> 25 Crm
> 25 Red
>
> Notice that column E is a repeat of A.
> In F2:O2 enter this array formula:
> =TRANSPOSE(IF(Opt_ref2=D2,Opt_des2,0))
> and fill down.
> Use reverse Pivot Table on E1:O5. See:
> http://j-walk.com/ss/excel/usertips/tip068.htm
> In the resulting Row/Column/Value table, insert 2 more blank Columns
> and delete zeros with
> Edit > Go To > Special > Constants > Numbers
> (or Logicals if ,0 is omitted in the above formula)
> Delete > Shift up > Entire Row
> Fill the first row of the 3 blank columns with this array formula
> with this format:
> =VLOOKUP(......,A25,{2,3,4})
> and fill down.
>
>

In my example Opt_ref2 and Opt_des2 are the arbitrary names
for the ranges A7:A16 and B7:B16
So if you did not name these ranges with
Insert > Name > Create > Top Row
then you could have written the formula this way:
=TRANSPOSE(IF($A$7:$A$16=D2,$B$7:$B$16,0))
To me, the first way is easier to read, is independent of
the data location you chose and you don't have to worry
about absolute and relative references.
Select F2:O2 (10 cells, the number of cells in Opt_ref2),
type the formula and hit Ctrl+Shift+Enter instead of just Enter.
Experiment with my setup before you tackle yours.