2 columns, take A1 and B1, A1 and B2, A1 and B3....then....A2 and B1, A2 and B2, A2 and B3

[marcus76]

Hi,

I have 2 columns, as follows:

A1: apples
A2: pears
A3: oranges
A4: bananas
A5: grapes

B1: 1
B2: 2
B3: 3


I need in column C: the following:

C1: apples 1
C2: apples 2
C3: apples 3
C4: pears 1
C5: pears 2
C6: pears 3
C7: oranges 1
C8: oranges 2
C9: oranges 3
C10: bananas 1
C11: bananas 2
C12: bananas 3
C13: grapes 1
C14: grapes 2
C15: grapes 3

Very appreciative to any help on the best way to achieve this.

Best Rgds

Marcus

[Tony Valko]

What version of Excel does this have to work in?

[Tony Valko]

I need in column C: the following:

C1: apples 1
C2: apples 2
C3: apples 3

Would it be OK if you did this...

Data Range

	C	D
1	apples	1
2	apples	2
3	apples	3
4	pears	1
5	pears	2
6	pears	3
7	------	------

[JohnTopley]

Try

=INDIRECT("A" & INT((ROW()-1)/COUNT(B:B))+1) & " " & INDIRECT("B" & MOD((ROW()-1),COUNT(B:B))+1)

[kvsrinivasamurthy]

Try this non volatile version,In C2, then drag down

Please Login or Register  to view this content.

[JohnTopley]

A more "generic" version of kvsrinivasamurthy posting .

=IFERROR(INDEX($A$1:$A$100,INT((ROWS($C$1:$C1)-1)/COUNT(B:B))+1)&INDEX($B$1:$B$100,MOD(ROWS($C$1:$C1)-1,COUNT(B:B))+1),"")

better than my INDIRECT version.