Converting Multi-Row Multi-Field Records to Workable Columns

[Nicole_Michelle01]

Hi, I have a file with approximately 500 records with the same fields listed in rows which I need to convert to columns so that I can sort, etc. on the total list of records. There are fields which I don't need and there are multiple fields in some of the rows.

Can I post a sample of the file and an example of what I want the final output to look like?

Let me know if you can help, I couldn't find anything similar in the archives.

Thanks!

~ Nici

 

[acw]

Nici

If the data has some form of delimiter, then you can use the data, text to columns facility to convert the text from one column, to multiple columns.

HTH

Tony

 

[Nicole_Michelle01]

It's a little more complex...

It's a little more complex than that. Each record is structured as follows:

COLA COLB COLC COLD COLE COLF
ROW1 ID NAME ID
ROW2 DATE
ROW3 DATE
ROW4 FIELD1 DATA
ROW5 FIELD2 DATA
ROW6 DATE
ROW7 FIELD3 DATA
ROW8 FIELD4 DATA
ROW9 FIELD5 DATA
ROW10 FIELD6 DATA
ROW11 FIELD7 DATA FIELD8 DATA
ROW12 FIELD9 DATA FIELD10 DATA FIELD11 DATA

There are approx 500 records and I need to move each field to columns horizontally across the spreadsheet and the data underneath. To get the data in a useful format, I have to delete rows 2, 3 and 6 because those dates are not relevant, cut/paste fields 8, 10, and 11 below Row12, then select the record and do a copy/paste special/transpose. Then I delete the copy and shift the cells left so that the data begins in COLA Can you help

 

[Richard Schollar]

Nici

Do all records follow the same structure? So that every nth row in excel will be Field X of any particular record - so that individual records will occupy the same number of rows (say 10 or whatever)? Are there blank rows between records?

Richard

 

[Nicole_Michelle01]

They do follow the same pattern...

Every nth row, etc.

 

[Nicole_Michelle01]

No blank line sin between

 

[Nicole_Michelle01]

I'll also need to delete of the rows with just field names. Is there anyway I can delete every other row to remove them? I know I can simply do a sort then delete all of the rows through the first row of data.

 

[Nicole_Michelle01]

Last thing, there is a number in ROW13, COLB which served as a record counter which I also deleted. So I delete Rows 2, 3, 6 and 13.

 

[Richard Schollar]

Nici

I think it will be relatively straightforward to write a simple macro to achieve what you want, but I want to be absolutely sure I understand how your data is structured. Can you use the posting software to post a sample of the data?

http://www.puremis.net/excel/downloads.shtml

Richard

 

[Nicole_Michelle01]

Absolutely!