Excel : Search in column based on patterns/substrings-CodePudding

In my excel sheet (Excel-365), I have 3 columns i.e. Table_Name, Size_MB and Session_Num as shown below. I am trying to list out all those Table_Name (Column A) along with their Size_MB (Column B) in two separate columns where Table_Name consists of Session_Num (Column C) at the end of the name preceded by an underscore ( _ ) for example BAL1_98460501.

Note: There are around 116k records in Table_Name and 80k records in Session_Num and I am using Excel-365.

Table_Name                     Size_MB  Session_Num
TDATASEG                       110135   98460501
SNP_SESS_TASK                  15346    240089501
BIN$yb73fGvTAPbgU4IdCBcZFA==$0   875    68243501
BAL1_15882501                    248    112275501
BAL1_98460501                    176    635501
LOG_216464501                    114    28794501
EXCH_240089501                 0.375    225655501
EXCH_RATE_252682501            0.375    216464501
LOG_68243501                    0.75    467687453
CUR_DEC_112275501             0.0625    225659501
BAL_200895501                 0.5625    252682501
......................      .........   ...............
….....................      .........   ...............

Expected output:

Table_Name           Size_MB
BAL1_98460501            176
LOG_216464501            114
EXCH_240089501         0.375
EXCH_RATE_252682501    0.375
LOG_68243501            0.75
CUR_DEC_112275501     0.0625
…................    .........
…................    .........

Can you please give me a hint how can we achieve this using excel formula? Thanks.

CodePudding user response：

You could try:

Formula in E2:

=FILTER(A2:B12,COUNTIF(C2:C12,TRIM(RIGHT(SUBSTITUTE(A2:A12,"_",REPT(" ",LEN(A2:A12))),LEN(A2:A12)))),"")

CodePudding user response：

If you want to return the table names that have a number after the last underscore that appear in column C, and you are using Office 365 you could try this formula.

=FILTER(A2:B12,COUNTIF(C2:C12,FILTERXML("<x><y>"&SUBSTITUTE(A2:A12,"_","</y><y>")&"</y></x>","//y[last()]")))

CodePudding user response：

If you have Excel-365 then could try below formula.

=FILTER(A2:B6,ISNUMBER(SEARCH("_",A2:A6)))

Above formula will fiter rows having underscore _ in table name field.