SOLVED

Excel formula or VBA script

Occasional Contributor

I have an excel spreadsheet that I am doing grouping by rows function, then Subtotal function based off of invoice numbers (column A). In the total rows, I am needing more information carried down from the row above the total column. Is there a way to do this?

Example: 

Invoice NumberClient CodeInvoice DateTotal ChargesTotal ExpensesTotal Amount DueCOMPANYCUSTOMERLOCATIONINVC_PREFIXCUSTOMER NAME
080522-1411594UHC-MR44778.5914160056.88656.88661008ATL66URExample customer name
080522-1411594 Total  138753537.6917412.69     

 

I am needing the Company, Customer, Location, and Invc_Prefix to be in the total row as well. 

This spreadsheet has over 100 different invoice numbers, so I would need to do this at each row that has the "Total" after the invoice number in Column A. 

 

Thank you for your assistance. 

 

12 Replies

@dfuhrman840 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

https://ascensus.box.com/s/dhniry5epou1own9mn5vaicowcen1zsa
Here is the box drive path for the file. Please let me know if you can get to it or not.
Thank you,

@dfuhrman840 

No, I can't access the file, sorry. It requires a login.

Let me try a different way

@dfuhrman840 

That works. I have to do something else now, if there are no replies I'll get back to you later.

Thank you! I appreciate it!

@dfuhrman840 

Sub fill()

Dim i As Long
Dim j As Long

j = Range("F" & Rows.Count).End(xlUp).Row

For i = 1 To j

If Cells(i, 7).Value = "" Then
Range(Cells(i, 7), Cells(i, 10)).Value = Range(Cells(i - 1, 7), Cells(i - 1, 10)).Value
Else
End If

Next i

End Sub

You can try these lines of code. In the attached file you can click the button in cell L2 to run the macro.

@Quadruple_Pawn - Thank you! I will try this out. 

best response confirmed by dfuhrman840 (Occasional Contributor)
Solution

@Hans Vogelaar 

Alternatively: select G1:J57.

Press Ctrl+G or F5 to activate the Go To dialog.

Click Special...

Select Blanks.

Click OK.

G3 should now be the active cell in the selection.

Enter the formula   =G2   and press Ctrl+Enter to propagate the formula to all blank cells in the selection.

Thank you Hans. I will try this one as well and see which one i like better.

@Hans Vogelaar  Thank you!! This made it very easy!!