Hi,
I need help from some excel geniuses.
I have an excel spreadsheet which has data on it I have extracted from Doc Parser. Unfortunately I can't get it into the format I need for further processing without some manipulation of the data in excel.
It comes out as I have illustrated below. For up to several hundred rows and as you can imagine this is fairly tedious to alter manually.
19/09/2015 Caltex Dargaville, Dargaville
25/09/2015 Caltex Kerikeri, Kerikeri
51.78 Regular
08 Sep 16 Ngapuhi Service Station, Kaikohe
27.46 Regular
2/08/2015 BP 2GO TAUMARUNUI
20/08/2015 BP 2GO WAIPAPA
32.71 Unleaded
31/07/2015 Caltex Kerikeri, Kerikeri
26.49 Regular
22/08/2015 Caltex Western Hills Drive, Whang
32.37 Regular
26/08/2015 Ngapuhi Service Station, Kaikohe
24.73 Regular
Firstly I need to delete every row that does not have an amount of Unleaded / Regular below it. So in the example above I will delete the following rows.
19/09/2015 Caltex Dargaville, Dargaville
2/08/2015 BP 2GO TAUMARUNUI
Which leaves me with:
25/09/2015 Caltex Kerikeri, Kerikeri
51.78 Regular
08 Sep 16 Ngapuhi Service Station, Kaikohe
27.46 Regular
20/08/2015 BP 2GO WAIPAPA
32.71 Unleaded
31/07/2015 Caltex Kerikeri, Kerikeri
26.49 Regular
22/08/2015 Caltex Western Hills Drive, Whang
32.37 Regular
26/08/2015 Ngapuhi Service Station, Kaikohe
24.73 Regular
From here I need to extract every second line and add it to a new column in the row above. And then split the cell I have added to the row above (containing the amount and the type of petrol.
The finished product should look like this.
25/09/2015 | Caltex Kerikeri, Kerikeri | 51.78 | Regular
08 Sep 16 | Ngapuhi Service Station, Kaikohe | 27.46 | Regular
20/08/2015 | BP 2GO WAIPAPA | 32.71 | Unleaded
31/07/2015 | Caltex Kerikeri, Kerikeri | 26.49 | Regular
22/08/2015 | Caltex Western Hills Drive, Whang | 32.37 | Regular
26/08/2015 | Ngapuhi Service Station, Kaikohe | 24.73 | Regular
I need to run this operation fairly regularly.
So I am hoping I can set up an excel sheet with a formula pre written and easy to run to reorganize the data in this way nice an efficiently.
Can any body help me? (Please see attached images for better idea)
Thanks
excel1.jpg
excel2.jpg
excel3.jpg
Bookmarks