Make possible combinations

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

1st data in cells C6:E7, I need to get possible sets in the cells H6:O7</SPAN></SPAN>

2nd data in cells C9:E10, I need to get possible sets in the cells H9:O10 </SPAN></SPAN>

3rd data in cells C12:E13, I need to get possible sets in the cells H12:O13 </SPAN></SPAN>

And so on....</SPAN></SPAN>

Example sample data</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5			P1	P2	P3			Set 1	Set 2	Set 3	Set 4	Set 5	Set 6	Set 7	Set 8	Set 9
6			1	X				1	X
7					2			2	2
8
9			1					1	1
10			1	X				1	X
11
12			1		2			1	1	2	2
13			1	X				1	X	1	X
14
15			1	X	2			1	1	X	X	2	2
16				X	2			X	2	X	2	X	2
17
18			1	X				1	X
19				X				X	X
20
21				X				X	X
22			1	X				1	X
23
24			1					1	1
25				X	2			X	2
26
27			1	X	2			1	1	1	X	X	X	2	2	2
28			1	X	2			1	X	2	1	X	2	1	X	2
29
30
Sheet1

Thank you in advance</SPAN></SPAN>

Regards</SPAN></SPAN>,</SPAN>
Kishan</SPAN></SPAN>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG10Dec14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("C" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = 6 To Lst
    c = 0
    [COLOR="Navy"]For[/COLOR] Ac = 3 To 6
        [COLOR="Navy"]For[/COLOR] Ac2 = 3 To 6
          [COLOR="Navy"]If[/COLOR] Not IsEmpty(Cells(n, Ac)) And Not IsEmpty(Cells(n + 1, Ac2)) [COLOR="Navy"]Then[/COLOR]
             c = c + 1
                Cells(n, c + 7) = Cells(n, Ac)
                Cells(n + 1, c + 7) = Cells(n + 1, Ac2)
           [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac2
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG10Dec14
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac2 [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Lst [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Lst = Range("C" & Rows.Count).End(xlUp).Row
[COLOR=navy]For[/COLOR] n = 6 To Lst
    c = 0
    [COLOR=navy]For[/COLOR] Ac = 3 To 6
        [COLOR=navy]For[/COLOR] Ac2 = 3 To 6
          [COLOR=navy]If[/COLOR] Not IsEmpty(Cells(n, Ac)) And Not IsEmpty(Cells(n + 1, Ac2)) [COLOR=navy]Then[/COLOR]
             c = c + 1
                Cells(n, c + 7) = Cells(n, Ac)
                Cells(n + 1, c + 7) = Cells(n + 1, Ac2)
           [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Ac2
    [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

MickG, your coding is unique!! It worked ideal with example layout, even it is working if I delete empty rows in-between each set or double them perfect!!</SPAN></SPAN>

Thank you for your time and help</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan </SPAN></SPAN>

 

[MickG]

You're welcome