CODEWITHSUNDEEP
vba
J-L
J-L

Reputation: 3

Finding repeating values in 3 columns

I have 7 spacers (1.25mm, 1.5mm, 3.0mm, 3.5mm, 4.0mm, 4.5mm, 5.0mm) that are used in combinations (up to 3) to add up to required final spacing. For example, for a final spacing of 6.0mm, I can choose between these combos: '3.0 + 1.5 + 1.5', or '1.25 + 1.25 + 3.5'. I've already produced a table with 3 columns of all combinations, and a 4th column with their sum. What I need is a method to remove all combinations that repeat, such as: '1.5 + 3.0 + 1.5' and '1.5 + 1.5 + 3.0' and be left with unique sets of combos only. The order in which the spacer are put together is not important. In the table excerpt below where sum = 6, I want to see only 2 available sets of unique combos; the other sets are just repeats of the same spacers that are arranged in different orders. table excerpt Hope that's clear. I assume one would need VBA to solve this. I appreciate any help you can share. J-L

Upvotes: 0

Views: 58

Answers (1)

Scott Craner
Scott Craner

Reputation: 152475

Assuming your table is in A1,

In E1:G1 put 1,2,3

The in E2 put this formula:

=AGGREGATE(15,6,$A2:$C2,E$1)

Copy over three and down the list.

enter image description here

Then using Remove Duplicates on the Data Tab we select only columns 1,2,3(E:G)

Hit OK

enter image description here

Then you can delete the new columns.

Upvotes: 2

Related Questions