How to convert rows to columns from file import

[shani1975]

Hello

I import data into excel that does not come across clean. In columns A thru D is how the data looks when I import the data into Excel. In columns G thru I is the info I need to extract from the import and how I need it displayed. As you can see not everyone will have both Meets Expectations or Below Expectations data and if there is no data for one of those categories, I also need the data to tell me zero for that agent. I am not sure how to get the data I need. Thanks in advance!!

 

[bosco_yip]

Try,

1] G2, copied down :

=IFERROR(INDEX(B$1:B$200,AGGREGATE(15,6,ROW(B$1:B$200)/(A$1:A$200=G$1),ROWS($1:1))),"")

2] H2, copied across right to I2 and all copied down :

=IF($G2="","",IFERROR(INDEX(OFFSET($B$1,MATCH($G2,$B$2:$B$200,0),0,5),MATCH(H$1,OFFSET($A$1,MATCH($G2,$B$2:$B$200,0),0,5),0)),0))

Regards
Bosco

 

[shani1975]

Thank you so very much, works like a charm!!