Excel help please

[Participant]

Hi

I have an Excel report that chucks out an aged debtors from our accounting system (assume the report can't be easily modified). The report only has the customer in one cell, then lists a load of invoices, which makes analysing with pivot tables tricky as I have to manually fill down the customer in column A.

So for example, I might have:

Customer: GSK
- invoice 2323423
- invoice 4634234
- invoice 5462342

But I'd need each row to have the customer filled in. Currently I just copy paste each customer's name into the rows below, but is there a better way?

Many thanks.

 

[bz_1970]

Needs a macro

Robbie

I don't know if you are familiar with macros/VBA but that's the best way to solve things like this.

The best thing to do is to record a macro and immediately end it and then Press Alt-F11. You should see "Module 1" listed on the left hand side and in that you should see :

Sub Macro1()
'
' Macro1 Macro
'

'
End Sub

You need then to select this below and paste it completely over the above so it reads :

Public Sub Insert_Customer()
With Selection
customer = Trim(Mid(.Cells(1, 1), 10, 30))
For i = 2 To .Rows.Count
.Cells(i, 1) = customer & " " & .Cells(i, 1)
Next
.Rows(1).Delete
.Cells(1, 1).Select
End With
End Sub

All that you have to do then is to switch back to the spreadsheet, select the group of cells for one customer, press Alt-F8 and choose to Run "Insert_Customer". It will prefix all rows with the customer name and then delete the first row. Repeat for all other groups of customer invoices.

Make sure to select a group of cells before running it.

There may be more elegant ways to do it but it seems to work !

You might want to make a copy first as there's no "Undo".

DT

 

[Participant]

bz_1970, thanks a lot for that. I'll try it next time I use the sheet that calls for it i.e. next financial quarter. Today I bit the bullet and spent half an hour copy pasting!

 

[fretted]

Macros seem a bit of an overkill to me. Besides, I think he wanted the customers name to be in column a next to the invoice number (in col b), and not have the customers name in the same cell.

How many invoices and customers are we talking about here?

Couldnt you copy and drag selections down? or if you fill in the customer name in the col a, first row of invoice you can double click the bottom right hand corner to autofill down (I'm assuming there is a blank row in between customers).