Formula Help

[How_Do_I]

How would I go about this please. I want to count and extract the items that are not listed in my Jeanie. So count the items that do not contain AA11-AA14 and extract those items please.

Excel Workbook
	AA	AB
11	Mounted Troops	4
12	Artillery	12
13	Guards	0
14	Field Ambulances	3
15	Not the Above
Sheet1

 

[Aladin Akyurek]

Would you post an input exhibit with a corresponding output exhibit without formulas, followed by a conscise description in words?

 

[How_Do_I]

Hello Aladin, what I'm after please are the Infantry Brigades that make up the 1st Division. This is a poor example because I could search "guards" and get the result but the search of "guards" would only work in some of the remaining Divisions in my workbook. So in the Jeanie, the Infantry Brigades are the ones NOT with the words "mounted" or "artillery" in the Brigade Range. The returns I seek are K2:K5, the row numbers of the 1st instance. I hope that clears it up.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1			1		Infantry	Mounted	Artillery		Returns	Range Row
2	Division	Brigade	1st Division		0	2	2		1st Guards Brigade	1
3	1st Division	1st Guards Brigade							2nd Guards Brigade	3
4	1st Division	1st Guards Brigade							3rd Guards Brigade	5
5	1st Division	2nd Guards Brigade
6	1st Division	2nd Guards Brigade
7	1st Division	3rd Guards Brigade
8	1st Division	3rd Guards Brigade
9	1st Division	Mounted Troops
10	1st Division	Mounted Troops
11	1st Division	XXV Artillery Brigade
12	1st Division	XXVI Artillery Brigade
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Aladin Akyurek]

Create unique brigades

where the conditions are:

1st Division

& not

infantry
mounted
artillery

in the 'brigade' description.

 

[How_Do_I]

Create unique brigades

where the conditions are:

1st Division

& not

mounted
artillery

in the 'brigade' description.

I need to specify want NOT to extract as opposed to what to extract.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td]

​

[/td][td][/td][td]

​

[/td][td][/td][td]distinct brigade list[/td][td]Range Row[/td][/tr]
[tr][td]

2​

[/td][td]Division[/td][td]Brigade[/td][td]1st Division[/td][td]

Mounted​

[/td][td]

Artillery​

[/td][td]

3​

[/td][td]1st Guards Brigade[/td][td]

1​

[/td][/tr]
[tr][td]

3​

[/td][td]1st Division[/td][td]1st Guards Brigade[/td][td][/td][td][/td][td][/td][td][/td][td]2nd Guards Brigade[/td][td]

3​

[/td][/tr]
[tr][td]

4​

[/td][td]1st Division[/td][td]1st Guards Brigade[/td][td][/td][td][/td][td][/td][td][/td][td]3rd Guards Brigade[/td][td]

5​

[/td][/tr]
[tr][td]

5​

[/td][td]1st Division[/td][td]2nd Guards Brigade[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]1st Division[/td][td]2nd Guards Brigade[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]1st Division[/td][td]3rd Guards Brigade[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]1st Division[/td][td]3rd Guards Brigade[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]1st Division[/td][td]Mounted Troops[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]1st Division[/td][td]Mounted Troops[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]1st Division[/td][td]XXV Artillery Brigade[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]1st Division[/td][td]XXVI Artillery Brigade[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$3:$B$12=""),IF($A$3:$A$12=$C$2,IF(1-ISNUMBER(SEARCH(" "&$D$2&" "," "&$B$3:$B$12&" ")),IF(1-ISNUMBER(SEARCH(" "&$E$2&" "," "&$B$3:$B$12&" ")),MATCH("~"&$B$3:$B$12,$B$3:$B$12&"",0))))),ROW($B$3:$B$12)-ROW($B$3)+1),1))

In I2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($I$2:I2)>$F$2,"",INDEX($B$3:$B$12,SMALL(IF(FREQUENCY(IF(1-($B$3:$B$12=""),IF($A$3:$A$12=$C$2,IF(1-ISNUMBER(SEARCH(" "&$D$2&" "," "&$B$3:$B$12&" ")),IF(1-ISNUMBER(SEARCH(" "&$E$2&" "," "&$B$3:$B$12&" ")),MATCH("~"&$B$3:$B$12,$B$3:$B$12&"",0))))),ROW($B$3:$B$12)-ROW($B$3)+1),ROW($B$3:$B$12)-ROW($B$3)+1),ROWS($I$2:I2))))

In J2 just enter and copy down:

=IF($I2="","",MATCH($I2,$B$3:$B$12,0))

 

[How_Do_I]

Absolutely brilliant Aladin, placed in my workbook and it is working, thank you so much for that.

I don't understand the IF(1-ISNUMBER though... I assume it is the same as <>?

Thanks again.

 

[Aladin Akyurek]

Absolutely brilliant Aladin, placed in my workbook and it is working, thank you so much for that.

You are welcome.

I don't understand the IF(1-ISNUMBER though... I assume it is the same as <>?

Thanks again.

Yes, it is depending how it's used:

1-(range="") is the same as range<>""

1-ISNUMBER(MATCH(...)) is the same as ISNA(MATCH(...))

1-ISNUMBER(SEARCH(...)) is the same as e.g., NOT(ISNUMBER(SEARCH(...))), etc.