How to get rid of combinations?

  • Thread starter Thread starter mike999
  • Start date Start date
M

mike999

Hi everybody,
Can anyone tell me how to perform an operation in Excel?
Let's say I have a huge amount of combinations of five numbers in
column a short version of which looks something like this:

1 13 19 27 32
1 13 19 30 32
2 13 19 32 35
3 13 19 32 36
4 13 19 31 37
5 13 21 23 32
6 13 21 25 32
7 13 21 27 32
8 13 21 30 32
9 13 21 32 35
10 13 21 32 36
10 13 21 32 37
10 13 21 25 33
11 13 21 23 32
11 14 22 26 32
11 15 22 27 33
11 15 22 28 32
12 14 22 30 32
12 14 22 32 34
12 14 22 32 35
12 14 22 34 36
12 14 22 32 37
12 14 23 24 34
12 14 23 25 32
12 14 23 26 34
12 14 23 27 32
12 14 25 28 34
12 14 25 30 32
...

What I want Excel help me do is to go through all the combinations an
look for numbers 11, 14, 25, 28 and 34. Then I want to get rid of al
the combinations containing more than ONE of the numbers: 11, 14, 25
28 and 34. That is if a combination contains only 11 – it stays, but i
it has both 11 and 14 in it – the combination must be deleted. Again i
there is let’s say number 25 only in a combination – I want to keep it
but if it has 14, 25, 28 and 34 – I want to discard such
combination.

If somebody knows how to do it - would you please tell me in detail?

Your help is greatly appreciated
 
Assuming your data is in say A1:Exxx, with headers in row 1. Put your 5 values
to check for in 5 cells, eg I1:M1

Now in a helper column, eg Col G, in say cell G2 put the following formula and
then array enter it using CTRL+SHIFT+ENTER

=SUM(COUNTIF(A2:E2,$I$1:$M$1))>1

Now copy this cell and then paste it as far down Col G as you need to. This
will effectively set up a series of TRUE/FALSE statements depending on whether
or not each row contains more than 1 of your check numbers.

Now stick any kind of header on Col G and then from that header, select down to
the last entry in Col G (Use CTRL+SHIFT+DOWN ARROW if it helps) and then do Data
/ Filter / Autofilter, and then Filter on TRUE. Now once again, select all you
can see in Col G and then do Edit / Go To / Special / Visible cells only, and
then do Edit / Delete / Entire Row. This will now delete the WHOLE row for
every row that has more than one of your check numbers in it.
 
one way :

F2: =SUM(COUNTIF(A2:E2,{11;14;25;28;34}))=1

Copy down and autofilter on TRUE (this assumes you have headers) to get
just the combos that contain 1 and only 1 of the digits.

or Autofilter on False to get the ones that don't meet that criteria.

If you want to keep rows that don't have any of the numbers, use

F2: =SUM(COUNTIF(A2:E2,{11;14;25;28;34}))>1

and autofilter on True to get the ones that have multiple values. You
can then choose Edit/Goto/Special/Visible Cells Only and delete those
rows.
 
Back
Top