Sales History Report

[edsjr]

I have a report that has the customer name at the top then below it items they bought and then an empty row, another customer and items they bought. I need to move the customer name to column A so it's in the same row as the item number. I have about 550 customers in this report. How can I do this?

I can't post attachments but here is a link to a sample spreadsheet in my onedrive account. The top section is how the data i currently formatted and the bottom is how I need it formatted.

https://1drv.ms/x/s!AlMKyRBHlUsBgawHWo8LLapoIIRucw

Thank You in Advance!

Ed.

 

[Jeffrey Mahoney]

Insert a column so the cusomer names are in column B. Select all the rows of data in Column B. Run this macro

Sub MoveCustName()  Dim Cel As Range
  
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  
  For Each Cel In Selection
    If Cel.Offset(0, 1).Value = "" Then
      Cel.Offset(1, -1).Value = Cel.Value
    End If
  Next Cel
  
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
End Sub

 

[edsjr]

The customer names are in the same column as the item numbers. Will that matter? This data is coming out of an old system that exports via .txt

 

[edsjr]

Thanks, now I just need to figure out how to run the macro

 

[edsjr]

Can someone take a look, I can't get this to run correctly

 

[SpillerBD]

The macro appears to do what I often do manually. But the macro provided does not appear to do the fill down part.
If the Cell to the right of the Customer Name is Blank, then the Customer Name, else the cell above.
Then I use Copy-Paste Special/Values.

If you are using Excel 2016, you can skip Macro or formula and use the Get&Transform feature.

 

[SpillerBD]

Maybe line

If Cel.Offset(0, 1).Value = "" Then

should be

If Cel.Offset(0, 2).Value = "" Then

 

[edsjr]

Sorry, I'm struggling to get this. To be upfront, I'm pretty good with excel and use productivity suite from add-in.com and asap utilities in case you know of a function that will do this in one of those. I've never learned anything about macros

Here is another example, this is actually a sampling of the file I am working on. Top is as the file is and bottom is as I need it, I had my customer send me a different file so now item number is at the top....if I can just get the item number on the same row as the customer name and other info all would be good in the world.

https://1drv.ms/x/s!AlMKyRBHlUsBgawhoo4GUWeRHpMs1g

FWIW, I use Excel 2016

 

[SpillerBD]

I drop into A4

=IF(ISERROR(FIND("NUMBER:",D4)),A3,D4)

and fill down. Then Copy and paste values.
Then, using Auto Filter, I filter non-data rows and delete.

 

[edsjr]

I drop into A4

=IF(ISERROR(FIND("NUMBER:",D4)),A3,D4)

and fill down. Then Copy and paste values.
Then, using Auto Filter, I filter non-data rows and delete.

Thank you so much, this did the trick!