Find Duplicate Rows, Match a value in a column, then return a third column value

[dxsley]

Ok... So I have seven columns... I want to find the duplicate rows (c column) and then find the rows that don't have a value of QC in column F, then return the number in column G of the duplicate rows that don't have a value of QC in column F but are duplicate values in column C.

ANY HELP IS APPRECIATED

A B C D D F G
[TABLE="width: 569"]
<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]25-Nov-17[/TD]
[TD]11:02:47[/TD]
[TD]17-3290056[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]MM[/TD]
[TD]137[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]11:02:47[/TD]
[TD]17-3290056[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]OC[/TD]
[TD]137[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]10:09:29[/TD]
[TD]17-3290049[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]QC[/TD]
[TD]182[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]10:09:29[/TD]
[TD]17-3290049[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]MM[/TD]
[TD]204[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]10:09:29[/TD]
[TD]17-3290049[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]OC[/TD]
[TD]204[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]06:22:56[/TD]
[TD]17-3290029[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]MM[/TD]
[TD]157[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]06:22:56[/TD]
[TD]17-3290029[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]OC[/TD]
[TD]157[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]06:22:56[/TD]
[TD]17-3290029[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]QC[/TD]
[TD]178[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]01:41:46[/TD]
[TD]17-3290010[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]QC[/TD]
[TD]153[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]01:41:46[/TD]
[TD]17-3290010[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]MM[/TD]
[TD]164[/TD]
[/TR]
[TR]
[TD]25-Nov-17[/TD]
[TD]01:41:46[/TD]
[TD]17-3290010[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]OC[/TD]
[TD]164[/TD]
[/TR]
[TR]
[TD]24-Nov-17[/TD]
[TD]23:21:52[/TD]
[TD]17-3280185[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]QC[/TD]
[TD]153[/TD]
[/TR]
[TR]
[TD]24-Nov-17[/TD]
[TD]23:21:52[/TD]
[TD]17-3280185[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]MM[/TD]
[TD]164[/TD]
[/TR]
[TR]
[TD]24-Nov-17[/TD]
[TD]23:21:52[/TD]
[TD]17-3280185[/TD]
[TD]EMS Response[/TD]
[TD]140[/TD]
[TD]OC[/TD]
[TD]164[/TD]
[/TR]
</tbody>[/TABLE]

 

[dxsley]

Let me update what I'm looking for, I don't think I explained it correctly. Basically, I'm looking to return the value of column G with the MM value in column F within the duplicate rows based on the value (duplicate) in column C. However, I only was to return the G value when any one of the duplicate rows do not contain a QC value in column F. So, in short. I'm looking for duplicate rows where in column F there is only either a MM or OC and then return the value in column G of the duplicate row containing the value MM in column F. I'm believing this is nearly impossible in excel and would need coding. If anyone can figure this out, I'll bow to your expertise. Thanks in adavance.

 

[MickG]

Perhaps this:-
Actual data assumed to start "A2".

[COLOR="Navy"]Sub[/COLOR] MG09Dec18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, 3).Value = "QC" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Dn.Offset(, 3) = "MM" [COLOR="Navy"]Then[/COLOR] Rw = Dn.Row
            .Add Dn.Value, Array(Dn, Rw)
        [COLOR="Navy"]Else[/COLOR]
           Q = .Item(Dn.Value)
            [COLOR="Navy"]If[/COLOR] Dn.Offset(, 3) = "MM" [COLOR="Navy"]Then[/COLOR] Q(1) = Dn.Row
            [COLOR="Navy"]Set[/COLOR] Q(0) = Union(Q(0), Dn)
            [COLOR="Navy"]If[/COLOR] Q(1) > 0 [COLOR="Navy"]Then[/COLOR] Q(0).Offset(, 4).Value = Q(1)
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick