Selected Answer
Please try this formula. Paste it to a cell in row 2 of any column and copy down.
=INDEX($A$1:$B$10,SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10<>"")*ROW($A$2:$A$10)),2)
The output will be a list of assets IDs you need for your column B. So, Copy/Paste Special > Values the results (to convert formulas to values) and then copy the values to your column B.
To modify the formula please note the following.
- INDEX($A$1:$B$10. The range must start in row 1 and end in the last row of the column in which you have the Asset IDs. You can include blank rows at the bottom. It doesn't matter how many columns are included in that range.
- The final ,2) in the formula specifies the number of the 'Asset ID' column. 2 corresponds to B, 3 would be C. If your 'Asset ID' column is colum G then you would need INDEX($A$1:$B$10 .... ,7)
- $A$2:$A$10=$A2. Here the range of the Employee IDs is specified. It's identical to the range of the rows ROW($A$2:$A$10). For better transparency let the last row be the same one as you specified for INDEX.
Of course, $A2 is the lookup value, defined with the row number relative to allow it to change for each row as you copy down.
- ($B$2:$B$10<>"") specifies the range of the Asset IDs. Make sure that the number of rows specified for the SUMPRODUCT function in columns A and B is identical.
In plain English, the formula returns the value in column B from the first row where the value in column A equals the lookup value and column B isn't blank. There are 3 components to this formula.
- INDEX($A$1:$B$10 specifies the range to operate in.
- SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10<>"")*ROW($A$2:$A$10)) finds the row number.
- 2) specifies the column.
