SOLVED

Excel formula or VBA script

Occasional Contributor

Excel formula or VBA script

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 Number Client Code Invoice Date Total Charges Total Expenses Total Amount Due COMPANY CUSTOMER LOCATION INVC_PREFIX CUSTOMER NAME
 080522-1411594 UHC-MR 44778.59141 600 56.88 656.88 66 1008 ATL66 UR Example customer name 080522-1411594 Total 13875 3537.69 17412.7

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.

12 Replies

Re: Excel formula or VBA script

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?

Re: Excel formula or VBA script

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,

Re: Excel formula or VBA script

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

Re: Excel formula or VBA script

Let me try a different way

Re: Excel formula or VBA script

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

Re: Excel formula or VBA script

Thank you! I appreciate it!

Re: Excel formula or VBA script

``````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.

Re: Excel formula or VBA script

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

best response confirmed by dfuhrman840 (Occasional Contributor)
Solution

Re: Excel formula or VBA script

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.

Re: Excel formula or VBA script

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

Re: Excel formula or VBA script

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