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.

Emp No	Employee Name				Class	Dental	Vision
283	a				EE	10	15
547	b				EE + SP	10	15
16	c				EE	10	15
4195	d			EE		10	15
544	e				EE + SP	10	15
894	f				EE + SP	10	15
516		g			EE	10	15
19	h				EE + SP	10	15
58747	i				EE	10	15
533		j			EE + DEPS	10	15
5865	k				EE + DEPS	10	15
854	l			EE + DEPS		10	15
4112	m				EE + SP	10	15
664	n				EE	10	15
166	o				EE + DEPS	10	15
2365		p		EE + SP		10	15
74411	q				EE	10	15
684	r				EE + DEPS	10	15
1235	s				EE	10	15

<tbody>
</tbody>

 

[JoeMo]

Is it always the case that anything in col C belongs in col B (same row) and anything in col E belongs in col F (same row)? And are those two types of misplaced data (B-data in C and F-data in E) the only types you encounter?

 

[jmcginley3]

That seems to be the case with B and C. Move anything found in C to B and keep in the same row.

I've included a screen shot with examples of the ways E and F get messed up. On my spreadsheet it's actually E that has most of the Class data. Sometimes the data will be in D and sometimes it will be in F. Sometimes it's like both the Name and the Class are shifted one column to the right, sometimes it's just one or the other. Sometimes when Class data is in shifted to the left into column D, the data for Dental and Vision is also shifted over to the left one column along with it. So that means there will be times when the Class column will have a value on a row, but the value it has is actually the value that is supposed to be in the Dental column.

Emp No	Employee Name				Class	Dental	Vision
198152	a				EE	10	15	This is normal.
2155	b			EE	10	15		Class, Dental, and Vision data is all shifted to the left one column.
576343	c					EE		Class data is shifted to the right one column (there is no data present for Dental/Vision in this cases -- which is correct)
688909		d				EE		Both the Name and the Class are shifted to the right one column.

<tbody>
</tbody>

 

[JoeMo]

It appears there are more than a few deviations from the norm that occur randomly. Not a situation that lends itself readily to writing a few lines of code to straighten things out. Maybe someone else will see an easier way.

 

[jmcginley3]

Thanks for the insight. Just so I can try to get it as automated as possible -- could you help with the part that is constant? The fact that anytime the Employee Name shows up in Column C that it should be moved to Column B? I ran a couple more extracts and confirmed there is nothing that should ever be in Column C that I want to keep. Moving everything from C to B would fix the names, and then I can move on to the next problem for now.

 

[JoeMo]

You are welcome. I'll write some code that moves names back to col B for you to try and post back later.

 

[JoeMo]

This assumes the layout shown in Post #1 where Emp No is in A1 and the only content in the whole of col C is the misplaced names.

Sub NamesToB()
Application.ScreenUpdating = False
On Error Resume Next
With Range("C:C").SpecialCells(xlCellTypeConstants, xlTextValues)
   .Offset(0, -1).Value = .Value
   .ClearContents
End With
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[jmcginley3]

Thanks for the help! My actual spreadsheet has a few lines of headers, so all the actual data starts in row 7. I was messing around and modified your code to make it start at row 7 and it worked, but only for the first name that it came across. On this particular document I've got, there are 3 names in Col C that should be in Col B. It moved the name on line 299 like it should, but then on lines 342 and 350 the names are still in the wrong column. Does it need to loop through that code to process every name? Did I inadvertently break something when I tried to get it to start at row 7?

        Application.ScreenUpdating = False    On Error Resume Next
    With Range(ActiveSheet.Range("C7"), ActiveSheet.Range("C7").End(xlDown)).SpecialCells _
    (xlCellTypeConstants, xlTextValues)
       .Offset(0, -1).Value = .Value
       .ClearContents
    End With
    On Error GoTo 0
    Application.ScreenUpdating = True

 

[JoeMo]

You set the target range in col C wrong. C7 to end(xlDown) will go from C7 to the next filled cell in col C. So, only C7, if it has a name in it, and that end cell will be moved.
Try this, and please copy it directly from your browser and test it before you do any "messing around" with it.

Sub NamesToB()
Dim lR As Long
lR = Cells(Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False
On Error Resume Next
With Range("C7:C" & lR).SpecialCells(xlCellTypeConstants, xlTextValues)
   .Offset(0, -1).Value = .Value
   .ClearContents
End With
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[jmcginley3]

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!