Create rows from a lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 13 Nov 2006 20:57 GMT

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

Reply to this Message

Herbert Seidenberg - 15 Nov 2006 03:36 GMT

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(......,A2:D5,{2,3,4})
and fill down.

Reply to this Message

Richard - 17 Nov 2006 15:35 GMT

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

> This procedure repeats a row a given, variable amount of times,
> using reverse Pivot Table.
[quoted text clipped - 33 lines]
> =VLOOKUP(......,A2:D5,{2,3,4})
> and fill down.

Reply to this Message

Herbert Seidenberg - 18 Nov 2006 05:29 GMT

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.

Reply to this Message

Rate this thread: