Converting Multi-Row Multi-Field Records to Workable Columns

Nicole_Michelle01

Board Regular
Joined
Nov 9, 2005
Messages
50
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
Nov 9, 2005
Messages
50
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Nov 9, 2005
Messages
50

ADVERTISEMENT

They do follow the same pattern...

Every nth row, etc.
 

Nicole_Michelle01

Board Regular
Joined
Nov 9, 2005
Messages
50

ADVERTISEMENT

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

Board Regular
Joined
Nov 9, 2005
Messages
50
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top