excelexcel-formulaexcel-2016

How do I populate empty cells with different values depending on what category the empty cells are in?

I need to fill the blank cells in column C with the number that is already there for each ITEM NUMBER. For example for ITEM NUMBER 19951034, there should be a value of 222,434 in column C for each cell. For ITEM NUMBER 4562, there should be a value of 23,543 in column C for each cell. Any help with this would be greatly appreciated.

A ITEM NUMBER C
22/10/2024 19951034
29/10/2024 19951034
29/10/2024 19951034
13/11/2024 19951034
15/11/2024 19951034 222,434
27/09/2024 623222 81,052
10/12/2024 623222
16/12/2024 623222
20/12/2024 623222
31/10/2024 4562
27/11/2024 4562 23,543
28/11/2024 4562
29/11/2024 4562
29/11/2024 4562
Solution

  • Not sure how one would do this without using a formula, however, using a formula applied to an adjacent column, can be accomplished as is:

    enter image description here

    • Formula used in cell D2

    =AGGREGATE(15,7,$C$2:$C$15/((B2=B$2:B$15)*(C$2:C$15<>"")),ROW($A$1))

    Once the formula is applied for the entire data, one can copy and paste as values, lastly remove the Column C to keep only the desired column.

    • Or can use MAX() function:

    =MAX((B2=$B$2:$B$15)*($C$2:$C$15<>"")*C$2:C$15)