[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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