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

[dbiss]

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.

	A	B	C	D	E	F
1	ID	COLOR	TEMP	StartDate	EndDate	Y/N
2	123ABC	green	hot	1/1/2017		Y
3	123ABC			1/1/2017		Y
4	123ABC	green	hot	1/1/2017	1/31/2017	Y
5	123ABC		hot	1/1/2017
6	123ABC		hot	1/1/2017		Y
7	123ABC	green		1/1/2017	1/31/2017
8	123ABC			1/1/2017		Y
9	123ABC		hot	1/1/2017		Y

<tbody>
</tbody>

 

[oldbrewer]

but row 4 is not a duplicate value as there are no identical rows with non blanks - please clarify

 

[dbiss]

but row 4 is not a duplicate value as there are no identical rows with non blanks - please clarify

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.

 

[oldbrewer]

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

 

[oldbrewer]

ID	COLOR	TEMP	StartDate	EndDate	Y/N
123ABC	green1	hot	01/01/2017		Y
123ABC			01/01/2017		Y
123ABC	red2	hot	01/01/2017	31/01/2017	Y
123ABC		hot	01/01/2017						this macro produced the lower table
123ABC		hot	01/01/2017		Y
123ABC	green3		01/01/2017	31/01/2017					Sub Macro4()
123ABC			01/01/2017		Y				'
123ABC		hot	01/01/2017		Y				' Macro4 Macro
456DEF	green4	hot	08/01/2017	31/01/2017	Y				' Macro recorded 12/09/2017 by bob
456DEF			08/01/2017		Y				'
456DEF	green5	hot	08/01/2017	31/01/2017	Y
456DEF		hot	08/01/2017						'
456DEF		hot	08/01/2017		Y				rrow = 39
456DEF	green6		08/01/2017	31/01/2017					For k = 1 To 2
456DEF			08/01/2017		Y				Match = Cells(k + 32, 1)
456DEF		hot	08/01/2017		Y				For j = 2 To 26
456DEF	green7	hot	08/01/2017		Y				If Cells(j, 1) = Match Then GoTo 20 Else GoTo 50
456DEF			08/01/2017		Y				20 If Cells(j, 2) <> "" And Cells(j, 3) <> "" And Cells(j, 4) <> "" Then GoTo 40 Else GoTo 50
123ABC	green8	hot	08/01/2017	31/01/2017	Y				40 If Cells(j, 5) <> "" And Cells(j, 6) <> "" Then GoTo 45 Else GoTo 50
123ABC		hot	08/01/2017						45 rrow = rrow + 1
123ABC		hot	08/01/2017		Y				For z = 1 To 7
123ABC	green9		08/01/2017	31/01/2017					Cells(rrow, z) = Cells(j, z)
123ABC			08/01/2017		Y				Next z
123ABC	red1	hot	08/01/2017	31/01/2017	Y				50 Next j
123ABC			08/01/2017		Y		row 26		Next k
									End Sub
					col F
123ABC							row 33
456DEF
123ABC	red2	hot	01/01/2017	31/01/2017	Y
123ABC	green8	hot	08/01/2017	31/01/2017	Y
123ABC	red1	hot	08/01/2017	31/01/2017	Y
456DEF	green4	hot	08/01/2017	31/01/2017	Y
456DEF	green5	hot	08/01/2017	31/01/2017	Y

<colgroup><col span="3"><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>

 

[dbiss]

ID	COLOR	TEMP	StartDate	EndDate	Y/N
123ABC	green1	hot	01/01/2017		Y
123ABC			01/01/2017		Y
123ABC	red2	hot	01/01/2017	31/01/2017	Y
123ABC		hot	01/01/2017						this macro produced the lower table
123ABC		hot	01/01/2017		Y
123ABC	green3		01/01/2017	31/01/2017					Sub Macro4()
123ABC			01/01/2017		Y				'
123ABC		hot	01/01/2017		Y				' Macro4 Macro
456DEF	green4	hot	08/01/2017	31/01/2017	Y				' Macro recorded 12/09/2017 by bob
456DEF			08/01/2017		Y				'
456DEF	green5	hot	08/01/2017	31/01/2017	Y
456DEF		hot	08/01/2017						'
456DEF		hot	08/01/2017		Y				rrow = 39
456DEF	green6		08/01/2017	31/01/2017					For k = 1 To 2
456DEF			08/01/2017		Y				Match = Cells(k + 32, 1)
456DEF		hot	08/01/2017		Y				For j = 2 To 26
456DEF	green7	hot	08/01/2017		Y				If Cells(j, 1) = Match Then GoTo 20 Else GoTo 50
456DEF			08/01/2017		Y				20 If Cells(j, 2) <> "" And Cells(j, 3) <> "" And Cells(j, 4) <> "" Then GoTo 40 Else GoTo 50
123ABC	green8	hot	08/01/2017	31/01/2017	Y				40 If Cells(j, 5) <> "" And Cells(j, 6) <> "" Then GoTo 45 Else GoTo 50
123ABC		hot	08/01/2017						45 rrow = rrow + 1
123ABC		hot	08/01/2017		Y				For z = 1 To 7
123ABC	green9		08/01/2017	31/01/2017					Cells(rrow, z) = Cells(j, z)
123ABC			08/01/2017		Y				Next z
123ABC	red1	hot	08/01/2017	31/01/2017	Y				50 Next j
123ABC			08/01/2017		Y		row 26		Next k
									End Sub
					col F
123ABC							row 33
456DEF
123ABC	red2	hot	01/01/2017	31/01/2017	Y
123ABC	green8	hot	08/01/2017	31/01/2017	Y
123ABC	red1	hot	08/01/2017	31/01/2017	Y
456DEF	green4	hot	08/01/2017	31/01/2017	Y
456DEF	green5	hot	08/01/2017	31/01/2017	Y

<tbody>
</tbody>

Thanks oldbrewer for the reply and suggestion. Unfortunately, though, I need a formula to do this rather than a macro/VB.