make combinations from items in different columns in excel [SOLVED]

[ravismiles]

Here is my problem. I have 3 different parameters customer, selling location and product in 3 different columns. My price will be a function of these 3 parameters. To upload these prices in system i have to get a linear excel table with all possible combinations possible. I can use macros to get the possible combinations but I want do it thru formulae so that file is prepared one time and I can use it every month when i change the price. My starting point will look like (8 customers, 5 locations, 7 products)

[pre]

Customer  Location  Product
C1        L1        P1
C2        L2        P2
C3        L3        P3
C4        L4        P4
C5        L5        P5
C6                  P6
C7                  P7
C8

[/pre]
Actual numbers are quite large. Above table is for illustration only. Once the combination table is ready i can put prices based on different criteria. Pl help

 

[NARAYANK991]

Hi Ravi ,


If we assume that your parameters are in 3 named ranges such as Customers , Locations and Products , then in a separate worksheet , start your listing of the combinations from row 2 , with the following formulae :


In column A , starting from A2 : =INDEX(Customers,1+INT((ROW()-ROW($A$2))/(ROWS(Locations)*ROWS(Products))))


In column B , starting from B2 : =INDEX(Locations,1+MOD(INT((ROW()-ROW($A$2))/ROWS(Products)),ROWS(Locations)))


In column C , starting from C2 : =INDEX(Products,1+MOD((ROW()-ROW($A$2)),ROWS(Products)))


Narayan

 

[Sajan]

Hi Narayan,

Very nice technique! I loved it.


(By the way, INT() is not necessary.)


-Sajan.

 

[ravismiles]

Thanks Narayan.It worked for me.

Thanks Sajan, it was again a learning that row number or column number in INDEX function need not be integral.