Getting messy data into one line

[snipescc]

A big part of my job is getting spreadsheet data from different clients and putting the information into our database. Most of our 200+ companies send reasonable spreadsheets I can manipulate pretty fast, but one insists on sending what I think is a scan of their paychecks, converted into Excel. There are about 6-10 lines per person, and I need to get a piece of data from people. However, not everyone has the piece of data we need, and where it is isn't consistent. Here is a cleaned up sample of the data. I need the number next to the word FEE. However, sometimes FEE is in column M, sometimes column N. Sometimes the number is right next to it, sometimes there is a blank cell between. So I'm looking in columns M & N to get a number that might be in N, O, or P. To make matters more complicated, the numbers are stored as text, so a formula like SUM or ISNUMBER won't necessarily work. I can multiply everything in those columns by 1 to force them into numbers, but that adds a 0 to every null cell. It's do-able, but might mess up any formula that is looking for if a cell is blank.

My current thought is using a series of IF statements to tell if a cell has a name. If it does, use either vlookup or sumif for getting the fee amount, by checking if FEE is in M, then sum the numbers in the two cells next to it. If it isn't, same for column N.

My expectation is I will have at least one, possibly 2 other sheets to help me transform the data, so I still have the original data if I need to check something manually.

Does anyone have some other ideas or something that would simplify this process?

I'm sorry this isn't in a proper grid. My security settings aren't letting me give assess to my clipboard. I put the data in this google sheet if anyone wants to see it in a proper grid.




<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]PerControl[/TD]
[TD]Pay Date[/TD]
[TD][/TD]
[TD="colspan: 2"]Period End Date[/TD]
[TD]Job Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Smith, Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234[/TD]
[TD]Pay No: 1[/TD]
[TD="align: right"]201909271[/TD]
[TD][/TD]
[TD="align: right"]9/27/2019[/TD]
[TD][/TD]
[TD="align: right"]9/20/2019[/TD]
[TD][/TD]
[TD]DCW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OT[/TD]
[TD][/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]63[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD]Fee[/TD]
[TD][/TD]
[TD="align: right"]10.86[/TD]
[TD]stuff4[/TD]
[TD="align: right"]21.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REG[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]480[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff5[/TD]
[TD="align: right"]16.67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff6[/TD]
[TD="align: right"]0.33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff7[/TD]
[TD="align: right"]52.66[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff8[/TD]
[TD="align: right"]7.87[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff9[/TD]
[TD="align: right"]33.66[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]43.5[/TD]
[TD][/TD]
[TD="align: right"]543[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10.86[/TD]
[TD][/TD]
[TD="align: right"]132.21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Check Printed:[/TD]
[TD][/TD]
[TD="align: right"]121880[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Check Amount:[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Direct Deposit:[/TD]
[TD][/TD]
[TD="align: right"]399.93[/TD]
[TD][/TD]
[TD]Net:[/TD]
[TD][/TD]
[TD="align: right"]399.93[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PerControl[/TD]
[TD][/TD]
[TD]Pay Date[/TD]
[TD][/TD]
[TD]Period End Date[/TD]
[TD][/TD]
[TD]Job Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jones, Alice[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1235[/TD]
[TD]Pay No: 1[/TD]
[TD="align: right"]201909271[/TD]
[TD][/TD]
[TD="align: right"]9/27/2019[/TD]
[TD][/TD]
[TD="align: right"]9/20/2019[/TD]
[TD][/TD]
[TD]DCW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PDATA[/TD]
[TD][/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]42[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD]Fee[/TD]
[TD][/TD]
[TD="align: right"]3.84[/TD]
[TD]stuff14[/TD]
[TD="align: right"]7.43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REG[/TD]
[TD][/TD]
[TD="align: right"]12.5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]150[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff15[/TD]
[TD="align: right"]5.89[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff16[/TD]
[TD="align: right"]0.12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff17[/TD]
[TD="align: right"]3.82[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff18[/TD]
[TD="align: right"]2.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff19[/TD]
[TD="align: right"]11.91[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD="align: right"]192[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.84[/TD]
[TD][/TD]
[TD="align: right"]31.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Check Printed:[/TD]
[TD][/TD]
[TD="align: right"]121678[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Check Amount:[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Direct Deposit:[/TD]
[TD][/TD]
[TD="align: right"]156.21[/TD]
[TD][/TD]
[TD]Net:[/TD]
[TD][/TD]
[TD="align: right"]156.21[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[MARK858]

I can multiply everything in those columns by 1 to force them into numbers, but that adds a 0 to every null cell. It's do-able, but might mess up any formula that is looking for if a cell is blank.

If you are using Excel then try using Text to Columns i.e.

Select the column, click "Data" tab - click "Text To Columns" - select "Delimited" and click "Next", make sure "Other" is not checked and click "Next", select General and click "Finish"

 

[snipescc]

Interesting. That's a neat trick!