I have 3 columns of data, not all the same length. I then have Excel 2007 create three more columns of data which will show each possible combination of the three sets of original data. Here are the formulas I created that works. No headers are in the columns and the original data is in the first three columns A,B,&C.
=OFFSET(LookUp!$A$1,INT((ROW()-1)/(COUNTA(LookUp!B:B)
*COUNTA(LookUp!C:C))),0)
=OFFSET(LookUp!$A$1,INT(MOD((ROW()-1),
COUNTA(LookUp!C:C)*COUNTA(LookUp!B:B))
/COUNTA(LookUp!C:C)),1)
=OFFSET(LookUp!$A$1,MOD((ROW()-2),(COUNTA(LookUp!C:C))),2)
My question now is how do I do more than three columns, say four or five? And is there a way to modify the formula to put headers above the data but not include that in the combinations? Help! Anyone?
No comments:
Post a Comment