Delete duplicate combinations

SirFox

New Member
Joined
Jan 20, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

In the pic below I've created four tables. Fruit1/2/3. I want to create all the unique combinations possible from these. After using Power Query with this formula = Table.ExpandTableColumn(#"Added Custom1", "FruitMerge2", {"Fruit3"}, {"Fruit3"}) I get the fourth table which you can see in columns FGH.

The first problem is I only want unique combinations, the first of which only appears in Row14 - i.e. only one fruit type per row, no duplicates. Nevertheless, even with this situation I thought I could simply use Remove Duplicates in Data tab, selecting all three columns. However it only seems to work for two columns not 3! When I include all three it removes no rows. I also tried Advanced Sort in Data and no change. Any help much appreciated!
In the
Example1.JPG
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi

I have added a Helper Column to get unique items
Book1
IJKLMNOP
1Fruit1Fruit2Fruit3Helper
2AppleAppleApple AppleBananaOrange
3AppleAppleBanana AppleBananaPear
4AppleAppleOrange AppleBananaGrape
5AppleApplePear AppleBananaLemon
6AppleAppleGrape AppleBananaLime
7AppleAppleLemon AppleBananaWatermelon
8AppleAppleLime AppleBananaGrapefruit
9AppleAppleWatermelon AppleBananaMango
10AppleAppleGrapefruit AppleOra ngeBanana
11AppleAppleMango AppleOrangePear
12AppleBananaApple AppleOrangeGrape
13AppleBananaBanana AppleOrangeLemon
14AppleBananaOrange1AppleOrangeLime
15AppleBananaPear1AppleOrangeWatermelon
16AppleBananaGrape1AppleOrangeGrapefruit
17AppleBananaLemon1AppleOrangeMango
18AppleBananaLime1ApplePearBanana
19AppleBananaWatermelon1ApplePearOrange
20AppleBananaGrapefruit1ApplePearGrape
21AppleBananaMango1ApplePearLemon
22AppleOrangeApple 
23AppleOra ngeBanana1
24AppleOrangeOrange 
25AppleOrangePear1
26AppleOrangeGrape1
27AppleOrangeLemon1
28AppleOrangeLime1
29AppleOrangeWatermelon1
30AppleOrangeGrapefruit1
31AppleOrangeMango1
32ApplePearApple 
33ApplePearBanana1
34ApplePearOrange1
35ApplePearPear 
36ApplePearGrape1
37ApplePearLemon1
Sheet8
Cell Formulas
RangeFormula
N2:P21N2=FILTER(I2:K37,L2:L37=1)
L2:L37L2=IF(COUNTIF(I2:K2,I2)+COUNTIF(I2:K2,J2)+COUNTIF(I2:K2,K2)=3,1,"")
Dynamic array formulas.
 
Upvote 0
Solution
Or in L2 you could use =IF(SUMPRODUCT(--((I2:K2=I2)+(I2:K2=J2)+(I2:K2=K2)))=3,1,"")
 
Upvote 0
Or in L2 you could use =IF(SUMPRODUCT(--((I2:K2=I2)+(I2:K2=J2)+(I2:K2=K2)))=3,1,"")
Hi

I have added a Helper Column to get unique items
Book1
IJKLMNOP
1Fruit1Fruit2Fruit3Helper
2AppleAppleApple AppleBananaOrange
3AppleAppleBanana AppleBananaPear
4AppleAppleOrange AppleBananaGrape
5AppleApplePear AppleBananaLemon
6AppleAppleGrape AppleBananaLime
7AppleAppleLemon AppleBananaWatermelon
8AppleAppleLime AppleBananaGrapefruit
9AppleAppleWatermelon AppleBananaMango
10AppleAppleGrapefruit AppleOra ngeBanana
11AppleAppleMango AppleOrangePear
12AppleBananaApple AppleOrangeGrape
13AppleBananaBanana AppleOrangeLemon
14AppleBananaOrange1AppleOrangeLime
15AppleBananaPear1AppleOrangeWatermelon
16AppleBananaGrape1AppleOrangeGrapefruit
17AppleBananaLemon1AppleOrangeMango
18AppleBananaLime1ApplePearBanana
19AppleBananaWatermelon1ApplePearOrange
20AppleBananaGrapefruit1ApplePearGrape
21AppleBananaMango1ApplePearLemon
22AppleOrangeApple 
23AppleOra ngeBanana1
24AppleOrangeOrange 
25AppleOrangePear1
26AppleOrangeGrape1
27AppleOrangeLemon1
28AppleOrangeLime1
29AppleOrangeWatermelon1
30AppleOrangeGrapefruit1
31AppleOrangeMango1
32ApplePearApple 
33ApplePearBanana1
34ApplePearOrange1
35ApplePearPear 
36ApplePearGrape1
37ApplePearLemon1
Sheet8
Cell Formulas
RangeFormula
N2:P21N2=FILTER(I2:K37,L2:L37=1)
L2:L37L2=IF(COUNTIF(I2:K2,I2)+COUNTIF(I2:K2,J2)+COUNTIF(I2:K2,K2)=3,1,"")
Dynamic array formulas.

Hi

I have added a Helper Column to get unique items
Book1
IJKLMNOP
1Fruit1Fruit2Fruit3Helper
2AppleAppleApple AppleBananaOrange
3AppleAppleBanana AppleBananaPear
4AppleAppleOrange AppleBananaGrape
5AppleApplePear AppleBananaLemon
6AppleAppleGrape AppleBananaLime
7AppleAppleLemon AppleBananaWatermelon
8AppleAppleLime AppleBananaGrapefruit
9AppleAppleWatermelon AppleBananaMango
10AppleAppleGrapefruit AppleOra ngeBanana
11AppleAppleMango AppleOrangePear
12AppleBananaApple AppleOrangeGrape
13AppleBananaBanana AppleOrangeLemon
14AppleBananaOrange1AppleOrangeLime
15AppleBananaPear1AppleOrangeWatermelon
16AppleBananaGrape1AppleOrangeGrapefruit
17AppleBananaLemon1AppleOrangeMango
18AppleBananaLime1ApplePearBanana
19AppleBananaWatermelon1ApplePearOrange
20AppleBananaGrapefruit1ApplePearGrape
21AppleBananaMango1ApplePearLemon
22AppleOrangeApple 
23AppleOra ngeBanana1
24AppleOrangeOrange 
25AppleOrangePear1
26AppleOrangeGrape1
27AppleOrangeLemon1
28AppleOrangeLime1
29AppleOrangeWatermelon1
30AppleOrangeGrapefruit1
31AppleOrangeMango1
32ApplePearApple 
33ApplePearBanana1
34ApplePearOrange1
35ApplePearPear 
36ApplePearGrape1
37ApplePearLemon1
Sheet8
Cell Formulas
RangeFormula
N2:P21N2=FILTER(I2:K37,L2:L37=1)
L2:L37L2=IF(COUNTIF(I2:K2,I2)+COUNTIF(I2:K2,J2)+COUNTIF(I2:K2,K2)=3,1,"")
Dynamic array formulas.
Hi Sky, thanks for your answer - I see the logic but when I enter the formula it simply doesn't do anything except paste the text of the formula into the cell. Sorry for being stupid, what am I doing wrong?
 
Upvote 0
I have had this many times. The cell that you are putting the formula into may be formatted as Text. You could try Clear All from the Editing Section on the Home Ribbon or put the forumla into another cell, in the column or delete the entire column.
 
Upvote 0
I have had this many times. The cell that you are putting the formula into may be formatted as Text. You could try Clear All from the Editing Section on the Home Ribbon or put the forumla into another cell, in the column or delete the entire column.
Hi Sky, I got it to work! You're a genius! Really appreciate your help. I scoured the net for ages to find the solution. Many thanks!!!
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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