Hi,

Here's a new one... A database query produced a report in the form of
name-value pairs, like this:

PLANT_LOCATION, VARIABLE, VALUE
New York, NumEmployees, 2454
New York, PlantSize, 125000
New York, PlantAge, 27
Toronto, NumEmployees, 2733
Toronto, PlantSize, 94000
Toronto, PlantAge, 15
....

Note that the same set of name-value pairs always appear in the same
order for each plant.

To work with these data, I need to put them in a more typical Excel
flat-file format with variables in columns and records in rows, e.g.:

PLANT_LOCATION, NumEmployees, PlantSize, Age
New York, 2454, 125000, 27
Toronto, 2733, 94000, 15
....

How do I do it? Any ideas? Please? I'm using Excel 2003.

Thanks to anyone who can help!

....Jay

Since it's all numeric data to summarize (and each field is a separate column,
right???)

It looks like a nice fit for Data|Pivottable.

Add headers to row 1 (if you don't have them)
select the range A1:Cxxx
Data|pivottable
Follow the wizard until you get to a dialog with a Layout button on it.
Click that layout button
drag the header for the plant_location to the Row field
drag the header for the Variable column to the column field
drag the header for the value column to the data field

And finish up the wizard.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


=======
Then it looks like you could save that worksheet as a CSV file.

Jay Weiss wrote:

Hi,

Here's a new one... A database query produced a report in the form of
name-value pairs, like this:

PLANT_LOCATION, VARIABLE, VALUE
New York, NumEmployees, 2454
New York, PlantSize, 125000
New York, PlantAge, 27
Toronto, NumEmployees, 2733
Toronto, PlantSize, 94000
Toronto, PlantAge, 15
...

Note that the same set of name-value pairs always appear in the same
order for each plant.

To work with these data, I need to put them in a more typical Excel
flat-file format with variables in columns and records in rows, e.g.:

PLANT_LOCATION, NumEmployees, PlantSize, Age
New York, 2454, 125000, 27
Toronto, 2733, 94000, 15
...

How do I do it? Any ideas? Please? I'm using Excel 2003.

Thanks to anyone who can help!

...Jay

--

Dave Peterson

Hi Jay

Work on a copy of your Data - just in case!!!

Create your set of headings in Row 1 as you wish.
In cell D2 enter
=IF(A2=A4,C3,"")
In cell E2 enter
=IF(A2=A4,C4,"")
Copy D2:E2 down column D as far as your data extends.
Mark your Headers, then DataAutofilterUse dropdown on Column DSelect
Blanks
Delete visible rows

--
Regards

Roger Govier


"Jay Weiss" wrote in message
oups.com...
Hi,

Here's a new one... A database query produced a report in the form of
name-value pairs, like this:

PLANT_LOCATION, VARIABLE, VALUE
New York, NumEmployees, 2454
New York, PlantSize, 125000
New York, PlantAge, 27
Toronto, NumEmployees, 2733
Toronto, PlantSize, 94000
Toronto, PlantAge, 15
...

Note that the same set of name-value pairs always appear in the same
order for each plant.

To work with these data, I need to put them in a more typical Excel
flat-file format with variables in columns and records in rows, e.g.:

PLANT_LOCATION, NumEmployees, PlantSize, Age
New York, 2454, 125000, 27
Toronto, 2733, 94000, 15
...

How do I do it? Any ideas? Please? I'm using Excel 2003.

Thanks to anyone who can help!

...Jay

Hi Jay

Apologies, I missed out a step.
After creating the formulae, mark columns D and E, Copy then Paste
SpecialValues to fix the data.
Then do you Autofilter and delete.

You can also delete Column B

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Jay

Work on a copy of your Data - just in case!!!

Create your set of headings in Row 1 as you wish.
In cell D2 enter
=IF(A2=A4,C3,"")
In cell E2 enter
=IF(A2=A4,C4,"")
Copy D2:E2 down column D as far as your data extends.
Mark your Headers, then DataAutofilterUse dropdown on Column
DSelect Blanks
Delete visible rows

--
Regards

Roger Govier


"Jay Weiss" wrote in message
oups.com...
Hi,

Here's a new one... A database query produced a report in the form
of
name-value pairs, like this:

PLANT_LOCATION, VARIABLE, VALUE
New York, NumEmployees, 2454
New York, PlantSize, 125000
New York, PlantAge, 27
Toronto, NumEmployees, 2733
Toronto, PlantSize, 94000
Toronto, PlantAge, 15
...

Note that the same set of name-value pairs always appear in the same
order for each plant.

To work with these data, I need to put them in a more typical Excel
flat-file format with variables in columns and records in rows, e.g.:

PLANT_LOCATION, NumEmployees, PlantSize, Age
New York, 2454, 125000, 27
Toronto, 2733, 94000, 15
...

How do I do it? Any ideas? Please? I'm using Excel 2003.

Thanks to anyone who can help!

...Jay

Dave and Roger,

Your suggestions were extremely helpful and they resolved my problem
perfectly. Thanks so much for your quick replies!

Best wishes...

....Jay