Complex Formula

[asad]

Hello All,

I have data that is similar to the one below:
[TABLE="width: 225"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Place[/TD]
[TD]GEE[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]Depot[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]ABC[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]GEE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]SGEE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]GEESTN[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]DRG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]Depot[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]ABC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]FED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]SGTE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]GEST[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]DRG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]Depot[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]ABC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]GEESTN[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]DRG[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


I want to get name from column A in column C if any place in column B contains full text in C1 ("GEE"). The answer should be 50 and 53. Each name must come only once in the answer. The data is about 50000 rows and there are about 120 different names. The places are about 200 different places.

Any help would be greatly appreciated.

Asad

 

[asad]

Got it: If someone can give me a better and easier formula, that would be great. One I have is:

=INDEX($A$1:$A$25000,SMALL(IF((ISNUMBER(SEARCH($J$1,$D$1:$D$25000)))*(ISNA(MATCH($A$1:$A$25000,$J$1:$J1,0))),ROW($A$1:$A$25000)),1))

 

[Aladin Akyurek]

Also...

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

A​

[/td][td]

D​

[/td][td]

J​

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

1​

[/td][td] Name[/td][td] Place[/td][td]

2​

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

2​

[/td][td] 50[/td][td] Depot[/td][td] GEE[/td][/tr]
[tr][td]

3​

[/td][td] 50[/td][td] ABC[/td][td]

50​

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

4​

[/td][td] 50[/td][td] GEE[/td][td]

53​

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

5​

[/td][td] 50[/td][td] SGEE[/td][td][/td][/tr]
[tr][td]

6​

[/td][td] 50[/td][td] GEESTN[/td][td][/td][/tr]
[tr][td]

7​

[/td][td] 50[/td][td] DRG[/td][td][/td][/tr]
[tr][td]

8​

[/td][td] 51[/td][td] Depot[/td][td][/td][/tr]
[tr][td]

9​

[/td][td] 51[/td][td] ABC[/td][td][/td][/tr]
[tr][td]

10​

[/td][td] 51[/td][td] FED[/td][td][/td][/tr]
[tr][td]

11​

[/td][td] 51[/td][td] SGTE[/td][td][/td][/tr]
[tr][td]

12​

[/td][td] 51[/td][td] GEST[/td][td][/td][/tr]
[tr][td]

13​

[/td][td] 51[/td][td] DRG[/td][td][/td][/tr]
[tr][td]

14​

[/td][td] 53[/td][td] Depot[/td][td][/td][/tr]
[tr][td]

15​

[/td][td] 53[/td][td] ABC[/td][td][/td][/tr]
[tr][td]

16​

[/td][td] 53[/td][td] GEESTN[/td][td][/td][/tr]
[tr][td]

17​

[/td][td] 53[/td][td] DRG[/td][td][/td][/tr]
[/table]

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

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(J$2,$D$2:$D$17)),$A$2:$A$17),$A$2:$A$17),1))

J2 houses a search value of interest.

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

=IF(ROWS($J$3:J3)>J$1,"",MIN(IF(ISNUMBER(SEARCH(J$2,$D$2:$D$17)),IF(ISNA(MATCH($A$2:$A$17,J$2:J2,0)),$A$2:$A$17))))

 

[asad]

This is much better Sir Aladin. I like it.
Thanks again for your help.

Asad

 

[Aladin Akyurek]

This is much better Sir Aladin. I like it.
Thanks again for your help.

Asad

You are welcome.