Can someone help with a formula?

I want to generate multiple combinations of a set of data and am looking for
a formula to do it.

Data Set:

Segment Options Type Options Vendor Options Portfolio Options Country
Options Revenue Stream Options
XBX Esti DLL VJV BE IntrInc
FED Actl CIT MGD NL Xcharge
SMS Rvrs SVC ES BankInt
EPG Amrt AU BlndInc
MBS NZ OthrInc
OTH US
CA
UK
CH
DE
IT
FR
BR
JP

Combination Example for FED Segment:
(I want to keep the combinations in the following order)

Segment Type Vendor Portfolio Country Revenue Stream
FED Esti DLL SVC US IntrInc
FED Actl DLL SVC US IntrInc
FED Rvrs DLL SVC US IntrInc
FED Amrt DLL SVC US Xcharge

Okay, I am assuming a couple things.
1: You need to do this one time, to get all the 8,064 different combinations
2: You can do CTRL+H for Find/Replace functions, etc.
3: Once you get all the necessary 8,064 combinations, there won't be add-ons
to Vendor options, etc. (Note: Even if there was, you could use the same
thought process behind this to do it.
I will tell you exactly what I did to get all the combinations
1 Create a new worksheet (just easier that way, you can copy paste it where
you need it later).
2 Cells A3:A8 are the segment options
3 Cells B3:B6 are the type options
4 C3:C4 = vendor options
5 D3:D5 = portfolio options
6 E3:E16 = country options
7 F3:F6 = revenue stream options
8 A18: =COUNTA(A3:A16)
9 Select A18:F18, Edit--Fill--Right (you should get values of 6, 4, 2, 3,
14, 4).
10 All cells A21:F21 are set =1
11 A22:
=IF(AND(B21=B$18,C21=C$18,D21=D$18,E21=E$18,F21=F$ 18),IF(A21=A$18,1,A21+1),A21)
12 B22:
=IF(AND(C21=C$18,D21=D$18,E21=E$18,F21=F$18),IF(B2 1=B$18,1,B21+1),B21)
13 C22: =IF(AND(D21=D$18,E21=E$18,F21=F$18),IF(C21=C$18,1, C21+1),C21)
14 D22: =IF(AND(E21=E$18,F21=F$18),IF(D21=D$18,1,D21+1),D2 1)
15 E22: =IF(F21=F$18,IF(E21=E$18,1,E21+1),E21)
16 F22: =IF(F21=F$18,1,F21+1)
17 press CTRL+G, go to A22:F8084, Edit--Fill--Down
18 press CTRL+G, go to A21:A8084, press CTRL+H, Find: 1, Replace with XBX
19 press CTRL+G, go to A21:A8084, press CTRL+H, Find: 2, Replace with FED
20 press CTRL+G, go to A21:A8084, press CTRL+H, Find: 3, Replace with SMS
21 I think you can see where I am going with this, just go to B21:B8084
when replacing the numbers with the type options, etc.
22 Last one: press CTRL+G, go to F21:F8084, press CTRL+H, Find: 4, Replace
with OtherInc
23 You now have all 8064 options, convoluted, and I am positive someone can
come up with something easier, but there it is.

Hope this helps.
--
John C


"Maureno" wrote:

Can someone help with a formula?

I want to generate multiple combinations of a set of data and am looking for
a formula to do it.

Data Set:

Segment Options Type Options Vendor Options Portfolio Options Country
Options Revenue Stream Options
XBX Esti DLL VJV BE IntrInc
FED Actl CIT MGD NL Xcharge
SMS Rvrs SVC ES BankInt
EPG Amrt AU BlndInc
MBS NZ OthrInc
OTH US
CA
UK
CH
DE
IT
FR
BR
JP

Combination Example for FED Segment:
(I want to keep the combinations in the following order)

Segment Type Vendor Portfolio Country Revenue Stream
FED Esti DLL SVC US IntrInc
FED Actl DLL SVC US IntrInc
FED Rvrs DLL SVC US IntrInc
FED Amrt DLL SVC US Xcharge

*sigh*, worked hard on this one, no response from the OP, gotta love it.
--
** John C **



"Maureno" wrote:

Can someone help with a formula?

I want to generate multiple combinations of a set of data and am looking for
a formula to do it.

Data Set:

Segment Options Type Options Vendor Options Portfolio Options Country
Options Revenue Stream Options
XBX Esti DLL VJV BE IntrInc
FED Actl CIT MGD NL Xcharge
SMS Rvrs SVC ES BankInt
EPG Amrt AU BlndInc
MBS NZ OthrInc
OTH US
CA
UK
CH
DE
IT
FR
BR
JP

Combination Example for FED Segment:
(I want to keep the combinations in the following order)

Segment Type Vendor Portfolio Country Revenue Stream
FED Esti DLL SVC US IntrInc
FED Actl DLL SVC US IntrInc
FED Rvrs DLL SVC US IntrInc
FED Amrt DLL SVC US Xcharge

Expect nothing in return and you will never be disappointed.

John C wrote:

*sigh*, worked hard on this one, no response from the OP, gotta love it.