Combine elements of a report to create a list

thutchinson
thutchinson used Ask the Experts™
on
Hi Experts,

A low-tech vendor sends me reports in Excel with the customer name and ID on a different line than the related records.  I want to run a procedure that puts the information identifying the customer on the same line as the invoice data.  In other words, I need to tag the invoice data with the customer ID from Col B and the Customer name from COL D.  The goal is to get a list that can be used for pivot tables or import.

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

Thanks for your help experts!
Carlisle-Sample-File.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Here's a macro that will do most of that for you. You only need to adjust a couple of the columns and you're all set:

Sub FixStuff()
Dim lLastRow As Long


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


lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

Columns("A:C").Insert

With Cells(1, 4).CurrentRegion
    .AutoFilter
    .AutoFilter 1, "Customer"
    
    Range("A2:C" & lLastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[4]"
    Range("A2:C" & lLastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    
    .AutoFilter
    
    Range("A2:C" & lLastRow).Value = Range("A2:C" & lLastRow).Value
    
    .AutoFilter 1, "Customer"
    
    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
    .AutoFilter
End With

Columns("B").Delete
Cells(1, 1) = "CUST"
Cells(1, 2) = "Customer Name"

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With


End Sub

Open in new window


Thomas

Author

Commented:
Thomas,

That is fabulous.  Works great. There's some good code in here.  
How can I learn to do this?

Author

Commented:
Thank you. Thank you.-TMH
Top Expert 2008

Commented:
You practice a lot. You can check out the Excel VBA for Dummies by John Walkenbach, hang around EE and start trying to answer questions, record some macros and look at the code you get (though it won't be very good code for sure).

You can also hang out at the following sites, as quoted from Brad Yundt:

"A person interested in Excel will find numerous solutions to standard problems on sites run by Chip Pearson http://www.cpearson.com/excel/topic.aspxhttp://www.cpearson.com/excel/topic.aspx, John Walkenbach http://spreadsheetpage.com/index.php/site/tips/, Jon Peltier http://peltiertech.com/, Debra Dalgleish http://contextures.com/tiptech.html, and Ron de Bruin http://www.rondebruin.nl/ "

Author

Commented:
Good tips.  Thanks again Thomas.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial