14 Comments Add yours

  1. Vishwa says:

    Here you go ,
    something are constant here like 0.33 ….


    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:


  3. Elias says:


    Formula in C1 and copy down.



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



  4. Paolo says:


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




  5. My Solution is :

  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?


  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:


  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:


  12. I think I nailed it with the most graceful solution:

    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