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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
krazykaj

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

krazykaj

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

Thankyou
KJ
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
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)
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494

ADVERTISEMENT

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
 

krazykaj

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

krazykaj

Board Regular
Joined
Jul 20, 2005
Messages
143

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
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.
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,389
Messages
5,571,850
Members
412,421
Latest member
Rimo86
Top