
Originally Posted by
kvsrinivasamurthy
Pl see attached file.
Hi kvsrinivasamurthy,
Technically, this thread is solved!
However, I noticed that my pivot data starts at row 5 instead of row 1 as shown in the sample. So taking the same data, H1:L4 is empty
I've made a change to the formula and tried to use it in my original, however, I'm getting #N/A value (but on the sample, it's not taking the #N/A value)
PS: I found the mistake. In the sample, #N/A is "FALSE" with ISTEXT function. However, in my original pivot, #N/A is considered as "TRUE" with ISTEXT function. Therefore, I've changed the formula with
=IFERROR(INDEX(Pivot!$H$6:$H$30,SMALL(IF(INDEX(Pivot!$I$6:$L$30,,1+INT((ROW()-2)/5))=$I2,ROW(Pivot!$H$6:$H$30)-1,""),COUNTIF(OFFSET($I2,0,0,-1-MOD((ROW()-2),5)),$I2))-4),"")
Bookmarks