Prev: how do i make excel pick words i start typing
Next: MAX Function in VBE
From: Steve on 13 May 2010 10:39
Example:
In spreadsheet - ONE, it is populated in a column:

A
B
C
D

In spreadsheet - TWO, it is populated in a column:

1
2
3
4
****************************************
Calculated Results desired in spreadsheet - ONE:
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4
C 1
C 2
C 3
C 4
D 1
D 2
D 3
D 4

Thanks for any direction


From: SteAXA on 13 May 2010 11:15
The fast way is that you can do an import data from external source and your
database is the worksheet, you join the sheets and you must remember to order
data.
It's more easy to use.
Ste'

From: Roger Govier on 13 May 2010 12:04
Hi Steve

You don't need to use 2 sheets.
On sheet1 enter
A2 A, A3, B, A4 C, A5 D
In B2 Enter the number of repeats that you want 4

In D4 enter
=INDEX(A:A,INT(ROW()/$B$2)+1)&MOD(ROW(),$B$2)+1
and copy down as required

The key is to start the formula in the row number represented by your
Repeats in B2
So if you want 10 repeats, enter 10 in B2, and start the first formula
in D10

Once you have created your series, Copy>Paste Special to wherever you
want to Fix the values.
--
Regards
Roger Govier

Steve wrote:
> Example:
> In spreadsheet - ONE, it is populated in a column:
>
> A
> B
> C
> D
>
> In spreadsheet - TWO, it is populated in a column:
>
> 1
> 2
> 3
> 4
> ****************************************
> Calculated Results desired in spreadsheet - ONE:
> A 1
> A 2
> A 3
> A 4
> B 1
> B 2
> B 3
> B 4
> C 1
> C 2
> C 3
> C 4
> D 1
> D 2
> D 3
> D 4
>
> Thanks for any direction
>
>
From: Steve on 13 May 2010 15:14
Thanks Roger:
I have two separate already populated spreadsheets here....one spreadsheet
has 2 columns, and the other has 4 columns.... Your code functions as you
denoted, but the functionality of the data encompasses 2 spreadsheets.
Thanks.
Steve Hicks


"Roger Govier" wrote:

> Hi Steve
>
> You don't need to use 2 sheets.
> On sheet1 enter
> A2 A, A3, B, A4 C, A5 D
> In B2 Enter the number of repeats that you want 4
>
> In D4 enter
> =INDEX(A:A,INT(ROW()/$B$2)+1)&MOD(ROW(),$B$2)+1
> and copy down as required
>
> The key is to start the formula in the row number represented by your
> Repeats in B2
> So if you want 10 repeats, enter 10 in B2, and start the first formula
> in D10
>
> Once you have created your series, Copy>Paste Special to wherever you
> want to Fix the values.
> --
> Regards
> Roger Govier
>
> Steve wrote:
> > Example:
> > In spreadsheet - ONE, it is populated in a column:
> >
> > A
> > B
> > C
> > D
> >
> > In spreadsheet - TWO, it is populated in a column:
> >
> > 1
> > 2
> > 3
> > 4
> > ****************************************
> > Calculated Results desired in spreadsheet - ONE:
> > A 1
> > A 2
> > A 3
> > A 4
> > B 1
> > B 2
> > B 3
> > B 4
> > C 1
> > C 2
> > C 3
> > C 4
> > D 1
> > D 2
> > D 3
> > D 4
> >
> > Thanks for any direction
> >
> >
> .
>
 | 
Pages: 1
Prev: how do i make excel pick words i start typing
Next: MAX Function in VBE