Search code examples
excelformula

sequential line item increasing

I am hoping someone might be able to help me work out a formula, I am working on some data migration and I need to bring some data from one spreadsheet into multiple sheets. This is payment information where in one (original sheet) it had all the data, but in the new spreadsheet it needs to be moved into two sheets; one a summary (e.g. a payment of x amount was made) and a second sheet which lists all the items that were paid under that payment (like batch processing).

The issue I am having is in the second line item spreadsheet, there are a few columns of interest which include a Vendor number, a Payment Number, a Line Number and a Description.

What I need to get the spreadsheet to do is to generate a sequential line number which resets when the vendor and voucher number changes. For example if I only have one vendor and voucher number then the line number would just be 1, but if I have two of the same then the line becomes 2 and so on. The numbers run sequentially however, they need to reset back to zero when its a new vendor and voucher number.

Is there a formula someone could recommend to help me automate this process, as I a few thousand entries so i need to do it via formula (or VBA):

Example

Solution

  • Please try this formula, entered in C2 and copied down.

    =COUNTIFS(A$2:A2,A2,B$2:B2,B2,D$2:D2,D2)

    I'm not sure if you need to include column D in the formula. For your sample data the formula below will produce the same result.

    =COUNTIFS(A$2:A2,A2,B$2:B2,B2)

    In fact, the way I understand your question, the Payment Number should be unique and even the reference to column A (A$2:A2,A2)could be eliminated.