Forum Discussion

sherikhan88's avatar
sherikhan88
Copper Contributor
Sep 09, 2023

Formula for createing transfer order ID's in excel for store and category combination

In attached file, I want to write a formula which can assigned a unique transfer order ID if the condition met.
Conditions for creating new transfer order:
1. if store is change by category remain same then new transfer order id should generate

2. if category change but store remain same than also new transfer ID should assign

3. if category and store is same but the sum of quanity for combination of store and category greater than 300 then a new transfer order ID created.(means for remaining quantities new order ID created and it keep created transfer ID till the time all quantities exausted but no transfer order can have more than 300 quantity)

Note: transfer order id can be numerical starts from 1 then then keep adding based on the conditions.

4. transfer order ID should be unique and cannot be repeated in any condition.

5. 5. transfer ID can have less than 300 quanitities if no more quantity to add remains but it should not have more than 300 quantity and can be created for one store and one category at a time 

BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
training
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 11, 2023

    sherikhan88 

    To achieve this in Excel 365, you can use a combination of formulas, helper columns, and a VBA macro. Here is a step-by-step guide to create a unique transfer order ID based on the conditions you specified (without having opened your Excel file, for personal security reasons) :

    Step 1: Add a Helper Column

    1. Insert a new column (e.g., Column E) and label it "Unique Transfer Order ID."

    Step 2: Enter the First Transfer Order ID 2. In cell E2, enter the first transfer order ID manually as "TO-1" since it is the initial transfer order.

    Step 3: Enter Formulas for Unique Transfer Order IDs 3. In cell E3 (the second row of the "Unique Transfer Order ID" column), enter the following formula:

    =IF(OR(A3<>A2, B3<>B2, D3+SUMIFS(D$2:D2, A$2:A2, A3, B$2:B2, B3)>=300), "TO-"&COUNTIF(E$2:E2,"TO-*")+1, "TO-"&COUNTIF(E$2:E2,"TO-*"))

    This formula checks the conditions:

    • If the store (Column A) changes OR the category (Column B) changes OR the sum of quantities (Column D) for the same store and category exceeds 300, a new transfer order ID is assigned.
    • Otherwise, it continues with the same transfer order ID.

    Step 4: Drag the Formula Down 4. Drag the formula in cell E3 down to fill the entire "Unique Transfer Order ID" column. This will automatically assign transfer order IDs based on your conditions.

    Step 5: Test Your Data 5. Test your data by changing store, category, or quantities to ensure that the transfer order IDs are assigned correctly according to your conditions.

    Step 6: Implement the VBA Macro (Optional) 6. If you want to automate this process with a VBA macro, you can create a macro that executes the above steps when you open the workbook or when you manually trigger it. Here is a basic outline of the VBA code:

    Sub AssignTransferOrderIDs()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 3 To LastRow
        If Cells(i, 1) <> Cells(i - 1, 1) Or Cells(i, 2) <> Cells(i - 1, 2) Or Cells(i, 4) + WorksheetFunction.SumIfs(Range("D2:D" & i), Range("A2:A" & i), Cells(i, 1).Value, Range("B2:B" & i), Cells(i, 2).Value) >= 300 Then
            Cells(i, 5).Value = "TO-" & Application.WorksheetFunction.CountIf(Range("E2:E" & i), "TO-*") + 1
        Else
            Cells(i, 5).Value = "TO-" & Application.WorksheetFunction.CountIf(Range("E2:E" & i), "TO-*")
        End If
    Next i
End Sub

    This VBA macro replicates the logic of the formula across the specified range.

    Remember that when using VBA macros, you should enable macros in Excel, and you can run the macro via the Developer tab or by creating a button for it. Save your workbook as a macro-enabled file (.xlsm) if you use VBA macros.

    Now you have two options :smile:: use the formula or automate the process with the VBA macro, depending on your preference and familiarity with VBA. The text and steps were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources