Return 1st value of duplicates based on multiple criterias

[dellzy]

Hi Experts,
Would need to have output as shown below. I am trying to figure out the right formula to be applied in Col "Output" to return value of ColB (those match in namedrange Members) when it finds first time match based on same value in ColA and the earliest datetime in ColC. For example, for data range with same value Tix123, at the earliest datetime in datarange ColC, output should be mectr at row6 because mectr is one of the values in "Members", listed at the earliest datetime 28/12/2015 10:59. So far, my trial and error on the formula only allows me to return the first value in ColB that matches in "Members" and ignores the rest duplicates regardless the Tix number in ColA or the datetime in ColC. Appreciate if anyone can modify my formula below to suit the criterias:-

My current formula in col "Output" : =IFERROR(IF(COUNTIF($B$2:$B$79,B2)=1,"",IF(AND((COUNTIF($B$2:B2,B2)=1)=TRUE,MATCH(B2,Members,0),MATCH(A2,$A$2:$A$79,0)),B2,"")),"")

NamedRange : "Members"
Values in "Members" are Wgbiz and mectr

(expected)
ColA ColB ColC Output
Tix123 Wgbiz 7/12/2015 19:55
Tix123 hizter 2/12/2015 9:22
Tix123 Wgbiz 2/12/2015 9:22
Tix123 mectr 29/12/2015 15:53
Tix123 spectra 25/12/2015 10:57
Tix123 mectr 28/12/2015 10:59 mectr
Tix123 Wgbiz 1/12/2015 10:13 Wgbiz
Tix123 spectra 7/12/2015 10:13
Tix999 hizter 23/12/2015 15:53
Tix999 mectr 28/12/2015 18:36
Tix999 hizter 7/12/2015 10:13
Tix999 mectr 7/12/2015 10:13 mectr
Tix999 Wgbiz 23/12/2015 15:53 Wgbiz​

 

[Aladin Akyurek]

Care to post a scaled down input sample along with the expected output?

 

[Aladin Akyurek]

Care to post a scaled down input sample along with the expected output?

That (expected) in red was misleading...

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

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

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

1​

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


[tr][td]

2​

[/td][td]Tix123[/td][td]Wgbiz[/td][td]

12/8/2015 15:50​

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


[tr][td]

3​

[/td][td]Tix123[/td][td]hizter[/td][td]

12/2/2015 18:44​

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


[tr][td]

4​

[/td][td]Tix123[/td][td]Wgbiz[/td][td]

12/2/2015 18:44​

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


[tr][td]

5​

[/td][td]Tix123[/td][td]mectr[/td][td]

12/30/2015 7:46​

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


[tr][td]

6​

[/td][td]Tix123[/td][td]spectra[/td][td]

12/25/2015 21:54​

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


[tr][td]

7​

[/td][td]Tix123[/td][td]mectr[/td][td]

12/28/2015 21:58​

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


[tr][td]

8​

[/td][td]Tix123[/td][td]Wgbiz[/td][td]

12/1/2015 20:26​

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


[tr][td]

9​

[/td][td]Tix123[/td][td]spectra[/td][td]

12/7/2015 20:26​

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


[tr][td]

10​

[/td][td]Tix999[/td][td]hizter[/td][td]

12/24/2015 7:46​

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


[tr][td]

11​

[/td][td]Tix999[/td][td]mectr[/td][td]

12/29/2015 13:12​

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


[tr][td]

12​

[/td][td]Tix999[/td][td]hizter[/td][td]

12/7/2015 20:26​

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


[tr][td]

13​

[/td][td]Tix999[/td][td]mectr[/td][td]

12/7/2015 20:26​

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


[tr][td]

14​

[/td][td]Tix999[/td][td]Wgbiz[/td][td]

12/24/2015 7:46​

[/td][td]Wgbiz[/td][/tr]
[/table]

Care to confirm that the output above is correct?

 

[dellzy]

Thank you so much. for creating the table, Aladin Akyurek.It's indeed correct.

 

[Aladin Akyurek]

Thank you so much. for creating the table, Aladin Akyurek.It's indeed correct.

What about Spectra at row 9? Why is this not in your output list?

 

[dellzy]

Hi,Aladin,
Spectra is not an expected output because Spectra is not one of the values in namedrange Members

 

[Aladin Akyurek]

Hi,Aladin,
Spectra is not an expected output because Spectra is not one of the values in namedrange Members

Right. That means Members consists of just Wgbiz and mectr as you indeed state in your original post.

One more question: Why is the output Wgbiz at row 14 correct for this just occurs once in association with Tix999?

 

[dellzy]

Hi Aladin,
Wgbiz at row 14 should be correct because that's the first occurrence and considered as the earliest among all that associates with Tix999.

Hopefully the above clarifies.

Thank you.

 

[Aladin Akyurek]

Hi Aladin,
Wgbiz at row 14 should be correct because that's the first occurrence and considered as the earliest among all that associates with Tix999.

Hopefully the above clarifies.

Thank you.

{=IF($C2=MIN(IF($A$2:$A$14=$A2,IF(ISNUMBER(MATCH($B$2:$B$14,members,0)),IF($B$2:$B$14=$B2,$C$2:$C$14)))),$B2,"")}

which does not test whether the A range is empty/blank.