Formula: Need to select a duplicate occurrence based upon other row values

D

dbiss

New Member
Joined
Sep 12, 2017
Messages
3
I need a formula to identify or select a single occurrence of a duplicate value based upon values in other columns of each row. For instance, in the table below, I would want to select the instance in A4- where none of the other row values were blank. Any advice is appreciated.

[TABLE="width: 750"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]COLOR[/TD]
[TD="align: center"]TEMP[/TD]
[TD="align: center"]StartDate[/TD]
[TD="align: center"]EndDate[/TD]
[TD="align: center"]Y/N[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"]green[/TD]
[TD="align: center"]hot[/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"]green[/TD]
[TD="align: center"]hot[/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"]1/31/2017[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"]hot[/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"]hot[/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"]green[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"]1/31/2017[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]123ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"]hot[/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
but row 4 is not a duplicate value as there are no identical rows with non blanks - please clarify
 
Upvote 0
oldbrewer said:
but row 4 is not a duplicate value as there are no identical rows with non blanks - please clarify
Click to expand...

Please consider "ID" (column A) the key value, which is the same in every row shown. I need to be able to select the instance of the key value based upon the values in the other columns; in this case wherein all of the other columns in that row have a value. Thanks.
 
Upvote 0
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 12/09/2017 by bob
'


'
rrow = 39
For k = 1 To 2
Match = Cells(k + 32, 1)
For j = 2 To 26
If Cells(j, 1) = Match Then GoTo 20 Else GoTo 50
20 If Cells(j, 2) <> "" And Cells(j, 3) <> "" And Cells(j, 4) <> "" Then GoTo 40 Else GoTo 50
40 If Cells(j, 5) <> "" And Cells(j, 6) <> "" Then GoTo 45 Else GoTo 50
45 rrow = rrow + 1
For z = 1 To 7
Cells(rrow, z) = Cells(j, z)
Next z
50 Next j
Next k
End Sub
 
Upvote 0
[TABLE="width: 1240"]
<colgroup><col span="3"><col><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]COLOR[/TD]
[TD]TEMP[/TD]
[TD]StartDate[/TD]
[TD]EndDate[/TD]
[TD]Y/N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]green1[/TD]
[TD]hot[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]red2[/TD]
[TD]hot[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]this macro produced the lower table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]green3[/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sub Macro4()[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]' Macro4 Macro[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD]green4[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]' Macro recorded 12/09/2017 by bob[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD]green5[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] rrow = 39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD]green6[/TD]
[TD][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For k = 1 To 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Match = Cells(k + 32, 1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For j = 2 To 26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD]green7[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] If Cells(j, 1) = Match Then GoTo 20 Else GoTo 50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20 If Cells(j, 2) <> "" And Cells(j, 3) <> "" And Cells(j, 4) <> "" Then GoTo 40 Else GoTo 50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]green8[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40 If Cells(j, 5) <> "" And Cells(j, 6) <> "" Then GoTo 45 Else GoTo 50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45 rrow = rrow + 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For z = 1 To 7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]green9[/TD]
[TD][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Cells(rrow, z) = Cells(j, z)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]red1[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50 Next j[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]row 26[/TD]
[TD][/TD]
[TD]Next k[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]red2[/TD]
[TD]hot[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]green8[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC[/TD]
[TD]red1[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD]green4[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF[/TD]
[TD]green5[/TD]
[TD]hot[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
oldbrewer said:
[TABLE="width: 1240"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]COLOR
[/TD]
[TD]TEMP
[/TD]
[TD]StartDate
[/TD]
[TD]EndDate
[/TD]
[TD]Y/N
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]green1
[/TD]
[TD]hot
[/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]red2
[/TD]
[TD]hot
[/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]this macro produced the lower table
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]green3
[/TD]
[TD][/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sub Macro4()
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]' Macro4 Macro
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD]green4
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]' Macro recorded 12/09/2017 by bob
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD]green5
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] rrow = 39
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD]green6
[/TD]
[TD][/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For k = 1 To 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Match = Cells(k + 32, 1)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For j = 2 To 26
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD]green7
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] If Cells(j, 1) = Match Then GoTo 20 Else GoTo 50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20 If Cells(j, 2) <> "" And Cells(j, 3) <> "" And Cells(j, 4) <> "" Then GoTo 40 Else GoTo 50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]green8
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40 If Cells(j, 5) <> "" And Cells(j, 6) <> "" Then GoTo 45 Else GoTo 50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45 rrow = rrow + 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For z = 1 To 7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]green9
[/TD]
[TD][/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Cells(rrow, z) = Cells(j, z)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next z
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]red1
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50 Next j
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD]row 26
[/TD]
[TD][/TD]
[TD]Next k
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col F
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 33
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]red2
[/TD]
[TD]hot
[/TD]
[TD="align: right"]01/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]green8
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123ABC
[/TD]
[TD]red1
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD]green4
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456DEF
[/TD]
[TD]green5
[/TD]
[TD]hot
[/TD]
[TD="align: right"]08/01/2017
[/TD]
[TD="align: right"]31/01/2017
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thanks oldbrewer for the reply and suggestion. Unfortunately, though, I need a formula to do this rather than a macro/VB.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
SUMIFS while ignoring duplicate rows based on values from a column
Replies
4
Views
275
slam
S
R
  • Solved
Find Duplicates and change font color based on other cell value
Replies
3
Views
305
etaf
etaf
D
  • Solved
Formula to aggregate emails if values in other cells match certain text
Replies
3
Views
505
Marcelo Branco
M
M
  • Question Question
Trouble with a formula returning duplicate values
2
Replies
19
Views
964
Cubist
Cubist
T
  • Question Question
Extending a SUM range, based upon a cell value
Replies
5
Views
631
Cubist
Cubist

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top