VBA - Data spread across multiple columns needs to be consolidated into one column

[jmcginley3]

We have a system that exports billing information for insurance purposes. The bills are typically generated via PDF, but business want them in Excel so they can do calculations and manipulations with them on a spreadsheet. Whenever we export a bill as an Excel file it comes out looking pretty messed up. The vendor we use for our billing system can't do anything about it, so my final option is to hopefully create a macro that will format to make it more legible and useable.

I have done some work with creating macros in the past, but this is the biggest project I've ever attempted to take on. I'm hoping someone will be willing to help me understand how I can overcome some of these bigger issues.

One of the biggest issues I'm coming across is the data is spread across multiple columns:

1. Column B is supposed to be the Employee Name column (I just used letters of the alphabet as example names), however our export has some random instances where the employee's name is listed in column C instead
2. Column F is supposed to be "class", but in some instances the "class" indication is listed in column E instead.
3. On most of the spreadsheet you'll see that "Class", "Dental", and "Vision" are in columns F, G, H. At the bottom of the spreadsheet, the last section has these columns in E, F, H instead.

Any idea how to make sure all the data is in it's correct column? There really is nothing on this spreadsheet that is always constant except for the fact that "Emp No" (Employee Number) is always in Column A.

Is there any way to attach the actual file I'm working with? I've edited it to remove any sensitive data.

Here is a small example of what I'm talking about:

g, j, and p need to move over with the rest of the letters of the alphabet in the "Employee Name" column and the EE, EE+DEPS, and EE+SP need to move over with the rest of the listings in the Class column.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Emp No[/TD]
[TD]Employee Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Class[/TD]
[TD]Dental[/TD]
[TD]Vision[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]283[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]547[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + SP[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4195[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]544[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + SP[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]894[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + SP[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]516[/TD]
[TD][/TD]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]h[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + SP[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]58747[/TD]
[TD]i[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]533[/TD]
[TD][/TD]
[TD]j[/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + DEPS[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5865[/TD]
[TD]k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + DEPS[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]854[/TD]
[TD]l[/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + DEPS[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4112[/TD]
[TD]m[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + SP[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]664[/TD]
[TD]n[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]166[/TD]
[TD]o[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + DEPS[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2365[/TD]
[TD][/TD]
[TD]p[/TD]
[TD][/TD]
[TD]EE + SP[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]74411[/TD]
[TD]q[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]684[/TD]
[TD]r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE + DEPS[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]s[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Awesome! That worked great. Thanks for your help! The only reason I tried modifying that first one was because it didn't work correctly (since my actual data started at Row 7 and you wouldn't have known that based on what I shared originally). I just copied this straight into my macro and it worked. Thanks for your explanation on why what I modified did not work correctly. I'm learning more and more everyday and trying to understand VBA and this has been immensely helpful!

You are welcome - thanks for the reply.