Multi-Parts Log Calculation

  • I was given an issue with the simple problem of working out when a combination of parts had been completed.


    Column A is the date of manufacture

    Column B is the first component: PSU

    Column C is the second component: Top Board

    Column D is the third component: Fan


    50 of each of the 3 components can be manufactured each day, but it's more likely that only 50 of one, or two, components will actually be produced.

    I need to mark the completion of 50 of each component when it occurs with a 1 in column E.


    I have found a solution in the attached but there must be a better methodology?


    In the attached, I’ve used columns H, I, and J to get the cumulative values which shows the change of totals per column.

    In columns K, l, and M, I’ve got the change of values from the cumulative columns H, I, and J.

    Column N returns the unique values from K, L, and M.

    Column O returns the last row number of the unique values across columns K, L, and M. (This is where each of the 3 columns obtains a unique 50, 50, and 50)

    Finally, we can return to column E to display a 1 to match the current row number minus 1, because the data starts on Row 2 of the worksheet, and if there’s no match leave the cell empty, otherwise it matches and returns a 1 in the correct row.


    I also created the same idea with a formatted table to allow expansion of extra rows which I like better but I still feel there must be something less complicated.


    Any ideas appreciated.

    Multi-Parts Log.xlsx