Need same value to reflect in Column B against each duplicate value in Coulmn A

0

I have a data in two columns Column A & Column B.
Consider column A has 'Employee ID' (numeric) and column B has 'IT Assets ID' (alphanumeric).
Column A has multiple duplicates for 'Employee ID', however column B may not necessarily have a cell value ('IT Asset ID') against each duplicate record for employee ID.
I want each duplicate record for 'Employee ID' in column A to reflect the same/respective 'IT Asset Value'.

I have

Emp ID IT Asset ID

1234 MD15342

5678

1470 AB22546

1234

1470

5678

1234

5678 RG66352

1234

I want

Emp ID IT Asset ID

1234 MD15342

5678 RG66352

1470 AB22546

1234 MD15342

1470 AB22546

5678 RG66352

1234 MD15342

5678 RG66352

1234 MD15342

Answer
Discuss

Discussion

Send me file i post easy method
beepetark Jan 5, '20 at 9:18 am
Add to Discussion

Answers

0

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.

  1. INDEX($A$1:$B$10 specifies the range to operate in.
  2. SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10<>"")*ROW($A$2:$A$10)) finds the row number.
  3. 2) specifies the column.
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login