Moving every second row into the row above?

[nzpetrol]

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

[BadlySpelledBuoy]

Would a VBA option be suitable for you?

Could you post the sample data in it's original format in a workbook so we don't have to recreate it from your image?

BSB

[nzpetrol]

Hi, yes I want to do that but having trouble attaching a doc to my thread. I think I've done it now. Cheers.

[Glenn Kennedy]

I looked at this before your attachment was attached. Here's a formula solution. It requires a helper column (C), to identify the rows that need to be deleted.

Columns E&F contain an array formula to remove the unnecessary rows and columns and columns H,I &J contain oridinary formulae to return the desired result. How does this look?



Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[nzpetrol]

Hi Glen,

Thanks for posting that. It looks great. Bearing in mind that I'm no excel expert (I use it regularly and am functionally literate but not so great on formulas etc)

If I was starting from scratch with a new lot of data could I just copy and paste into columns 1 and 2?

Also I need to split column C (K) into 2 columns. The amount of petrol in one and the type in the other. I should end up with 4 rows in total.

Thanks mate - I really appreciate you taking the time to help me out.

[Glenn Kennedy]

I didn't spot that. Gimme 5 minutes...

[Glenn Kennedy]

All you need to do is paste in at A&B. If there are more than 500 rows, just adjust the 500s in the formulae in E&F to suit. Don't make them impossibly long, otherwise this will slow down horribly.

=IFERROR(1/(1/INDEX(A:A,SMALL(IF($C$2:$C$500<>"Remove",ROW($C$2:$C$500)),ROWS(F$2:F2)))),"")

Otherwise just copy the formulae all down as far as you need.

[BadlySpelledBuoy]

Just in case it's of any use to you, attached is a VBA solution to this.

The code (below) is very basic so it's easy(ish) to follow and amend if you're new to this.
It could probably be rewritten to be much more efficient but that would make it less easy to follow.

Paste the data into columns A & B, then hit the button.

BSB

Please Login or Register  to view this content.

[nzpetrol]

That's awesome!

Only problem is when I drag the formula down, the bits that I've dragged down stop working correctly.

Any idea why that might be?

[Glenn Kennedy]

Post your sheet, with the problems.

[nzpetrol]

See attached.

[nzpetrol]

Thanks BSB that is bloody awesome.

Much appreciated!!!!!

[Glenn Kennedy]

It's becasue soem of the dates aren't presenting as dates. Try this in C2, copied down:


=IF(AND(A2+0>0,A3+0>0),"Remove","")

[nzpetrol]

Awesome thanks mate. Appreciate that.

[Glenn Kennedy]

Great! I'm glad to have helped! Thanks for the Rep.

[nzpetrol]

OK - Job Done!

[nzpetrol]

Hi BSB,

I'm currently using your sheet to manipulate this data and I was wondering if you would be kind enough to add a couple of small details to the formula?

After they get put through your formula I manually edit the order of the columns (see attached). And then get Excel to recognize my dates as dates and sort the sheet from oldest to newest (please note dates in the format d,m,y - I'm from New Zealand).

I appreciated what you've done so far but if I could push my luck and ask you to alter that final bit that would be awesome.

Thanks mate.

[nzpetrol]

Sorry - attached this time.

[BadlySpelledBuoy]

As requested.

BSB

[nzpetrol]

Legend. Thanks mate!!