repeated rows to single rows with many columns

G

geanswerman

Below is shown an extracted table that I need to rearange to show price list.

Material Description Minimum_Qty Sell_Price
10001 Screw,IF,Can,Ti,7X20 1 90
10001 Screw,IF,Can,Ti,7X20 11 83.5
10001 Screw,IF,Can,Ti,7X20 21 76.5
10002 Screw,IF,Can,Ti,7X25 1 90
10002 Screw,IF,Can,Ti,7X25 11 83.5
10002 Screw,IF,Can,Ti,7X25 21 76.5

Material Description 1 11 21
10001 Screw,IF,Can,Ti,7X20 90.00 83.50 76.50
10002 Screw,IF,Can,Ti,7X25 90.00 83.50 76.50


Is this possible? I thought I had done it before but I can not find a
reference for it.
 
L

Luke M

You'll want to setup a PivotTable (Data-Pivot) with Material & Description as
Row field, Qty as you column field and Sell_Price as your data field. This
should arrange your data the way you desire.

Note that you could then copy the data out of the PivotTable, if you wish to
make it static/save on file space.
 
M

Max

Assume source data in Sheet1, in A2:D2 down,
with the regular repeating pattern as posted

In Sheet2,
In A2: =OFFSET(Sheet1!A$2,ROWS($1:1)*3-3,)
Copy A2 to B2

In C2: =OFFSET(Sheet1!$D$2,ROWS($1:1)*3-3+COLUMNS($A:A)-1,)
Copy C2 to E2. Select A2:E2, fill down to exhaust source data

In C1: =INDEX(Sheet1!$C$2:$C$4,COLUMNS($A:A))
Copy to E1

Success ? Wave it, click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top