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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,130
Messages
6,129,062
Members
449,484
Latest member
khairianr

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
Back
Top