one content of cell to many row of cells

E

Eddie

I have two list of data. eg
1 A
2 B
3 C

I want to create two list of data like following

1 A
1 B
1 C

2 A
2 B
2 C

3 A
3 B
3 C

the number of cells in each list maybe vary and I don't care about the
location of cells. I am looking for a method to do that other than copy
and past. Thank you very much
 
G

Gary''s Student

Lets say in A1 thru B10 we have:

1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j

Enter and run this small macro:

Sub Macro1()
k = 1
For i = 1 To 10
v = Cells(i, 1)
For j = 1 To 11
If j = 11 Then
Else
Cells(k, 3) = v
Cells(k, 4) = Cells(j, 2)
End If
k = k + 1
Next
Next
End Sub and cols C&D will contain:

1 a
1 b
1 c
1 d
1 e
1 f
1 g
1 h
1 i
1 j

2 a
2 b
2 c
2 d
2 e
2 f
2 g
2 h
2 i
2 j

3 a
3 b
3 c
3 d
..
..
..
for a total of 100 rows. If you have more or less input data, just change
the 10 and 11 in the macro to match.
 
E

Eddie

Thank you Gary''s Student. It works out great. I just found out that I
maybe more than two lists. e,g
1 A1 C1
2 A2 C2
3 A3 C3

Is there a easy way to assign number list to letter lists. like this;

1 A1 C1
1 A2 C2
1 A3 C3

2 A1 C1
2 A2 C2
2 A3 C3

3 A1 C1
3 A2 C2
3 A3 C3

The number of letter list could be vary. Thank you .
 
Top