We help IT Professionals succeed at work.

# Fill Values in a row based on a column value

on
Hello, it's hard to put this in words but I'll try. There's also an example attached to help with the visual

I have a data dump of employee(s) various employee dates (U3 service, U5 quota and U9 Ret Contri),  The values were dump all in separate rows, but what makes them unique is the Pers.no joined with the Prior Term Start Date.  Each combination has a U3 and date on one row a U5 and date and a second row and possibly a U9 date on a 3rd row.  I need to get all three of these dates in one row

Currently

Pers.No Prior Term Start Date    UT     Date
1             9/14/2018                      U3     4/22/2007
1             9/14/2018                      U5     9/15/2018

How I need it to look

Pers.No Prior Term Start Date    UT     Date         U3                 U5                U9
1             9/14/2018                                           4/22/2017       9/15/2018
2018-to-Jul-2019-Salaried-Rehires--.xlsx
Comment
Watch Question

## View Solution Only

Finance Analyst
CERTIFIED EXPERT

Commented:
That can be achieved using a Pivot Table.

See attached.
2018-to-Jul-2019-Salaried-Rehires--.xlsx
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
You may copy below formula and paste in G3 then copy and paste special formula in any cell where you require desired result.

``````=IFERROR(INDEX(\$F\$3:\$F\$15,MATCH(\$A3&G\$2,INDEX(\$A\$3:\$A\$15&\$D\$3:\$D\$15,,),0)),"")
``````

check in attached...
AudraBreedlove_2018-to-Jul-2019-Sal.xlsx
Sr HRIS Analyst

Commented:
Shums: Perfect!! this is exactly what I was looking for, Thank you

Rob:  You are correct, it could be achieved in a pivot, but the INDEX formula given by Shums would allow me to update the table without using additional lookups after I've created the Pivot.  There are more columns that are not shown and over 9000 rows.  I really appreciate your input
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome Audra! Glad I was able to help.
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You can tweak the formula suggested by Shums like below so that you can place the formula in a single cell and then drag it across and down.

In G3
``````=IFERROR(IF(COUNTIFS(\$A\$3:\$A3,\$A3,\$B\$3:\$B3,\$B3)=1,INDEX(\$F\$3:\$F\$15,MATCH(\$A3&G\$2,INDEX(\$A\$3:\$A\$15&\$D\$3:\$D\$15,,),0)),""),"")
``````
And then drag it across to I3 and then down the rows.