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>

a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q
						PerControl	Pay Date		Period End Date	Job Code
	Smith, Bob			1234	Pay No: 1	201909271		9/27/2019		9/20/2019		DCW
					OT		3.5	12	63	Z		Fee		10.86	stuff4	21.02
					REG		40	12	480	Z					stuff5	16.67
															stuff6	0.33
															stuff7	52.66
															stuff8	7.87
															stuff9	33.66
							43.5		543					10.86		132.21
	Check Printed:		121880				Check Amount:		0		Direct Deposit:		399.93		Net:		399.93
						PerControl		Pay Date		Period End Date		Job Code
	Jones, Alice			1235	Pay No: 1	201909271		9/27/2019		9/20/2019		DCW
					PDATA		3.5	12	42	Z		Fee		3.84	stuff14	7.43
					REG		12.5	12	150	Z					stuff15	5.89
															stuff16	0.12
															stuff17	3.82
															stuff18	2.78
															stuff19	11.91
							16		192					3.84		31.95
	Check Printed:		121678				Check Amount:		0		Direct Deposit:		156.21		Net:		156.21

<tbody>
</tbody>

<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!