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...

3	Apples	4	Oranges
4	Oranges	5	Bananas
5	Bananas	6	Blueberries
3
6	Blueberries

<tbody>
</tbody>

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

Col01	Col02		Col01	Col02
3	Apples		4	Oranges
4	Oranges		5	Bananas
5	Bananas		6	Blueberries
3
6	Blueberries
*******	***********	**	*******	***********

<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> </tbody>

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...

3	Apples	4	Oranges
4	Oranges	5	Bananas
5	Bananas	6	Blueberries
3
6	Blueberries

<tbody>
</tbody>

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.