Complex row to column help required!

[BaffledOfBristol]

My VBA/macro writing is a little rusty and I need to consolidate multiple rows into one row and then delete the old rows. My vehicle table has 1 row per bus per month. What I am trying to do is change the file so it contains only one row per bus with the monthly data in new columns.


The current columns are as follows (many rows per bus). The underlined columns are static/repeating for each monthly bus row. In bold are the columns that do change per bus per month :-


FLEET NO
REGISTRATION NO
VEHICLE TYPE
MODEL
COMPANY
ALLOCATED DEPOT
CLOSING ODOMETER
ODOMETER DAT
ODOMETER UNIT
FUEL USED IN PERIOD
MILES IN PERIOD
BSOG ELIGIBILITY



What I would like to do is have the data re-arranged as per the following columns with 1 row per bus:-


FLEET NO
REGISTRATION NO
VEHICLE TYPE
MODEL
COMPANY
ALLOCATED DEPOT
CLOSING ODOMETER1
ODOMETER DAT1
FUEL USED IN PERIOD1

MILES IN PERIOD1
CLOSING ODOMETER2
ODOMETER DAT2
FUEL USED IN PERIOD2

MILES IN PERIOD2

....
....
....
CLOSING ODOMETERn
ODOMETER DATn
FUEL USED IN PERIODn

MILES IN PERIODn

ODOMETER UNIT

BSOG ELIGIBILITY



It would be great if I could also order the repeating 'n' group of columns chronologically . Is this possible? The ODOMETER_DAT is in dd/mm/yyyy format.


The monthly data for the buses is not consistent i.e. the buses could have anywhere from one months data (i.e. 1 row) to a years worth (i.e. 12 rows). FLEETNO is the unique identifier for the bus.

Can anyone help please? Many thanks.

 

[hiker95]

BaffledOfBristol,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[BaffledOfBristol]

Hi Hiker95,

I have spent the last 2 hours trying to get one of the three options to work but without luck. I hope the text below describes the data. I am using Excel 2007.

The first table has the current data format for 1 bus with the following column order. I thought it best to confirm because they wrap below:-

FLEET NO
REGISTRATION NO
VEHICLE TYPE
MODEL
COMPANY
ALLOCATED DEPOT
CLOSING ODOMETER
ODOMETER DAT
ODOMETER UNIT
FUEL USED IN PERIOD
MILES IN PERIOD
BSOG ELIGIBILITY

FLEET NO REGISTRATION NO VEHICLE TYPE MODEL COMPANY ALLOCATED DEPOT CLOSING ODOMETER ODOMETER DAT ODOMETER UNIT FUEL USED IN PERIOD MILES IN PERIOD BSOG ELIGIBILITY
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 172989 27-Apr-13 M 2031.8 2614 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 175235 25-May-13 M 1939.5 2246 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 175703 29-Jun-13 M 2656.7 3077 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 178030 27-Jul-13 M 2008.9 2327 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 179981 24-Aug-13 M 1684.8 1951 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 179969 28-Sep-13 M 2168.22 2511 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 182546 26-Oct-13 M 2105.7 2577 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 168677 29-Dec-12 M 2581.6 3041 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 168011 26-Jan-13 M 1579.2 1860 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 170615 23-Feb-13 M 2210.7 2604 TRUE
12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 173780 30-Mar-13 M 2687 3165 TRUE



The data below is how I would like the data. I stopped the repeating columns after the first 3 months, but it should go on to 11 months.

FLEET NO

REGISTRATION NO

VEHICLE TYPE

MODEL

COMPANY

ALLOCATED DEPOT

ODOMETER UNIT

BSOG ELIGIBILITY

CLOSING ODOMETER1

ODOMETER DAT1

FUEL USED IN PERIOD1

MILES IN PERIOD1

CLOSING ODOMETER2

ODOMETER DAT2

FUEL USED IN PERIOD2

MILES IN PERIOD2

CLOSING ODOMETER3

ODOMETER DAT3

FUEL USED IN PERIOD3

MILES IN PERIOD3

12345

A123ABC

B7TL

VOLVO B7TL

BusCo

Bristol

M

TRUE

168677

29-Dec-12

2581.6

3041

168011

26-Jan-13

1579.2

1860

170615

23-Feb-13

2210.7

2604

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

I hope that describes it better? If not please let me know. Many thanks.

 

[hiker95]

BaffledOfBristol,

I would prefer not to have to split the following text into columns.

12345 A123ABC B7TL VOLVO B7TL BusCo Bristol 172989 27-Apr-13 M 2031.8 2614 TRUE


So that I can get it right the first time:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[BaffledOfBristol]

Hi Hiker95,

Please find the example file at:-

https://experten.app.box.com/files

Thanks.

 

[hiker95]

BaffledOfBristol,

The link is asking for an ID and password.

You can upload your workbook to Box Net,
sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[BaffledOfBristol]

Hi Hiker95,

Can you try it again please? I checked and unchecked the sharing settings and have successfully had two friends open and download the file now.

https://experten.box.com/s/jctqyjpam91ftv35dcfk

Thanks.

 

[hiker95]

BaffledOfBristol,

Thanks for the workbook.

You want to go from this:

Excel 2007
	D	E	F	G	H	I	J	K	L
1	MODEL	COMPANY	ALLOCATED DEPOT	CLOSING ODOMETER	ODOMETER DAT	ODOMETER UNIT	FUEL USED IN PERIOD	MILES IN PERIOD	BSOG ELIGIBILITY
2	VOLVO B7TL	BusCo	Bristol	172989	27-Apr-13	M	2031.8	2614	TRUE
3	VOLVO B7TL	BusCo	Bristol	175235	25-May-13	M	1939.5	2246	TRUE
4	VOLVO B7TL	BusCo	Bristol	175703	29-Jun-13	M	2656.7	3077	TRUE
5	VOLVO B7TL	BusCo	Bristol	178030	27-Jul-13	M	2008.9	2327	TRUE
6	VOLVO B7TL	BusCo	Bristol	179981	24-Aug-13	M	1684.8	1951	TRUE
7	VOLVO B7TL	BusCo	Bristol	179969	28-Sep-13	M	2168.22	2511	TRUE
8	VOLVO B7TL	BusCo	Bristol	182546	26-Oct-13	M	2105.7	2577	TRUE
9	VOLVO B7TL	BusCo	Bristol	168677	29-Dec-12	M	2581.6	3041	TRUE
10	VOLVO B7TL	BusCo	Bristol	168011	26-Jan-13	M	1579.2	1860	TRUE
11	VOLVO B7TL	BusCo	Bristol	170615	23-Feb-13	M	2210.7	2604	TRUE
12	VOLVO B7TL	BusCo	Bristol	173780	30-Mar-13	M	2687	3165	TRUE
13
Sheet1

To this?

Excel 2007
	A	B	C	D	E	F	G	H	I
1	MODEL	COMPANY	ALLOCATED DEPOT	ODOMETER UNIT	BSOG ELIGIBILITY	CLOSING ODOMETER1	ODOMETER DAT1	FUEL USED IN PERIOD1	MILES IN PERIOD1
2	VOLVO B7TL	BusCo	Bristol	M	TRUE	168677	29-Dec-12	2581.6	3041
3
Results

Excel 2007
	J	K	L	M	N	O	P	Q	R
1	CLOSING ODOMETER2	ODOMETER DAT2	FUEL USED IN PERIOD2	MILES IN PERIOD2	CLOSING ODOMETER3	ODOMETER DAT3	FUEL USED IN PERIOD3	MILES IN PERIOD3	NEXT
2	168011	26-Jan-13	1579.2	1860	170615	23-Feb-13	2210.7	2604
3
Results

 

[BaffledOfBristol]

Hiker95,


That is correct. There were three earlier columns that also need to be included i.e. Fleet No, Registration No and Vehicle Type as columns A-C please.


In short I need the data to be restructured to include:-


1. One row per bus
2. 8 fixed columns at the start of the row
3. A group of 4 monthly repeating columns driven by the number of rows per bus in the original data
4. Repeating columns to be ordered chronological based on ODOMETER DAT
4. If a bus does not have the max number of monthly rows then repeating columns can be blank


Many thanks

 

[BaffledOfBristol]

Hiker95,


That is correct. There were three earlier columns that also need to be included i.e. Fleet No, Registration No and Vehicle Type as columns A-C please.


In short I need the data to be restructured to include:-


1. One row per bus
2. 8 fixed columns at the start of the row
3. A group of 4 monthly repeating columns driven by the number of rows per bus in the original data
4. Repeating columns to be ordered chronological based on ODOMETER DAT
4. If a bus does not have the max number of monthly rows then repeating columns can be blank


Many thanks