Find data in a column based on the first 2 characters and write the value on a new sheet

This one beat me up!

I borrowed shamelessly from many others. The re-dimensioning strategy I got from Lori who answered a near identical challenge posted there.

The UDF was not included in that challenge. It's called ConcatAll written by tigeravatar.

There is a formula in Name Manager called Tabs and this is all there is to it.

Formula:

Please Login or Register  to view this content.

I limited this to 3 tabs. Sheet4 column A is where the formula is. It must be array entered in A1 and filled down.



=TRIM(MID(SUBSTITUTE(concatall(IF((LEFT(CELL("contents",IF(1,+INDIRECT("'"&Tabs&"'!"&BASE(MODE.MULT(DECIMAL(ADDRESS(ROW($A$1:$M$10),COLUMN($A$1:$M$10),4),36),DECIMAL(ADDRESS(ROW($A$1:$M$10),COLUMN($A$1:$M$10),4),36)),36)))),2)="XX")+(LEFT(CELL("contents",IF(1,+INDIRECT("'"&Tabs&"'!"&BASE(MODE.MULT(DECIMAL(ADDRESS(ROW($A$1:$M$10),COLUMN($A$1:$M$10),4),36),DECIMAL(ADDRESS(ROW($A$1:$M$10),COLUMN($A$1:$M$10),4),36)),36)))),2)="TD"),CELL("contents",IF(1,+INDIRECT("'"&Tabs&"'!"&BASE(MODE.MULT(DECIMAL(ADDRESS(ROW($A$1:$M$10),COLUMN($A$1:$M$10),4),36),DECIMAL(ADDRESS(ROW($A$1:$M$10),COLUMN($A$1:$M$10),4),36)),36)))),""),";"),";",REPT(" ",256)),(ROWS($1:1)-1)*256+1,256))


If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

A follow up. This one does not require VBA. It uses the same set-up and re-dimensioning strategy as before.

It has a helper column in A that assigns index numbers to the qualifiers. The formula must be array entered.

Formula:

Please Login or Register  to view this content.

The output formula in column B must be array entered also.

Formula:

Please Login or Register  to view this content.