Duplicate plus 2nd Column Action

[Philip112]

Hello Friends,

I have been going over code for the past week and have yet to get this, hoping that you could help.

I am looking for a formula that will:

1. Go through column A for duplicates.
2. If duplicate, then look to column B for a blank field.
3. If any of the duplicates in column B are blank, then delete entire A row for all duplicate numbers (or put a number in column c if that is easier then I can sort and delete)


So for example...

A B

3 Apples
4 Oranges
5 Bananas
3
6 Blueberries

For the example above, the formula would see that 3 is a duplicate in multiple spots, and seeing as in the 4th row there is a blank, it would delete both the first row that says Apples, AND the 4th row that has the blank field in B.

If the number 3 was duplicate in multiple spots, but there was never a blank spot in column B, then nothing would happen.

Does that make sense?

Thanks so much.

Philip

 

[Philip112]

Bump... any ideas out there on this would be greatly appreciated.

 

[Aladin Akyurek]

Bump... any ideas out there on this would be greatly appreciated.

A formula aproach cannot modify A:B directly, create the appropriate results in C:D. Maybe someone into VBA can do the required processing in A:B directly.

 

[Philip112]

A formula aproach cannot modify A:B directly, create the appropriate results in C:D. Maybe someone into VBA can do the required processing in A:B directly.

I see, thanks for explaining. Yeah I thought with VBA, except I have no experience with that.

Even just the results in C:D would work, then I can sort and delete myself.

 

[Aladin Akyurek]

I see, thanks for explaining. Yeah I thought with VBA, except I have no experience with that.

Even just the results in C:D would work, then I can sort and delete myself.

Not quite sure, but here is an interpretation...

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD="width: 98"][/TD]
[TD="width: 64"][/TD]
[TD="width: 83"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Apples[/TD]
[TD="align: right"]4[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Oranges
[/TD]
[TD="align: right"]5[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Bananas
[/TD]
[TD="align: right"]6[/TD]
[TD]Blueberries[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Blueberries[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

C2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(MATCH($A$2:$A$6,$A$2:$A$6,0),
  ROW($A$2:$A$6)-ROW($A$2)+1)=1,
  ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($C$2:C2))),"")

D2, just enter and copy down:

=IF($C2="","",VLOOKUP($C2,$A$2:$B$6,2,0))

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 171"]
<colgroup><col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <tbody>[TR]
[TD="width: 42, bgcolor: transparent"]Col01[/TD]
[TD="width: 62, bgcolor: transparent"]Col02[/TD]
[TD="width: 17, bgcolor: transparent"] [/TD]
[TD="width: 42, bgcolor: transparent"]Col01[/TD]
[TD="width: 62, bgcolor: transparent"]Col02[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Apples[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow, align: right"]4[/TD]
[TD="bgcolor: yellow"]Oranges[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Oranges[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow, align: right"]5[/TD]
[TD="bgcolor: yellow"]Bananas[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Bananas[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow, align: right"]6[/TD]
[TD="bgcolor: yellow"]Blueberries[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]Blueberries[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]***********[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]***********[/TD]
[/TR]
</tbody>[/TABLE]

Formula

In D2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX(A$2:A$6,SMALL(IF(MMULT(--($A$2:$A$6=TRANSPOSE((COUNTIF($A$2:$A$6,$A$2:$A$6)>1)*($B$2:$B$6="")*$A$2:$A$6)),ROW($A$2:$A$6)^0)=0,ROW($A$2:$A$6)-ROW($A$2)+1),ROWS(D$2:D2))),"")

And copy to the right and down.

Markmzz

 

[Philip112]

Not quite sure, but here is an interpretation...

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD="width: 98"][/TD]
[TD="width: 64"][/TD]
[TD="width: 83"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Apples[/TD]
[TD="align: right"]4[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Oranges[/TD]
[TD="align: right"]5[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Bananas[/TD]
[TD="align: right"]6[/TD]
[TD]Blueberries[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Blueberries[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

C2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(MATCH($A$2:$A$6,$A$2:$A$6,0),
  ROW($A$2:$A$6)-ROW($A$2)+1)=1,
  ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($C$2:C2))),"")

D2, just enter and copy down:

=IF($C2="","",VLOOKUP($C2,$A$2:$B$6,2,0))

Thanks Aladin, I had to make a small change to capture another rows data I didn't want to lose, but nailed it in the end. I appreciate it!

Philip

 

[Aladin Akyurek]

Thanks Aladin, I had to make a small change to capture another rows data I didn't want to lose, but nailed it in the end. I appreciate it!

Philip

You are welcome. Thanks for providing feedback.