+ Reply to Thread
Results 1 to 2 of 2

Count number of columns between first and last data in each row

  1. #1
    Registered User
    Join Date
    03-26-2017
    Location
    London
    MS-Off Ver
    13
    Posts
    23

    Count number of columns between first and last data in each row

    I have years across the top and a lot of rows (thousands) with data for different IDs. The IDs don't have data for every year and each ID has data for different years.

    ID 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
    1 53 64 82 79
    2 48 61 74
    3 42 48
    4 34 19 34 55 52 72 93
    5 26 40 118



    I need to count the differences in years between the first and last data points. So for ID it would be 3 years (2010- 2013). Because each year is in a column I can also use a formula which counts have many columns there are if thats easier.

    This would take such a long time to do it manually so I wondered if there is a solution?
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count number of columns between first and last data in each row

    Hi,

    In M2:

    =MMULT(AGGREGATE({14,15},6,COLUMN(B2:L2)/(B2:L2<>""),1),{1;-1})

    Copy down as required.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ 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: 2
    Last Post: 01-27-2017, 11:13 AM
  2. [SOLVED] Count number of data between two columns
    By bhenlee in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-29-2016, 10:14 AM
  3. count columns with data then insert that number of rows
    By johnson1421 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-12-2014, 06:54 PM
  4. [SOLVED] count number of pivot columns
    By MarMo in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-01-2013, 08:52 AM
  5. compare columns and count number of matching columns
    By san000 in forum Excel General
    Replies: 1
    Last Post: 07-15-2009, 10:31 PM
  6. VBA to count number of columns
    By blucky in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-13-2008, 09:20 AM
  7. Count Number of Columns
    By mrdata in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-03-2007, 04:44 PM

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