Extract row for specific cells with Criterias based on count of word

1. Extract row for specific cells with Criterias based on count of word

Hello Friends

In the Column B for the `ABC dmkijtfsh' and if the word `Faco' is there then need to extract the row for specific cells (Column H, K, AA & AT) based on count of word `Fako'.

In attached excel refer the required result in Sheet 2 Light yellow (Fako count 1), light red (Fako count 2) and light green cells (Fako count >2), Sheet 3 shows the after filtered view for better understanding.

Reference Forum :
https://www.excelforum.com/excel-for...ml#post5166502

Sekar

2. Re: Extract row for specific cells with Criterias based on count of word

This proposal employs a helper column (AW) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =IF(B4="ABC dmkijtfsh",(LEN(H4)-LEN(SUBSTITUTE(H4,AW\$3,"")))/LEN(AW\$3),0)
The tables on Sheet 2 are populated using formulas similar to: =IFERROR(INDEX(Sheet1!A\$4:A\$87,AGGREGATE(15,6,(ROW(\$A\$4:\$A\$87)-ROW(\$A\$3))/(Sheet1!\$AW\$4:\$AW\$87=1),ROWS(\$A\$1:\$A1))),"")
Let us know if you have any questions.

3. Re: Extract row for specific cells with Criterias based on count of word

Hello JeteMx

Thanks for your formula, Please find the attached sheet, i got the formulas without any helpher.

Thanks again.

Sekar

4. Re: Extract row for specific cells with Criterias based on count of word

Glad that you got a solution with which you are satisfied. Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1