Trying to return only rows containing a Case sensitive value.

[jordiejones]

I have tried previous helper columns that would allow me to return rows but looking for a nice clean solution. here's what my data looks like.

Ted	DP	dp	dp	bp
Angela	dp	dp	ap	dp
Mike	dp	DP	dp	DP
Scott	PP	lp	dp	dp
Renee	dp	dp	ap	DP

<tbody>
</tbody>

I'm trying to create a new list of ONLY rows containing Capitalized "DP" regardless of how many instances

Ted	Dp	dp	dp	bp
Mike	dp	DP	dp	DP
Renee	dp	dp	ap	DP

<tbody>
</tbody>

I've tried "=EXACT(A2:A5,"DP")" but it only returns EXACT matches in the first cell.
Any help would be greatly appreciated.

 

[XOR LX]

Hi,

Assuming your original table is in A1:E5 (with headers in row 1), array formula** in G1:

=IFERROR(INDEX(A:A,SMALL(IF(MMULT(N(EXACT(B$1:E$5,"DP")),TRANSPOSE(COLUMN(B$1:E$5)^0)),ROW(B$1:E$5)),ROWS(A$1:A1))),"")

Copy down until you start to get blanks for the results.

Then, in H1:

=IF($G1="","",INDEX(B$1:B$5,MATCH($G1,$A$1:$A$5,0)))

Copy to the right as required and also down until you start to get blanks for the results.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[James006]

@ XOR LX,

Always a real pleasure to stumble upon your very creative solutions ...

Cheers

 

[XOR LX]

Cheers, James!

 

[jordiejones]

Hi,

Assuming your original table is in A1:E5 (with headers in row 1), array formula** in G1:

=IFERROR(INDEX(A:A,SMALL(IF(MMULT(N(EXACT(B$1:E$5,"DP")),TRANSPOSE(COLUMN(B$1:E$5)^0)),ROW(B$1:E$5)),ROWS(A$1:A1))),"")

Copy down until you start to get blanks for the results.

Then, in H1:

=IF($G1="","",INDEX(B$1:B$5,MATCH($G1,$A$1:$A$5,0)))

Copy to the right as required and also down until you start to get blanks for the results.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

I wound up using roughly

{=IFERROR(IF(MATCH(TRUE,EXACT(B1:E1,"DP"),0),1+MAX($F$1:F1),""),"")}

then

 =IF(ISBLANK(IFERROR(INDEX($A:A,MATCH(ROWS($1:1),$F:$F,0)),"")),"",IFERROR(INDEX($A:A,MATCH(ROWS($1:1),$AH:$AH,0)),""))

copied down and across

Its not the cleanest but it works. I'm going to try your solution to see if it works better! I'll let you know!

 

[James006]

Hello,

Besides XOR LX formulas ... you can obviously also use VBA for your extraction ... :wink: