14 Comments Add yours

  1. Vishwa says:

    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

  2. maksood alam says:

    this formula is giving error in indirect fucntion

    1. DR Matt says:

      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)

  3. Elias says:

    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

    1. Elias says:

      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

  4. Paolo says:

    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

  5. 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))

  6. Elias says:

    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

  7. Tatyana Zakharenko says:

    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)))

  8. Divaker says:

    =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)))))

  9. Deepak says:

    Data Data Formula Answer
    1 A =A2&B2 1A
    2 B =A3&B3 2B
    3 C =A4&B4 3C
    4 D =A5&B5 4D

  10. Abhi says:

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

  11. JM says:

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

  12. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s