Converting data from columnar to row format

[neutie88]

I have several dozen files that are all formatted the same way-Three columns, with an ID in the first row, a field name in the second, and a value in the third (most files contain about 5000 rows of data and about 50 field names):

ID FIELD VALUE
234 Year 2004
234 Season Spring
234 Name WSDE4
345 Year 2003
345 Season Spring
345 Name WSDE3

I want to create a row (record) based spreadsheet, with the Field names across the top and all of the data for a single ID in one row:

ID Year Season Name
234 2004 Spring WSDE4
345 2003 Spring WSDE3

Pivot Tables work great for organizing the rows and columns, but the text-based data does not come through. I tried most formulas, and combinations of formulas, but i can't find the right combo to get these files re-formatted without a lot of copying and pasting. I am hoping that someone can provide the magic bullet that will let me write a macro to format these sheets programatically versus manually.

[Bryan Hessey]

I have several dozen files that are all formatted the same way-Three columns, with an ID in the first row, a field name in the second, and a value in the third (most files contain about 5000 rows of data and about 50 field names):

ID FIELD VALUE
234 Year 2004
234 Season Spring
234 Name WSDE4
345 Year 2003
345 Season Spring
345 Name WSDE3

I want to create a row (record) based spreadsheet, with the Field names across the top and all of the data for a single ID in one row:

ID Year Season Name
234 2004 Spring WSDE4
345 2003 Spring WSDE3

Pivot Tables work great for organizing the rows and columns, but the text-based data does not come through. I tried most formulas, and combinations of formulas, but i can't find the right combo to get these files re-formatted without a lot of copying and pasting. I am hoping that someone can provide the magic bullet that will let me write a macro to format these sheets programatically versus manually.

Hi,

are the files all in the same directory, and if other files are in the directory how are the files distinguished?

Are there Field Names other than what you have shown,
do you want the files merged into one file? -
will there be equal ID numbers in more than 1 file?

Any other points of interest?
---

[steven1001]

This is a horrible kludge and you would not want to do it every day .... but if you dont get an easier suggestion at least it works.

Append your files in any way you choose (or you could test the following with one file first).

Concatenate the "Field" and "Value" columns to get values like "Year2004" and "SeasonSpring". Then >copy>paste special>values
You should now have Column A=ID and Column B = everything else.
In column B, use >edit>replace to replace
"Year" with "Year,"
"Spring" with "Spring,,"
"Name" with "Name,,,"

On column B, use "text to columns" with "," as separator to break column B into B,C,D and E.
This gives:
A=ID,
B= a now redundant label
C=all the Year values,
D=all the Season values,
E=all the Name values

Delete cells D2, E2 and E3 and all the rows with data will then line up as long as every record set has three rows.

Sort, then delete all the rows with IDs and blank data.

As I said, a horrible kludge :-)

[neutie88]

Hi,

are the files all in the same directory, and if other files are in the directory how are the files distinguished?

Are there Field Names other than what you have shown,
do you want the files merged into one file? -
will there be equal ID numbers in more than 1 file?

Any other points of interest?
---

All of the files are in the same directory. They all have unique names that identify the year and season they were produced, and they project they were for.

There are about 50 field names, and they are identical across all the files. So a single ID has 50 fields of data associated with it.

I have a utility that will merge the files together if I decide to approach it that way.

The ID numbers do repeat across files, but there are 3 other fields (Year, Season, Form) that make that ID record unique across ALL files.

I may end up trying the kludge suggestion that was made... I am running out of time on this one (by March 30th).

Thank you!
Michelle

[Bryan Hessey]

All of the files are in the same directory. They all have unique names that identify the year and season they were produced, and they project they were for.

There are about 50 field names, and they are identical across all the files. So a single ID has 50 fields of data associated with it.

I have a utility that will merge the files together if I decide to approach it that way.

The ID numbers do repeat across files, but there are 3 other fields (Year, Season, Form) that make that ID record unique across ALL files.

I may end up trying the kludge suggestion that was made... I am running out of time on this one (by March 30th).

Thank you!
Michelle

Hi,

try the attached against your data.

Tools, Macro and run macros ListFiles and then GatherLines

In Gather Lines leave a value in A1 and B1
(it's a kludge)

If this file is in the same folder as your data then remove it from the list before running Gather.

Let me know how you go
---

[neutie88]

I gave it a shot, but it did not work. It errored out (error 400) and I could not debug it to find the issue. I have been trying the kludge-y suggestion that Steven1001 gave. It works OK, but very time intensive.

Michelle

[Bryan Hessey]

I gave it a shot, but it did not work. It errored out (error 400) and I could not debug it to find the issue. I have been trying the kludge-y suggestion that Steven1001 gave. It works OK, but very time intensive.

Michelle

It does work.

what is the foldername that your files are in?

Which folder did you save this file into?

Did you produce a list of files when you ran the List macro?
or did you get a ERROR 400 because you had no files in your list to run the Gather macro on??

---