Need assistance friends...

[Sheshank Pydikondala]

Column1 Column2 Column3
XXXINDIA[IN][COMP][1][111111][1]:IN:GR-ISAN:12345 | 1st Row 1st Col details | 1st Row 2nd Col details
XXXINDIA[IN][COMP][12][123456]:IN:LR-ISAN:2345 | 2nd Row 1st Col details | 2nd Row 2nd Col details
XXXINDIA[IN][COMP][12]12345]:IN:LR-ISAN:4567890 | 3rd Row 1st Col details | 3rd Row 2nd Col details
XXXINDIA[IN][COMP][12][345678]:IN:LR-ISAN:5678 | 4th Row 1st Col details | 4th Row 2nd Col details
XXXINDIA[IN][COMP][12][123456]:IN:LR-ISAN:6789 | 5th Row 1st Col details | 5th Row 2nd Col details
XXXINDIA[IN][COMP][12][123456]:IN:LR-ISAN:78 | 6th Row 1st Col details | 6th Row 2nd Col details
XXXINDIA[IN][COMP][123][123456]:IN:LR-ISAN:11584 | 7th Row 1st Col details | 7th Row 2nd Col details
XXXINDIA[IN][COMP][1][123456][12]:IN:GR-ISAN:9874 | 8th Row 1st Col details | 8th Row 2nd Col details
XXXINDIA[IN][COMP][123][12345][123]:IN:GR-ISAN:963 | 9th Row 1st Col details | 9th Row 2nd Col details
XXXINDIA[IN][COMP][1][123456][1]:IN:GR-ISAN:259864785 | 10th Row 1st Col details | 10th Row 2nd Col details
XXXINDIA[IN][COMP][12][123456]:IN:GR-ISAN:159487263 | 11th Row 1st Col details | 11th Row 2nd Col details


Now what my requirement is...


In the above mentioned rows, the "XXXINDIA[IN][COMP]" is common for every row.

The string consists of 3 instances of values in "[]" square brackets.

Ex: XXXINDIA[IN][COMP][1][111111][1]:IN:GR-ISAN:12345

The 1st square bracket can contain number between 1 and 999 (max 3 digits)

The 2nd square bracket can contain number upto max 6 digits

The 3rd square bracket can contain number between 1 and 999 (max 3 digits)

My requirement is : If the complete value in the cell has a 3rd square bracket with a number between 1 and 999 (in the same order as mentioned above), then I need the complete row of details i.e to be vlookup

In the above example, the 1st, 8th, 9th and 10th rows are the strings which needs to be filtered because it consists of 3 square brackets with a number





Final solution should be like this
XXXINDIA[IN][COMP][1][111111][1]:IN:GR-ISAN:12345 1st Row 1st Col details 1st Row 2nd Col details
XXXINDIA[IN][COMP][1][123456][12]:IN:GR-ISAN:9874 8th Row 1st Col details 8th Row 2nd Col details
XXXINDIA[IN][COMP][123][12345][123]:IN:GR-ISAN:963 9th Row 1st Col details 9th Row 2nd Col details
XXXINDIA[IN][COMP][1][123456][1]:IN:GR-ISAN:259864785 10th Row 1st Col details 10th Row 2nd Col details




Kindly assist in this regards... Thank you.

 

[r2c2]

@Sheshank Pydikondala Interesting question.

Assuming your data starts in A2, you can figure out whether to filter a row or not using below formula.

=IFERROR(REPT("Yes",SEARCH("*[IN][COMP][*][*][*]",A2)),"No")

Once you do this for all rows, just filter away anything with "No" and you will get the result. You can also extract the "Yes" rows using another set of INDEX, MATCH formula, but unless you have a lot of data and do this everyday, it may not be worth the effort.

Please note that this approach assumes, third []s will always contain a number. For ex. if your data has a row like this, it will be flagged as "Yes" for filtering.

XXXINDIA[IN][COMP][1][123456][ABCD]:IN:GR-ISAN:259864785 10th Row 1st Col details 10th Row 2nd Col detail

 

[Sheshank Pydikondala]

Thank you r2c2. It helps...