May 05 2019 11:39 AM
How do get values from Section column to Result column for all the "Yes" under Response column and for any "No", the excel should skip that Section No and provide the number for next "Yes"?
Section | Response | Result | |
1 | Yes | 1 | |
2 | Yes | 2 | |
3 | Yes | 3 | |
4 | No | 5 | |
5 | Yes | 6 | |
6 | Yes |
May 05 2019 02:55 PM
Solution@subhashgc , actually you'd like to return first column filtered by condition on second one. That's like
=FILTER(B3:B8,C3:C8="Yes")
but since above function available only for Office Insiders we may imitate it by
=IFERROR(INDEX($B$3:$B$8,AGGREGATE(15,6,1/($C$3:$C$8="Yes")*(ROW($C$3:$C$8)-ROW($C$2)),(ROW()-ROW($E$2)))),"")
in E3 above and drag it down.
Here AGGREGATE returns rows for which we meet the condition, we take next smallest and by this position INDEX returns the value from first column.
May 06 2019 10:39 AM
May 06 2019 10:40 AM
@Sergei BaklanThanks a ton!
May 06 2019 11:29 AM
@subhashgc , you are welcome
May 05 2019 02:55 PM
Solution@subhashgc , actually you'd like to return first column filtered by condition on second one. That's like
=FILTER(B3:B8,C3:C8="Yes")
but since above function available only for Office Insiders we may imitate it by
=IFERROR(INDEX($B$3:$B$8,AGGREGATE(15,6,1/($C$3:$C$8="Yes")*(ROW($C$3:$C$8)-ROW($C$2)),(ROW()-ROW($E$2)))),"")
in E3 above and drag it down.
Here AGGREGATE returns rows for which we meet the condition, we take next smallest and by this position INDEX returns the value from first column.