Extract Rows/Cells from Master Table to Sorted Tables based on Suffix of Text.

[hro5e]

Hello fellow excellors,

I have a number of assets that contain very specific suffixes, for example:

XRD05_CV
XRD06_CV
XRD07_CV
XRD08_CV_I
XRD08_HB
XRD08_WS
XRD09_CV

These assets are listed in a master table. as you can see, there are different suffixes such as _CV, _CV_I, _HB and _WS.

Now I need to separate these assets into designated tables (without blank rows) depending on the suffix giving these results:

Sorted Table 1:
XRD05_CV
XRD06_CV
XRD07_CV
XRD08_CV_I
XRD09_CV


Sorted Table 2:
XRD08_HB

Sorted Table 3:
XRD08_WS

As you can see sorted table one has two types of suffix, _CV and _CV_I. so I would need a formula that can accommodate more than one suffix into one specific table.

If you require any further information, please ask.

 

[hro5e]

Ok,

So I believe I have figured it out. Please correct me if wrong, or if you have a better solution.

Here it goes:

So I created a column that will give each possible suffix a number with this formula.

=IF(COUNTIF(F13,"*_CV"),1,IF(COUNTIF(F13,"*_CV_I"),1,IF(COUNTIF(F13,"*_PS"),2,IF(COUNTIF(F13,"*VM"),3,IF(COUNTIF(F13,"*_VS"),3,IF(COUNTIF(F13,"*_AS"),4,IF(COUNTIF(F13,"*_HB"),5,IF(COUNTIF(F13,"*_PB"),6,0))))))))

I then used this formula for the extracting of the data into the relevant table: for example, the table to extract all _CV suffix:

=IFERROR(INDEX($F:$F,SMALL(IF($H$13:H$62=1,ROW($F$13:$F$62)),ROWS($F$13:$F13))),"")