SOLVED
Home

Excel Formula to skip rows for a specific text

%3CLINGO-SUB%20id%3D%22lingo-sub-532682%22%20slang%3D%22en-US%22%3EExcel%20Formula%20to%20skip%20rows%20for%20a%20specific%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532682%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20get%20values%20from%20Section%20column%20to%20Result%20column%20for%20all%20the%20%22Yes%22%20under%20Response%20column%20and%20for%20any%20%22No%22%2C%20the%20excel%20should%20skip%20that%20Section%20No%20and%20provide%20the%20number%20for%20next%20%22Yes%22%3F%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3ESection%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EResponse%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EResult%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-532682%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-532808%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20to%20skip%20rows%20for%20a%20specific%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334010%22%20target%3D%22_blank%22%3E%40subhashgc%3C%2FA%3E%26nbsp%3B%2C%20actually%20you'd%20like%20to%20return%20first%20column%20filtered%20by%20condition%20on%20second%20one.%20That's%20like%3C%2FP%3E%0A%3CPRE%3E%3DFILTER(B3%3AB8%2CC3%3AC8%3D%22Yes%22)%3C%2FPRE%3E%0A%3CP%3Ebut%20since%20above%20function%20available%20only%20for%20Office%20Insiders%20we%20may%20imitate%20it%20by%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24B%243%3A%24B%248%2CAGGREGATE(15%2C6%2C1%2F(%24C%243%3A%24C%248%3D%22Yes%22)*(ROW(%24C%243%3A%24C%248)-ROW(%24C%242))%2C(ROW()-ROW(%24E%242))))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20263px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112191i83660E1428C1B6C7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20E3%20above%20and%20drag%20it%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20AGGREGATE%20returns%20rows%20for%20which%20we%20meet%20the%20condition%2C%20we%20take%20next%20smallest%20and%20by%20this%20position%20INDEX%20returns%20the%20value%20from%20first%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-535730%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20to%20skip%20rows%20for%20a%20specific%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-535730%22%20slang%3D%22en-US%22%3EThis%20helps!%20Thanks%20a%20ton....%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-535738%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20to%20skip%20rows%20for%20a%20specific%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-535738%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThanks%20a%20ton!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-536048%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20to%20skip%20rows%20for%20a%20specific%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334010%22%20target%3D%22_blank%22%3E%40subhashgc%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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"?

SectionResponse Result
1Yes 1
2Yes 2
3Yes 3
4No 5
5Yes 6
6Yes  
4 Replies
Highlighted
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)))),"")

image.png

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.

Highlighted
This helps! Thanks a ton....
Highlighted
Highlighted

@subhashgc , you are welcome

Related Conversations
Fixieren in Exel
nicoalex12 in Excel on
0 Replies
Combine/Rearrange Rows and Columns
Rob Nunley in Excel on
0 Replies
Excel: Condensing and Counting
MADavis in Excel on
0 Replies
VBA code to reset togglebutton colors.
dick_hollister in Excel on
0 Replies
Horizontal Scrolling
stephen607 in Excel on
0 Replies
How to in Excel
HarryNetherlands in Excel on
1 Replies