Unconsolidate data

[bmatthewstevens]

I have tried my hand at macos, and what's left of my hair is starting to fall out... could anyone help me with a macro to unconsolidate an excel file? If it was only a few I would just do it by hand, but I'm filling in for a co-worker who quit and this is nothing that I've done before, I'm in networking.

Here is what I have

GL Number	IVNUM	Charge Description	Financial Class	Qty	Charges	Total Qty	Total Charges
10000.000	99003	BALANCE TRANSFER	MEDICARE	658​	2,844.62
10000.000			MEDICAID	42​	3,106.39
10000.000			BLUE CROSS	692​	(2,576.55)
10000.000			COMMERCIAL	643​	(4,903.98)
10000.000			HMO/PPO		-
10000.000			PRIVATE	1022​	1,614.98	3057​	85.46
10203.000	9903000	TRANSFERS TO/ FROM SURGERY CLINIC	MEDICARE		-
10203.000			MEDICAID		-
10203.000			BLUE CROSS	1​	148.15
10203.000			COMMERCIAL		-
10203.000			HMO/PPO		-
10203.000			PRIVATE		-	1​	148.15
10234.000	99006	REFUND FROM AR	MEDICARE	62​	35,987.42
10234.000			MEDICAID	27​	2,336.62
10234.000			BLUE CROSS	441​	56,376.17
10234.000			COMMERCIAL	119​	33,105.98
10234.000			HMO/PPO		-
10234.000			PRIVATE	13​	1,141.38	662​	128,947.57

Here is what I need it to look like.

GLNUM	IVNUM	Desc	Medicare QTY	Medicare AMT	Medicaid QTY	Medicaid AMT	BC QTY	BC AMT	COM QTY	COM AMT	PP QTY	PP AMT	TOTAL QTY	TOTAL
10000.000	99003	BALANCE TRANSFER	392	$46.40	8	$0.00	328	$44.02	295	-$148.66	189	$11.79	1212	-$46.45
10203.000	9903000	TRANSFERS TO/ FROM SURGERY CLINIC		$0.00		$0.00	5	-$143.48		$0.00	1	-$26.28	6	-$169.76
10234.000	99006	REFUND FROM AR	55	$9,876.78	7	$388.28	138	$14,858.17	58	$25,350.24	5	$226.84	263	$50,700.31

 

[alansidman]

Cannot see how your numbers in what you have get to what you want. Cannot determine the correlation. Also, not familiar with Med terms, so does PP correlate to HMO/PPO? What is the relationship for Private? Ignored? Really need to understand better what your mapping is.

 

[Alex Blakenburg]

I noticed that you started another thread with the same question but have not answered Alan's question here.
It looks like you are not Unconsolidating as much as doing a Fill Down and a Pivot.
Are you open to using Power Query ?
Also how many rows of data do you have ?
Do your dashes show a 0 in the formula bar ?
Do you know how to do a Pivot table, if we were to only automate the Fill Down part ?

 

[bmatthewstevens]

I started the new thread because I couldn't edit the data to show what I was wanting in this one. The reason for that is the data I was displaying as what I needed is this years data, and the example of what I needed was last years data, the numbers aren't the same. I changed that in the second post, so that it would all make more sense for someone else looking at it.

The new data I posted in the other thread answered his questions and made the mapping more clear with the column names matching correctly.


Here is that new data that I posted

The data I have

GL Number	IVNUM	Charge Description	Financial Class	Qty	Charges	Total Qty	Total Charges
10000.000	99003	BALANCE TRANSFER	MEDICARE	658	2,844.62
10000.000			MEDICAID	42	3,106.39
10000.000			BLUE CROSS	692	(2,576.55)
10000.000			COMMERCIAL	643	(4,903.98)
10000.000			HMO/PPO		-
10000.000			PRIVATE	1022	1,614.98	3057	85.46
10203.000	9903000	TRANSFERS TO/ FROM SURGERY CLINIC	MEDICARE		-
10203.000			MEDICAID		-
10203.000			BLUE CROSS	1	148.15
10203.000			COMMERCIAL		-
10203.000			HMO/PPO		-
10203.000			PRIVATE		-	1	148.15
10234.000	99006	REFUND FROM AR	MEDICARE	62	35,987.42
10234.000			MEDICAID	27	2,336.62
10234.000			BLUE CROSS	441	56,376.17
10234.000			COMMERCIAL	119	33,105.98
10234.000			HMO/PPO		-
10234.000			PRIVATE	13	1,141.38	662	128,947.57

What I need it to look like

GLNUM	IVNUM	Charge Desc	Medicare QTY	Medicare AMT	Medicaid QTY	Medicaid AMT	BC QTY	BC AMT	COM QTY	COM AMT	HMO/PPO QTY	HMO/PPOAMT	Private QTY	Private AMT	Total Qty	Total Charges
10000.000	99003	BALANCE TRANSFER	658	2,844.62	42	3,106.39	692	(2,576.55)	643	(4,903.98)			1022	1,614.98	3057	85.46
10203.000	9903000	TRANSFERS TO/ FROM SURGERY CLINIC		$0.00		$0.00	1	148.15		$0.00			1	148.15	1	148.15
10234.000	99006	REFUND FROM AR	62	35,987.42	27	2,336.62	441	56,376.17	119	33,105.98			13	1,141.38	662	128,947.57

 

[bmatthewstevens]

It basically is removing the financial class column and making those options just their own individual columns. I can't imagine there isn't a way to do this, I just know I don't know how as this isn't my area, which is why I reaching out to those who might know more. I greatly appreciate any help, even if it's just some instructions on how to do this easier. If I knew how to dump it all into sql or access, to then pull it correctly out in a report, I could figure that all out too, I've just not been able to import the data correctly.

Honestly, i'm willing to do just about anything at this point.. I have 28,000 lines in this report to condense down to about 7,000, so it's way too much for me to do manually.

 

[johnnyL]

How about:

Sub Consolidate()
'
    Dim ResultsSheetMissing     As Boolean
    Dim ArrayRow                As Long, DestinationRow     As Long
    Dim ResultsSheetName        As String
    Dim DestinationArray()      As Variant, SourceArray     As Variant
    Dim HeaderTitlesToPaste     As Variant
    Dim DestinationWS           As Worksheet, SourceWS      As Worksheet
'
'-----------------------------------------------------------------------------------------------------------------------
'
    ResultsSheetName = "Results Sheet"                                                                                  ' <--- Set this to the name of the destination sheet
    Set SourceWS = Sheets("Sheet1")                                                                                     ' <--- Set this to the name of the source sheet
'
'-----------------------------------------------------------------------------------------------------------------------
'
    HeaderTitlesToPaste = Array("GLNUM", "IVNUM", "Charge Desc", "Medicare QTY", "Medicare AMT", "Medicaid QTY", _
            "Medicaid AMT", "BC QTY", "BC AMT", "COM QTY", "COM AMT", "HMO/PPO QTY", "HMO/PPO AMT", "Private QTY", _
            "Private AMT", "Total Qty", "Total Charges")                                                                ' Header row to paste to destination sheet
'
    ResultsSheetMissing = Evaluate("IsError(Cell(""col"",'" + ResultsSheetName + "'!A1))")                              ' If ResultsSheetMissing = False then the sheet does exist
'
    If ResultsSheetMissing = False Then                                                                                 ' If the ResultsSheetName exists then
        Application.DisplayAlerts = False                                                                               '   Turn DisplayAlerts off ... sheet deletion causes popup
        Sheets(ResultsSheetName).Delete                                                                                 '   Delete the sheet
        Application.DisplayAlerts = True                                                                                '   Turn DisplayAlerts back on
    End If
'
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = ResultsSheetName                                                     ' Add the ResultsSheet & name it
    Set DestinationWS = Sheets(ResultsSheetName)                                                                        ' Set the DestinationWS
'
    SourceArray = SourceWS.Range("A2:H" & SourceWS.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row)           ' Load source data into 2D 1 based SourceArray
'
    ReDim DestinationArray(1 To UBound(SourceArray, 1), 1 To 17)
'
    DestinationRow = 0                                                                                                  ' Initialize DestinationRow
'
    For ArrayRow = 1 To UBound(SourceArray, 1) Step 6                                                                   ' Loop through the SourceArray rows
        DestinationRow = DestinationRow + 1                                                                             '   Increment DestinationRow
        DestinationArray(DestinationRow, 1) = SourceArray(ArrayRow, 1)                                                  '   GLNUM
        DestinationArray(DestinationRow, 2) = SourceArray(ArrayRow, 2)                                                  '   IVNUM
        DestinationArray(DestinationRow, 3) = SourceArray(ArrayRow, 3)                                                  '   Charge Desc
        DestinationArray(DestinationRow, 4) = SourceArray(ArrayRow, 5)                                                  '   Medicare QTY
        DestinationArray(DestinationRow, 5) = SourceArray(ArrayRow, 6)                                                  '   Medicare AMT
        DestinationArray(DestinationRow, 6) = SourceArray(ArrayRow + 1, 5)                                              '   Medicaid QTY
        DestinationArray(DestinationRow, 7) = SourceArray(ArrayRow + 1, 6)                                              '   Medicaid AMT
        DestinationArray(DestinationRow, 8) = SourceArray(ArrayRow + 2, 5)                                              '   BC QTY
        DestinationArray(DestinationRow, 9) = SourceArray(ArrayRow + 2, 6)                                              '   BC AMT
        DestinationArray(DestinationRow, 10) = SourceArray(ArrayRow + 3, 5)                                             '   COM QTY
        DestinationArray(DestinationRow, 11) = SourceArray(ArrayRow + 3, 6)                                             '   COM AMT
        DestinationArray(DestinationRow, 12) = SourceArray(ArrayRow + 4, 5)                                             '   HMO/PPO QTY
        DestinationArray(DestinationRow, 13) = SourceArray(ArrayRow + 4, 6)                                             '   HMO/PPO AMT
        DestinationArray(DestinationRow, 14) = SourceArray(ArrayRow + 5, 5)                                             '   Private QTY
        DestinationArray(DestinationRow, 15) = SourceArray(ArrayRow + 5, 6)                                             '   Private AMT
        DestinationArray(DestinationRow, 16) = SourceArray(ArrayRow + 5, 7)                                             '   Total Qty
        DestinationArray(DestinationRow, 17) = SourceArray(ArrayRow + 5, 8)                                             '   Total Charges
    Next                                                                                                                ' Loop back
'
    DestinationWS.Range("A1:Q1").Value = HeaderTitlesToPaste                                                            ' Write header row to DestinationSheet
'
    DestinationWS.Range("A2").Resize(UBound(DestinationArray, 1), UBound(DestinationArray, 2)) = DestinationArray       ' Write the DestinationArray to the destination sheet
'
    DestinationWS.Columns("A:A").NumberFormat = "0.000"                                                                 ' Format column A in the destination sheet to 3 decimal places
'
    DestinationWS.Range("E:E,G:G,I:I,K:K,M:M,O:O,Q:Q").NumberFormat = "#,##0.00_);(#,##0.00)"                           ' Format the Amount columns on the destination sheet
'
    With ActiveSheet.UsedRange
        .Replace "-", 0, xlPart, , , , False, False                                                                     ' Replace the dashes with zeros
        .Value = .Value
   End With
'
    DestinationWS.UsedRange.Columns.AutoFit                                                                             ' Autofit the widths of all used columns
End Sub

 

[Alex Blakenburg]

See if the code Johnny gave you works for you. It will mean that if you get new Finance Classes or they are in a different order you will need to get into the code to fix it.
I would normally address this sort of thing with a fill down of column B & C and then simply pivot the Qty and Amt fields.

If you are familiar with MS Access and SQL already then I would definitely think Power Query would be the way to go.

In terms of getting you going quickly you could try this:
1) Run the code below it will Fill Down columns B & C
2) Create the pivot

• Click anywhere in the data area and Insert > PivotTable > From Table/Range
• Go with the New Sheet option
• You will get a blank Pivot Table and if you click inside it you will get a Pivot Fields Navigation Panel
• Make it look like the 2nd screen shot below.
• You can rename and rearange fields if you need to.
• By clicking in a field you will get an option Value Field Settings. That is where you change Qty or Amt if it says count instead of sum and where you set the field format (eg commas and no of decimals.

Fill Down Code:

FillDown()

    Dim ws As Worksheet
    Dim rng As Range, arr As Variant
    Dim lastRow As Long, i As Long
   
   
    Set ws = ActiveSheet
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    With ws
        Set rng = .Range(.Cells(2, "B"), .Cells(lastRow, "C"))
    End With
   
    arr = rng.Value
    For i = 1 To lastRow - 1
        If arr(i, 1) = "" Then
            arr(i, 1) = arr(i - 1, 1)
            arr(i, 2) = arr(i - 1, 2)
        End If
    Next i
   
    rng.Value = arr
   
End Sub

Pivot Table Images

 

[bmatthewstevens]

That worked perfect… sort of…

It did about 20% then through a error 7 out of memory.

Even opening a new document cutting out a chunk of data, closing everything except excel even in task manager… it won’t let that macro run any more… which puzzles me that a new document wouldn’t work…

 

[Alex Blakenburg]

Which macro did you run ? Johnny's ?

 

[bmatthewstevens]

yes Johnny's