Formulae to populate rows that are dynamically listed

[pschroeder]

Hi.

I'm attempting to populate Customer ID and Customer Name on rows for which adjacent cells are populated with invoice info. In doing so, I want to avoid having to define a tailored formula for each respective customer listing set, otherwise for each change in customer, the formula will be different on a relative basis. I'm seeking a solution which can be copied down-page.

The challenge is that each customer listing is positioned a different number of cells from the preceding customer listing due to the number of invoice records listed per customer.

Referring to the attached image, I'm attempting to populate fields in column H and column I, where there is a change in customer name (or ID) and for only those rows containing invoice information.

Is this at all possible?

Thanks

 

[RobP]

Hi,

I couldn't manage to do a formula that copies down, but have a macro that you could copy in and run on that page.
If you've not played with Macros before, just a simple search for loading and running excel VBA code should get you started, and just paste my code in.

Caveat is that your data is actually in columns I : M as your picture shows, and there is no data in the Col (I) after the last customer name.
Your data also has a blank cell under each set of invoice ID's (eg. J21, J25 etc.) which needs to be kept as thats the trigger for the last invoice of that set.

Hope it helps. Cheers

Sub populate_ID()


last_ID = ActiveSheet.Cells(Rows.Count, 9).End(xlUp).Row  'gets last row of Col "I" for last ID.

'loop through all customer ID's
For Count = 1 To last_ID

    If ActiveSheet.Cells(Count, 9).Value = "Customer name:" Then
    
        customer = ActiveSheet.Cells(Count, 10).Value
        ID = ActiveSheet.Cells(Count + 1, 10).Value
        
        inv_start_row = Count + 5
        
        Do While ActiveSheet.Cells(inv_start_row, 10).Value <> ""
            ActiveSheet.Cells(inv_start_row, 8).Value = ID
            ActiveSheet.Cells(inv_start_row, 9).Value = customer
            inv_start_row = inv_start_row + 1
        Loop
        
        Count = inv_start_row 'increase to start next customer ID loop
        
    End If

Next 'customer ID

End Sub

 

[pschroeder]

Hi,

I couldn't manage to do a formula that copies down, but have a macro that you could copy in and run on that page.
If you've not played with Macros before, just a simple search for loading and running excel VBA code should get you started, and just paste my code in.

Caveat is that your data is actually in columns I : M as your picture shows, and there is no data in the Col (I) after the last customer name.
Your data also has a blank cell under each set of invoice ID's (eg. J21, J25 etc.) which needs to be kept as thats the trigger for the last invoice of that set.

Hope it helps. Cheers

Sub populate_ID()


last_ID = ActiveSheet.Cells(Rows.Count, 9).End(xlUp).Row  'gets last row of Col "I" for last ID.

'loop through all customer ID's
For Count = 1 To last_ID

    If ActiveSheet.Cells(Count, 9).Value = "Customer name:" Then
   
        customer = ActiveSheet.Cells(Count, 10).Value
        ID = ActiveSheet.Cells(Count + 1, 10).Value
       
        inv_start_row = Count + 5
       
        Do While ActiveSheet.Cells(inv_start_row, 10).Value <> ""
            ActiveSheet.Cells(inv_start_row, 8).Value = ID
            ActiveSheet.Cells(inv_start_row, 9).Value = customer
            inv_start_row = inv_start_row + 1
        Loop
       
        Count = inv_start_row 'increase to start next customer ID loop
       
    End If

Next 'customer ID

End Sub

Thanks Rob. Appreciate your response.

 

[RobP]

No problem, thanks for the feedback.
Rob