Help : Multiple Match & Index

[Murari Rajagopalan]

Hello There,
Need help. I have data in excel wherein the Empid is repeated multiple times in rows and need to output @ employee level as per Sheet 2. I can create the column header manually based on the maximum value found as well.
Please help

Regards
Murari R

 

[Naresh Yadav]

Hi,

Pls try this one

IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW($2:$10)-1),COLUMN(A$1))),"") finish this with CTRL+SHIFT+ENTER

 

[bosco_yip]

1] A2, copied down :

=IFERROR(INDEX(Sheet1!A$2:A$10,MATCH(0,INDEX(COUNTIF(A$1:A1,Sheet1!A$2:A$10),0),0)),"")

2] B2, copied across and all copied down :

=IF($A2="","",IFERROR(INDEX(Sheet1!$B$1:$B$10,AGGREGATE(15,6,ROW(Sheet1!$B$1:$B$10)/(Sheet1!$A$1:$A$10=$A2),COLUMNS($A:A))),""))

Regards
Bosco

 

[Murari Rajagopalan]

thanks to everyone ! Appreciate the support !

 

[neeraj2050]

Hi,

Would you be kind enough to brief the following highlighted parts of the formulas

=IFERROR(INDEX(Sheet1!A$2:A$10,MATCH(0,INDEX(COUNTIF(A$1:A1,Sheet1!A$2:A$10),0),0)),"")

=IF($A2="","",IFERROR(INDEX(Sheet1!$B$1:$B$10,AGGREGATE(15,6,ROW(Sheet1!$B$1:$B$10)/(Sheet1!$A$1:$A$10=$A2),COLUMNS($A:A))),""))

Thanks in advance!!

Regards
Neeraj Kumar Agarwal