Hi Guys.

Enjoy the new formula challenge 🙂

suppose you have data in A and Column b like

1 A

2 B

3 C

4

you just need to create a Excel Formula to get output below :

1A

1B

1C

2A

2B

2C

3A

3B

3C

4A

4B

4C

Enjoy

Rajan.

%d bloggers like this:

Here you go ,

something are constant here like 0.33 ….

=INDEX($A$1:$A$4,CEILING(ROW(INDIRECT(“1:12”))*0.33,1),1)&INDEX($B$1:$B$4,INDEX(MOD(ROW(INDIRECT(“3:14”)),3)+1,ROW(INDIRECT(“1:14”)),1),1)

nice one keep posting

this formula is giving error in indirect fucntion

I made a small modification to the formula (got rid of the first two INDIRECTS) that seems to work:

=INDEX($A$1:$A$4,CEILING(ROW(12:23)*0.33,1),1)&INDEX($B$1:$B$4,INDEX(MOD(ROW((14:25)),3)+1,ROW(INDIRECT(“1:14”)),1),1)

rA=A1:A4

rB=B1:B4

Formula in C1 and copy down.

=INDEX(rA,INT(ROWS(C$1:C1)/COUNTA(rB))+1)&INDEX(rB,MOD(ROWS(C$1:C1)-1,COUNTA(rB))+1)

Regards

Thanks to Prem from LinkedIN Excel and VBA users group that point out that a slight modification on my formula is needed. From INT(ROWS(C$1:C1) to (INT(ROWS(C$1:C1)-1).

=INDEX(rA,INT((ROWS(C$1:C1)-1)/COUNTA(rB))+1)&INDEX(rB,MOD(ROWS(C$1:C1)-1,COUNTA(rB))+1)

Regards

Hi,

if list 1 starts in A1 and list 2 starts in B1 you can use the below formula:

=IF(ROW()<=COUNTA(A:A)*COUNTA(B:B),INDIRECT("A"&CEILING(ROW()/COUNTA(B:B),1)),"")&IF(ROW()<=COUNTA(A:A)*COUNTA(B:B),INDIRECT("B"&(ROW()-(CEILING(ROW()/COUNTA(B:B),1)-1)*COUNTA(B:B))),"")

Best,

Paolo

My Solution is :

=INDEX($A$1:$A$8,CEILING(ROW(INDIRECT(“1:”&AllCount))/Col2Count,1))&INDEX($B$1:$B$4,IF(MOD(-Col2Count+ROW(INDIRECT(“1:”&AllCount)),Col2Count),MOD(-Col2Count+ROW(INDIRECT(“1:”&AllCount)),Col2Count),Col2Count))

What will be your solution if records in column A are not numbers, the numbers of records is variable and not always start in row 1?

Regards

My solution:

IF(ROUNDUP (ROW (B1) / COUNTA(B:B);0)> COUNTA (A:A); “”; ROUNDUP (ROW (B1) / COUNTA (B:B);0) & INDIRECT (“b”& ROW (B1) – ROUNDUP (ROW (B1) / COUNTA (B:B);0)* COUNTA (B:B) + COUNTA (B:B)))

=CONCATENATE(INDIRECT(“A”&IF(MOD(ROW(),COUNTA(A:A))=0,COUNTA(A:A),MOD(ROW(),COUNTA(A:A)))),INDIRECT(“B”&IF(MOD(ROW(),COUNTA(B:B))=0,COUNTA(B:B),MOD(ROW(),COUNTA(B:B)))))

Data Data Formula Answer

1 A =A2&B2 1A

2 B =A3&B3 2B

3 C =A4&B4 3C

4 D =A5&B5 4D

Should a simple ‘&’ formula work?? Having the numbers constant and then just dragging the formula??

=OFFSET($A$1,INT((ROW()-1)/COUNTA($B:$B)),0,1,1)&OFFSET($B$1,MOD(ROW()-1,COUNTA($B:$B)),0,1,1)

I think I nailed it with the most graceful solution:

=INT((ROW()+2)/3)&INDEX($B$1:$B$3,MOD(ROW()+2,3)+1)

It’s an array formula so:

Place it somewhere in row 1 >>> Ctrl+Shift+Enter >>> Pull down to row 12

Note that it can refer to any 3 consecutive cells in column B AND it doesn’t require reference to numbers in column A. The formula must be located in row 1 though.