Pulling Multiple Criteria From Data

[klis]

Hi All,

I have a long list of string entries (55,000+) on one worksheet and in another worksheet I would like to pull multiple ‘different and specific’ entry types.

A small extract from the list looks like this:

Please Login or Register  to view this content.

The strings I need to pull *MUST contain SJ (11,2) [Group]

*Needs to be equal to either SKJT, SBJT, SQJT (4,4) [Project] – These codes are unique and define the string.

*Can only contain SSA, SPD, SPA, SKE (14,3) [Type]

In the above example, the results would show the following:

Please Login or Register  to view this content.

In another worksheet I am currently pulling all entries associated with [Project] codes that are defined by “S” – Or like above but more detailed “SQ, SB, SK” etc

The equation I am using is:

Please Login or Register  to view this content.

Where G = the list of strings in the main worksheet

This formula works perfectly, however, when I try to add more than one or multiple searching criteria using <> it all goes a bit haywire

Does anyone have any thoughts, input or suggestions?

This is probably really simple, but I am an Excel noob after all :D

Will add [Solved] once complete. Thanks in advance.

[NBVC]

Assuming database is in Sheet1, column A, starting at A2, then in B2 add a helper formula to find matches:

=IF(AND(MID(A2,11,2)="SJ",OR(MID(A2,4,4)={"SKJT","SBJT","SQJT"}),OR(MID(A2,14,3)={"SSA","SPD","SPA","SKE"})),COUNT(B$1:B1)+1,"")

copied down.

Then in Sheet2, A2:

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($A$2:$A2),Sheet1!$B:$B,0)),"")

copied down.

adjust ranges and sheetnames to suit.

[klis]

NBVC, thank you very much for your quick and informed response

I started a new excel to test your formula, so didn't need to change the sheet names. I followed it through exactly as you outlined, however, I am receiving the #NAME? error. Can you hazard a guess as to why please

[NBVC]

I see you are using 2003... IFERROR Doesn't work there.

Try ammending the formula to:

=IF(ISNUMBER(MATCH(ROWS($A$2:$A2),Sheet1!$B:$B,0)),INDEX(Sheet1!A:A,MATCH(ROWS($A$2:$A2),Sheet1!$B:$B,0)),"")