Pivot Table Issues

[Mldeuser]

Hello

I have a file that I need to convert to a pivot table to show vender, invoice number and to subtotal based on the invoice number. My issue is on the data file the vender name is not on the same row as the invoices. For example Vender ABC is in cell B15, the invoice numbers start on the row below the vender name and can vary by vender. How can I get the vender name next to each of the invoice numbers below sort of copy and paste them.

For the example I would need the vender name in rows B16 - B64. The invoice numbers are in column K,

The report has many vender names in column B that I need to be copied to rows below the number of rows is different for each vender.

Any help would be appreciated

Thank you

 

[Alex Blakenburg]

You can generally get around that using a helper column. Power Query or VBA would also be options you could consider.
If you can show us a sample of your data, including the Row & Column references especially of the vendor no & invoice no columns, we could be more specific

 

[Mldeuser]

Payables Aging Report
uscb-car
Period: 11/2022
As of : 11/08/2022
Payee	Payee Name	Doc Seq #	Control	Batch Id	Property	Invoice Date	Due Date	Post Month	Account	Invoice #	Base	Current	0-30	31-60	61-90	Over	Future	Notes
Code											Currency	Owed	Owed	Owed	Owed	90	Invoice
																Owed
ucbstma	STARLING MADISON LOFQUIST INC																0.00
		202200003083	P-3806	1796	51us011a	9/30/2022	10/31/2022	11-2022	5200-4100 Building R&M - Exterior (recoverable)	75282	usd	62.54	0.00	62.54	0.00	0.00	0.00	08/19/22 Engineering Services
		202200003083	P-3806	1796	51us011b	9/30/2022	10/31/2022	11-2022	5200-4100 Building R&M - Exterior (recoverable)	75282	usd	92.46	0.00	92.46	0.00	0.00	0.00	08/19/22 Engineering Services
		202200003083	P-3806	1796	51us011c	9/30/2022	10/31/2022	11-2022	5200-4100 Building R&M - Exterior (recoverable)	75282	usd	92.46	0.00	92.46	0.00	0.00	0.00	08/19/22 Engineering Services
		202200003083	P-3806	1796	51us011d	9/30/2022	10/31/2022	11-2022	5200-4100 Building R&M - Exterior (recoverable)	75282	usd	91.71	0.00	91.71	0.00	0.00	0.00	08/19/22 Engineering Services
Total ucbstma											usd	339.17	0.00	339.17	0.00	0.00	0.00

I am looking to have the vender name copied into the blank cells below. it would need to stop at the lats row with an invoice number. I would need this to work for each vender in the worksheet

 

[Alex Blakenburg]

If you are mainly using it for a Pivot Table, you can just use formulas out to the right hand side - see below.
Then all you need to do each time you get new data is to make sure the formulas cover all your rows (ie it extends to the last row of your new data)
Then use the additional columns for your pivot.

There is a manual fill down option which is fairly easy to do but you would need to do it each time you refresh the data(it involves using go to > Special > Blank), which means a macro or Power Query would be a better option. Let me know which option you are comfortable with and we can progress that.

20221115 VBA Fill Down Mldeuser.xlsx
	A	B																	S	T	U
1	Payables Aging Report
2	uscb-car
3	Period: 11/2022
4	As of : 11/08/2022
5	Payee	Payee Name																	Notes	Payee Code	Name
6	Code
7
8	ucbstma	STARLING MADISON LOFQUIST INC																		ucbstma	STARLING MADISON LOFQUIST INC
9																			08/19/22 Engineering Services	ucbstma	STARLING MADISON LOFQUIST INC
10																			08/19/22 Engineering Services	ucbstma	STARLING MADISON LOFQUIST INC
11																			08/19/22 Engineering Services	ucbstma	STARLING MADISON LOFQUIST INC
12																			08/19/22 Engineering Services	ucbstma	STARLING MADISON LOFQUIST INC
13	Total ucbstma																			Total ucbstma	STARLING MADISON LOFQUIST INC
14																				Total ucbstma	STARLING MADISON LOFQUIST INC
15	nextvendor	Another Vendor																		nextvendor	Another Vendor
16																			08/19/22 Engineering Services	nextvendor	Another Vendor
17																			08/19/22 Engineering Services	nextvendor	Another Vendor
18																			08/19/22 Engineering Services	nextvendor	Another Vendor
19																			08/19/22 Engineering Services	nextvendor	Another Vendor
20	Total nextvendor																			Total nextvendor	Another Vendor
Aged Payable

Cell Formulas
Range	Formula
T8:U20	T8	=IF(ISBLANK(A8),T7,A8)

 

[Mldeuser]

I would prefer a macro to run this as the venders will change each week.

Thank you

 

[Alex Blakenburg]

See if this does what you need. If it is too slow we will need to switch to using an array.

Sub FillDown()

    Dim ws As Worksheet
    Dim lastRow As Long, firstRow As Long
    Dim rngSrc As Range, rngBlanks As Range
   
    Application.ScreenUpdating = False
   
    Set ws = ActiveSheet
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    With Application
        firstRow = .IfError(.Match("Code", ws.Columns("A"), 0), 0)
        If firstRow = 0 Then
            MsgBox "Incorrect Worksheet, 'Code' not found in heading"
            Exit Sub
        End If
    End With
   
    With ws
        If .Range("A" & firstRow + 1) <> "" Then
            firstRow = firstRow + 1
        Else
           firstRow = .Range("A" & firstRow).End(xlDown).Row
        End If
        Set rngSrc = .Range(.Cells(firstRow, "A"), .Cells(lastRow, "A"))
    End With

    ' Fill Down Payee Code
    Set rngBlanks = rngSrc.SpecialCells(xlCellTypeBlanks)
    rngBlanks.FormulaR1C1 = "=R[-1]C"
    rngSrc.Value = rngSrc.Value
   
    ' Fill Down Payee Name
    Set rngSrc = rngSrc.Offset(, 1)
    Set rngBlanks = rngSrc.SpecialCells(xlCellTypeBlanks)
    rngBlanks.FormulaR1C1 = "=R[-1]C"
    rngSrc.Value = rngSrc.Value
   
    Application.ScreenUpdating = True
End Sub