# Going through all possibilities from 2 lists ... How to Do?

#### krazykaj

##### Board Regular
Hello,

Here is another question (i seem to have so many )

Say i have this in excel:

List 1
Cell A1 = a
Cell A2 = b
Cell A3 = c

List 2
Cell B1 = 1
Cell B2 = 2
Cell B3 = 3

And then have excel, say on a commandbutton_click, run through a code, using all possibilities made by mixing the two columns together for logical tests:

So I'd like something along these lines to happen.
The generic code being:

If whatever_1stCell = varFromList1 AND whatever_2ndCell = varFromList2 then
call Do_Something
End If

And basically put that generic code into some sort of loop, so that each time it runs, the varFromList1 and the varFromList2 will change eventually represent all possibilities of the two lists. i.e. I'd like this to happen:

First loop, the code will be:
If whatever_1stCell = a AND whatever_2ndCell = 1 then
call Do_Something
End If

Second loop, the code will be:
If whatever_1stCell = a AND whatever_2ndCell = 2 then
call Do_Something
End If

Third loop, the code will be:
If whatever_1stCell = a AND whatever_2ndCell = 3 then
call Do_Something
End If

Fourth loop, the code will be:
If whatever_1stCell = b AND whatever_2ndCell = 1 then
call Do_Something
End If

Fifth loop, the code will be:
If whatever_1stCell = b AND whatever_2ndCell = 2 then
call Do_Something
End If

. . . And so on, untill the last run through the loop, where the code should be:

If whatever_1stCell = c AND whatever_2ndCell = 3 then
call Do_Something
End If

So you can see i'm trying to have it so that two variable will test all possibilities of the two lists mixed together.
i.e.
a and 1
a and 2
a and 3
b and 1
b and 2
b and 3
c and 1
c and 2
c and 3

How could i go about doing this???
Any help would be greatly appreciated

Cheers
kJ

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
krazykaj

Does it need to be a macro? Is a formula approach worth considering?

Peter_SSs said:
krazykaj

Does it need to be a macro? Is a formula approach worth considering?

How do you mean?
I'm open to anything

Thankyou
KJ

OK, see if I have understood what you want and if this is any good to you.

I have extended your data a little by adding d and e in column A.

D2 =COUNTA(A:A) and E2 =COUNTA(B:B) and F2 =D2*E2 are fairly obvious. Columns D, E and F could be hidden if you want.

H1 is:
=IF(ROW()<=\$F\$2,INDEX(\$A:\$B,1+INT((ROW()-1)/\$E\$2),1)&INDEX(\$A:\$B,1+MOD((ROW()-1),\$E\$2),2),"")

This formula needs to be copied down the number of rows shown by the number in F2 (but can be copied further).
Mr Excel.xls
ABCDEFGH
1a1No in ANo in BTotala1
2b25315a2
3c3a3
4db1
5eb2
6b3
7c1
8c2
9c3
10d1
11d2
12d3
13e1
14e2
15e3
16
Combinations (2)

Hi KJ,

If you wanted a VBA solution, maybe you can adapt this:

Code:
``````Sub Combos()
Dim  R1 As Range, R2 As Range
Dim WS As Worksheet

Set WS = Sheets("Sheet1")

For Each R1 In WS.Range("A1:A" & WS.Range("A65536").End(xlUp).Row)
For Each R2 In WS.Range("B1:B" & WS.Range("B65536").End(xlUp).Row)
MsgBox R1.Text & " " & R2.Text
Next R2
Next R1
End Sub``````

Hello

That's great.

Thankyou very much Peter_SSs and al_b_cnu for the input/comments!

You've given me more than enough to work with now . . .

Thankyou kindly again,
Cheers
KJ

One last nit . . .

Hello again,

I just have one more question, as sort of a follow-through on the above:
BTW, what you both gave proved great thanks again.

Here is what I currently have:
Book2.xls
ABCDEFGHIJ
1appleredappleredapplered4
2applegreenorangeorange4416appleorange0
3appleredbananayellowappleyellow0
4orangeorangemangogreenapplegreen2
5orangeorangeorangered0
6bananayelloworangeorange3
7appleredorangeyellow0
8orangeorangeorangegreen0
9appleredbananared0
10bananayellowbananaorange0
11mangoorangebananayellow2
12applegreenbananagreen0
13mangored0
14mangoorange1
15mangoyellow0
Sheet1

Here's a quick explanation.

1. I have a list of items (column A) with a description of each (Column B)
2. I then created an index of the list (column C) which remove duplicates etc., reduces it to the bare minimum. (I just typed them in, but I now have a way of calculating it automatically)
3. Then, using what you gave above (slightly modified), I now can re-create all possible possibilities (columns H and I) from the index.
4. I then created a column which takes a possibility, goes through the original list, and sees if I there is actually an instance of it there. If the value is 0, then no instance of that possibility was found, it the value is 1 or greater, then obviously an instance of that particular possibility was found in the original list.

What I would like to do now is to take all the info in columns H, I and J . . . to then create a Final List, which will now eliminate the instances with a value of 0 in column J.
So this new list will be exactly the same as that in Column H and I, except only the instances with a value greater than zero will be in this list.

So the result would be something like this . . .
(I just typed in the values, but I’d like to have it automatically calculated Like what you did above . . .)

[See Next Post]

Again, I hope that made sense . . .
Thankyou again greatly for the help and assistance
Much appreciated.

Kind regards,
KJ

krazykaj

It depends on what you want to do with that 'final list'.

One of the easiest ways to get the final list is to select column J, apply AutoFilter (Data|Filter|AutoFilter) and filter off the zero values (Custom|Does not equal|0). Note that this will also hide some of your data in earlier columns but if you just want to look at the list briefly, or print it, or copy it to another location, this might be all you need.

If you need a different method, post back and I am sure somebody will come up with something.

Hi KJ,

one way would be to use advanced filter - select 'unique records' & 'copy to another location'

Note that this requires each column to have a heading.

Replies
9
Views
170
Replies
5
Views
344
Replies
3
Views
151
Replies
5
Views
308
Replies
8
Views
210

1,211,457
Messages
6,101,964
Members
447,766
Latest member
cool_aikon

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

### Which adblocker are you using?

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

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