Move data from rpt columns to single row by employee

[vsynowiec]

Hi, I am trying to move several data points by employee to a single row for that employee. Employee may have several grants, and I'd like move all grants onto single row.

The attached example explains the end goal. Pls take a look

[PaulM100]

Array formulas entered with Ctrl+Shift+Enter instead or regular Enter in J, L and N in the order below:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Formula copied into K, M and O:

Please Login or Register  to view this content.

[vsynowiec]

Thanks so much Paul!!! Worked perfectly. Can you pls explain how the lookup formula works? I like to understand what I'm doing.

[Glenn Kennedy]

Please amend your profile to show the Excel version you are using. This will work with Excel 2010+. Also indicate (approximately) where you are in the world, as regional settings can often be very important in reaching a solution.

Ordinary formulae:

J6: =IFERROR(INDEX($E:$E,AGGREGATE(14,6,ROW($E$6:$E$14)/($D$6:$D$14=$I6),1+INT((COLUMNS($I:I)-1)/2))),"")

K6: =IFERROR(INDEX($F:$F,AGGREGATE(14,6,ROW($E$6:$E$14)/($D$6:$D$14=$I6),1+INT((COLUMNS($I:J)-1)/2))),"")

format as date and number, respectively. Select BOTH cells and drag across and down.