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

25-Nov-17	11:02:47	17-3290056	EMS Response	140	MM	137
25-Nov-17	11:02:47	17-3290056	EMS Response	140	OC	137
25-Nov-17	10:09:29	17-3290049	EMS Response	140	QC	182
25-Nov-17	10:09:29	17-3290049	EMS Response	140	MM	204
25-Nov-17	10:09:29	17-3290049	EMS Response	140	OC	204
25-Nov-17	06:22:56	17-3290029	EMS Response	140	MM	157
25-Nov-17	06:22:56	17-3290029	EMS Response	140	OC	157
25-Nov-17	06:22:56	17-3290029	EMS Response	140	QC	178
25-Nov-17	01:41:46	17-3290010	EMS Response	140	QC	153
25-Nov-17	01:41:46	17-3290010	EMS Response	140	MM	164
25-Nov-17	01:41:46	17-3290010	EMS Response	140	OC	164
24-Nov-17	23:21:52	17-3280185	EMS Response	140	QC	153
24-Nov-17	23:21:52	17-3280185	EMS Response	140	MM	164
24-Nov-17	23:21:52	17-3280185	EMS Response	140	OC	164

<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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