Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Convert a report with sub groups to a list

Hello Experts,

I receive large sales reports in Excel that I need to be able to convert to a list. The report is grouped by customer name and ID on a different line than the related records.  I want to run a procedure that extracts the customer name and puts it on the same line as the invoice data.  The goal is to get a list that can be used for pivot tables or import.

Here's the steps that I identified:
-Remove Titles
-Insert new COL A
-Extract Customer name from string
-Populate COL A with the Customer name associated with correct invoice records
-Delete empty rows, subtotals, and section headers
-Anything else that you see wrong.

Attached is a workbook with the bad data that I receive in one tab and the result I need in the other tab.

Thanks for your help experts!
-TH
Add-Customer-to-each-record.xlsx
0
thutchinson
Asked:
thutchinson
  • 2
1 Solution
 
nutschCommented:
Here is a macro that should do all that neatly:

Sub Macro2()
Dim rgData As Range
Dim lLastRow As Long, lRowLoop As Long

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With


Rows("1:11").Delete Shift:=xlUp
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Columns("A").Insert
    
[a1] = Mid([b1], 12)
Range("A2:A" & lLastRow).FormulaR1C1 = "=if(left(rc[1],8)=""Customer"",right(rc[1],len(rc[1])-11),R[-1]C)"
Range("A1:A" & lLastRow).Value = Range("A1:A" & lLastRow).Value
Rows("1").Delete Shift:=xlUp
[a1] = "Customer"
    
With Range("A1:O" & lLastRow)
    .AutoFilter
    .AutoFilter Field:=8, Criteria1:= _
        "=Plug Cost", Operator:=xlOr, Criteria2:="="
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
    .Columns.AutoFit
End With
    
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub

Open in new window


Thomas
0
 
thutchinsonAuthor Commented:
Hi Thomas,

Perfection, as usual Thomas. I really appreciate it!

Best,

Todd
0
 
nutschCommented:
Glad to help.

THomas
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now