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

krazykaj

Board Regular
Joined
Jul 20, 2005
Messages
143
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 :biggrin:

Hope that all made sense :(

Thankyou for your time,
Cheers
kJ
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
krazykaj

Does it need to be a macro? Is a formula approach worth considering?
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
Hello :biggrin:

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
 
Upvote 0
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 :biggrin:
Much appreciated.

Kind regards,
KJ
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

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