+ Reply to Thread
Results 1 to 4 of 4

non consecutive to consecutive to cells

  1. #1
    Registered User
    Join Date
    10-08-2013
    Location
    Québec
    MS-Off Ver
    Excel 2002
    Posts
    14

    non consecutive to consecutive to cells

    Hello

    In column A I have non consecutive cells, in column B I want them to be consecutive.
    I can't use VBA for this project , is there a way with array formula to do so ?


    Thanks

    A B
    1 Banana Banana
    2 Strawberry
    3 Strawberry Strawberry
    4 Strawberry Orange
    5 Orange Orange
    6 Orange
    7
    8 Orange
    9
    10 Orange

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: non consecutive to consecutive to cells

    A
    B
    C
    1
    Banana Banana B1: {=INDEX($A$1:$A$10, SMALL(IF($A$1:$A$10 <> "", ROW($A$1:$A$10)), ROWS(B$1:B1)))}
    2
    Strawberry
    3
    Strawberry Strawberry
    4
    Strawberry Orange
    5
    Orange Orange
    6
    Orange
    7
    #NUM!
    8
    Orange
    9
    10
    Orange
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-08-2013
    Location
    Québec
    MS-Off Ver
    Excel 2002
    Posts
    14

    Re: non consecutive to consecutive to cells

    Thanks, I didn't know the SMALL() function

    It works perfectly

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: non consecutive to consecutive to cells

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 05-29-2013, 10:35 AM
  2. Replies: 10
    Last Post: 02-27-2013, 02:05 PM
  3. Replies: 6
    Last Post: 04-28-2012, 08:50 AM
  4. Replies: 6
    Last Post: 03-23-2012, 06:03 PM
  5. Excel macro to "fill in" consecutive numbers in non-consecutive list?
    By Tomkat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2009, 01:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1