Help with Pivot Table, please

[drpudding]

I'm new to Pivot tables and need them to rearrange some data.

My data looks like this:

OrderID ------- RegistrationType ----- OptionName -- OptionValue
1186721308 -- Football Registration -- Name --------- Benny
1186721308 -- Football Registration -- Gender ------- male
1186721308 -- Football Registration -- Grade -------- 10th
1186721308 -- Football Registration -- Address ------ 1234 Main St.
1186721310 -- Baseball Registration -- Name -------- Lou
1186721310 -- Baseball Registration -- Gender ------- male
1186721310 -- Baseball Registration -- Grade -------- 5th
1186721310 -- Baseball Registration -- Address ------ 5678 Doe St.

what I need is this pivot, where the Option name is converted to a column, with the corresponding value as a row:

OrderID ------ RegistrionType ------- Name -- Gender -- Grade -- Address
1186721308 -- Football Registration - Benny - male ---- 10th --- 1234 Main St.
1186721310 -- Baseball Registration - Lou --- male ---- 5th ---- 5678 Doe St.

The value of the data is text, not numbers, so I get this (counts):

OrderID ------ RegistrionType --------------- Name -- Gender -- Grade -- Address
1186721308 -- Football Registration ---------- 1 --------- 1 ---------1 -------- 1
1186721310 -- Baseball Registration ---------- 1 --------- 1 ---------1 -------- 1

Can a pivot tale be used for the purpose I need? or is there another approach?

One other question i have is this. Once I've set up a base pivot table, can I automatically update the table with new data from another excel spreadsheet , or do i need to recreate the pivot table manually each time?

Thanks!

[rylo]

Hi

Don't believe you can use a Pivot Table to do this, but you can do it with formulas.

If your data as shown is in the range A1:D9 then

F1:K1 - OrderID;RegistrationType;Name;Gender;Grade;Address
F2: =OFFSET($A$1,(ROW()-2)*4+1,0)
G2: =OFFSET($B$1,(ROW()-2)*4+1,0)
H2: =OFFSET($D$1,(ROW()-2)*4+1,0)
I2: =OFFSET($D$1,(ROW()-2)*4+2,0)
J2: =OFFSET($D$1,(ROW()-2)*4+3,0)
K2: =OFFSET($D$1,(ROW()-2)*4+4,0)

Copy these down as required to convert your data.

HTH

rylo

[drpudding]

Thanks for the reply. I'll give this a go.

[lalbatros]

The data field in an excel pivot table is always a numerical value obtained by a statistical operation (min, max, average, ...).
In Ms-Access the pivot tables (called crosstab) can do what you asked.
Maybe you should use access, maybe your application is more like a database application.

Further, note that it is easy to write a VBA function that will perform what you asked.
I recommend you this method for clarity. The use of excel function becomes easily unreadable and is definitively not maintainable.
I have not written it yet, but in the past I programmed the reverse operation: uncrosstab. Usuallu people like to enter data in a crosstab layout, but using the data is easier in a table layout like the one you used to enter your data.