Correcting disjointed data.

[melastro]

Good morning members,

I've attached some sample(dummy) data and I hope that someone may be able to help with my dilemma.
The sample is shown at this link. I couldn't download the XL2BB add-in due to some internal security settings set by our IT people.

The sample data is shown to go to column W but actually goes out to column AH and, at times, even further along. I have deleted some columns to hopefully make the sample easier to view. My actual data is set out as shown in the sample. The problem I have is that the data comes from an external source and the data from column M to the last column is disjointed. By this I mean that it is offset by one column to the right.
Currently, I (or an assistant) have to manually place the data into the correct column.
The problem starts at column M. You may notice that in column M ("Contact"), there is some text shown as "PRT". This text is not needed and is meaningless for our purposes. I don't know why it continues to come through. Column M should be just contact names, noted as Next Name2, Next Name3 etc....but because of the unwanted "PRT" text, the names are pushed into the next column(N) which means that all the following data is pushed into the next column and so on. You can see that the final AS & D value is pushed out into column W. This then means that all the AS & D numeric values are in the wrong columns which then creates new headaches for me.
I was wondering if the process to bring the columns back into order could be automated. Wasting time doing this is not ideal as the data can have, at times, thousands of rows.

Hopefully someone may have an idea on how we could automate the 'repairs'. Please let me know if you require any further information.
Thank you for any help or advice.

Kindly,
Mel.

 

[vcoolio]

Hello Mel,

If I've understood you and the data sample correctly, you may only need the following VBA code to sort out the problem. It assumes that the data in Column N is always text.

Sub MelsTest()

    Dim ws As Worksheet: Set ws = Sheets("Contractor Data")
    
    On Error Resume Next
    ws.Range("N2", ws.Range("N" & ws.Rows.Count).End(xlUp)).SpecialCells(2, 2).Offset(, -1).Delete xlShiftToLeft
    On Error GoTo 0
    
End Sub

Assign the code to a button and test it in a copy of your actual workbook first.
I hope that this sorts out your dilemma.

Cheerio,
vcoolio.

 

[melastro]

Thank you Vcoolio. It worked treat! Thank you for saving us many hours of unnecessary work.

Kindly,
Mel.

 

[vcoolio]

You're welcome Mel. I'm glad to have been able to assist.

Cheerio,
vcoolio.