Macro help with combining data across 2 rows (and deleting non-required rows)

Hello,

I am trying to see if there is a macro that would help me fix the layout of the data (example file attached). The system (not mine) produces the info over 2 rows, and also out of line, but I need to have it in one row.

Currently I am just doing B5=A4, C5=B4 etc and copying the formulas down where required.

A Couple of issues though:

I only need the rows that show ‘accepted’ or ‘referred’ (and the corresponding rows beneath that).

Anything that is ‘new’, ‘rejected’ or ‘arbitrator’ I need deleted (and the corresponding row below this)

To compound this there are incidents in column A that have multiple rows (e.g. 66-68 & 73-76) so in these examples I currently manually delete everything but the most recent incident. Again is there a way for a macro to check if an incident (cell A4 is an example of an incident number) has multiple rows and keep the most recent row and delete the rows above it that relate to the same incident?

Any help greatly appreciated

Thanks

Innes

I've uploaded now how I would like the final layout to be.

Thanks for getting back to me.

Innes

Hello,

In the example of 66-68 all of these rows would be deleted (and therefore line 69) and none are 'accepted' or 'referered'

In the example of 73-76 only line 76 (and therefore line 77 also) would be kept as it is the most recent entry that is either accepted or referred (could you use column C that has date-time to identify the most recent entry in macro?)

Thanks

Innes

Hello,

Yes that's correct.

Thanks

Innes

Hello,

That worked perfectly thanks. However, I removed columns A-N from the original file thinking that when I added these back in and ran the macro it would delete these as well (Sorry, my mistake). I tried amending the code but code baffles me so I just messed it up :-(

I've attached a tab called 'original sample file' and highlighted the columns in yellow (A-N) I removed. Is it possible to amend the macro so that rather than starting at A3 it starts at O3 but still removed all the relevant rows, does that make sense?

Also, after it's all sorted out the rows is it possible to add some more code to insert a new column after column A and 'Text to Columns' the incident number based on a hypen (-)?

Much appreciated if you could.

Thanks

Innes

Hello,

Sorry, I thought it would make it easier by skimming down the amount of data but lesson learned for future reference!

I tried the new code but everything in column A-O is deleted. I've attached the ouput after the Macro is run so you can see.

Thanks

Innes

Hello,

I tried to edit the macro to include that part but I came unstuck. Can you advise on how I would change it to include that part please?

I would like it so that the macro checks for the most recent referred or accepted line and keeps it (and the the corresponding line beneath that) and deletes any other rows not required.

Basically what it is doing now but including columns A-N. Is there a simple way to fix this (even if I put a random letter in A4,A6,A8 etc prior to running the macro)

Thanks

Innes

Hello,

Sorry for delay in replying, I was not in work over the weekend.

I don't understand your last reply. I have attached a sample of the file I get sent to me and then a smaple of what I would like it to look like after the macro has run.

Thanks

Innes

Hello,

Yes it is now....apologies I'll blame it on being Monday morning.

I've deleted A4:N4 then run the macro and all appears to be working!

Thanks very much for everything, especially your patience!

Regards

Innes