Replicate one list against each value in another list - VBA?

[Khristopher]

Hi All,

This is my first post here so looking for a point in the right direction more than anything, I've done a lot of Google searching and forum searching but think the way I'm phrasing this question isn't coming up with the results i need.

I want to replicate every cell in one column against each cell in another list.

For example,

Column A contains numerical characters 1,2,3,4,5
Column B contains characters a,b,c,d,e

I want a new sheet to then contain:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[/TR]
</tbody>[/TABLE]

I'm doing this manually at the moment which is taking far too much time to populate data tables,

Any help is appreciated,

Thanks,
Khris

 

[XOR LX]

Hi,

Assuming the 5 rows of data you give are in Sheet1A1:B5, enter this formula in Sheet2A1 and copy down:

=INDEX(Sheet1!$A$1:$A$5,ROUNDUP(ROWS($1:1)/5,0))

And this one in Sheet2B1 and copy down:

=INDEX(Sheet1!$B$1:$B$5,IFERROR(1/(1/(MOD(ROWS($1:1),5))),5))

(Note: you don't give your Excel version - the second formula assumes you are using 2007 or later.)

Regards

 

[Khristopher]

Hi,

Thankyou for that - Have got it to do exactly what was required. Added in some named ranges using offset formulas and a COUNTA so that if a longer list is used the formula wont need updating.

Thanks,
Khris

 

[XOR LX]

Excellent. Thanks for the feedback.