Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Formula
Sorry but I'm relatively inexperienced with excel. I have 4 columns with a
word in each cell for about 5 to 10 rows. I need a formula that will give me every combination of every cell in my data field can anyone help me? -- Nate |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Formula
When you say "data field" do you mean your table of data, i.e. 40
cells? Or do you mean combinations of your 4 cells for each row? If the latter, then consider your cells containing A, B, C and D - the combinations would be: AB AC AD BC BD CD ABC ABD ACD BCD ABCD and even more if the order is important, i.e. if BA is different than AB. Do you realise how many combinations from 40 cells there would be? Pete On Jan 30, 12:27*am, Nate wrote: Sorry but I'm relatively inexperienced with excel. I have 4 columns with a word in each cell for about 5 to 10 rows. I need a formula that will give me every combination of every cell in my data field can anyone help me? -- Nate |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Formula
Thanks Pete I should be more specific. I have 2 names of bike frames in
Column A. In Column B I have 5 names of Kits that can be put on to each of those frames. In Column C there are 4 shocks that could go on each frame and each kit. In Column D I have 10 forks that could go with each frame, each kit, and each shock. So I need every combination that expands outward from Column A. -- Nate "Pete_UK" wrote: When you say "data field" do you mean your table of data, i.e. 40 cells? Or do you mean combinations of your 4 cells for each row? If the latter, then consider your cells containing A, B, C and D - the combinations would be: AB AC AD BC BD CD ABC ABD ACD BCD ABCD and even more if the order is important, i.e. if BA is different than AB. Do you realise how many combinations from 40 cells there would be? Pete On Jan 30, 12:27 am, Nate wrote: Sorry but I'm relatively inexperienced with excel. I have 4 columns with a word in each cell for about 5 to 10 rows. I need a formula that will give me every combination of every cell in my data field can anyone help me? -- Nate |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Formula
Okay, well that will give you 2 x 5 x 4 x 10 (=400) different
combinations. I set up this test data in A1:D10: Frame1 kit1 shock1 fork1 Frame2 kit2 shock2 fork2 kit3 shock3 fork3 kit4 shock4 fork4 kit5 fork5 fork6 fork7 fork8 fork9 fork10 (it will probably look terrible when posted !!) Then I put this formula in E1: =INDIRECT("A"&MOD(INT((ROW(A1)-1)/(COUNTA(B:B)*COUNTA(C:C)*COUNTA (D:D))),COUNTA(A:A))+1)&"-"&INDIRECT("B"&MOD(INT((ROW(A1)-1)/(COUNTA (C:C)*COUNTA(D:D))),COUNTA(B:B))+1)&"-"&INDIRECT("C"&MOD(INT((ROW (A1)-1)/COUNTA(D:D)),COUNTA(C:C))+1)&"-"&INDIRECT("D"&MOD(ROW (A1)-1,COUNTA(D:D))+1) and copied it down to row 401 (you only need to go to 400, but see below). Here's the first part of it: Frame1-kit1-shock1-fork1 Frame1-kit1-shock1-fork2 Frame1-kit1-shock1-fork3 Frame1-kit1-shock1-fork4 Frame1-kit1-shock1-fork5 Frame1-kit1-shock1-fork6 Frame1-kit1-shock1-fork7 Frame1-kit1-shock1-fork8 Frame1-kit1-shock1-fork9 Frame1-kit1-shock1-fork10 Frame1-kit1-shock2-fork1 Frame1-kit1-shock2-fork2 Frame1-kit1-shock2-fork3 Frame1-kit1-shock2-fork4 Frame1-kit1-shock2-fork5 Frame1-kit1-shock2-fork6 Frame1-kit1-shock2-fork7 Frame1-kit1-shock2-fork8 Frame1-kit1-shock2-fork9 Frame1-kit1-shock2-fork10 Frame1-kit1-shock3-fork1 Frame1-kit1-shock3-fork2 Frame1-kit1-shock3-fork3 Frame1-kit1-shock3-fork4 Frame1-kit1-shock3-fork5 Frame1-kit1-shock3-fork6 Frame1-kit1-shock3-fork7 Frame1-kit1-shock3-fork8 Frame1-kit1-shock3-fork9 Frame1-kit1-shock3-fork10 Frame1-kit1-shock4-fork1 Frame1-kit1-shock4-fork2 Frame1-kit1-shock4-fork3 Frame1-kit1-shock4-fork4 Frame1-kit1-shock4-fork5 and the last few rows: Frame2-kit5-shock4-fork7 Frame2-kit5-shock4-fork8 Frame2-kit5-shock4-fork9 Frame2-kit5-shock4-fork10 Frame1-kit1-shock1-fork1 Note that the last (row 401) has started to repeat the pattern. Obviously you'll need to put your own names in the cells A1:D10. The formula is dynamic - i.e. add another Kit, delete a Shock etc and it will adjust, but you should have contiguous data in each column (not intervening blanks). You might need to copy it down further if you have more combinations. What a strange thing to ask for, though !! Hope this helps. Pete On Jan 30, 12:56*am, Nate wrote: Thanks Pete I should be more specific. I have 2 names of bike frames in Column A. In Column B I have 5 names of Kits that can be put on to each of those frames. In Column C there are 4 shocks that could go on each frame and each kit. In Column D I have 10 forks that could go with each frame, each kit, and each shock. So I need every combination that expands outward from Column A. -- Nate "Pete_UK" wrote: When you say "data field" do you mean your table of data, i.e. 40 cells? Or do you mean combinations of your 4 cells for each row? If the latter, then consider your cells containing A, B, C and D - the combinations would be: AB AC AD BC BD CD ABC ABD ACD BCD ABCD and even more if the order is important, i.e. if BA is different than AB. Do you realise how many combinations from 40 cells there would be? Pete On Jan 30, 12:27 am, Nate wrote: Sorry but I'm relatively inexperienced with excel. I have 4 columns with a word in each cell for about 5 to 10 rows. I need a formula that will give me every combination of every cell in my data field can anyone help me? -- Nate- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|