We help IT Professionals succeed at work.

Fill Values in a row based on a column value

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

Rob HensonFinance 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)),"")

Open in new window


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

Author

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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome Audra! Glad I was able to help.
Subodh Tiwari (Neeraj)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)),""),"")

Open in new window

And then drag it across to I3 and then down the rows.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.