Help with Formula

  • Thread starter Thread starter Nate
  • Start date Start date
N

Nate

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?
 
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
 
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.
 
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
 
Back
Top