+ Reply to Thread
Results 1 to 5 of 5

Formula to automatically consolidate list that's changing

  1. #1
    Registered User
    Join Date
    09-03-2015
    Location
    Yorkshire
    MS-Off Ver
    2016 32bit
    Posts
    43

    Formula to automatically consolidate list that's changing

    Hi all,

    Been trying to automate some data summary things I have to do.

    I look for unique HAZXXX numbers, and show the unique DEXXX numbers next to them.

    I've done all of it! - Except for the list I create has gaps in it.

    Anyway to automatically remove the list gaps in the summary section of the attached spreadsheet?
    Cheers!

    TinyDamooge
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,006

    Re: Formula to automatically consolidate list that's changing

    An array formula in C3, copied across and down:

    =IFERROR(INDEX(C:C,SMALL(IF(C$17:C$265<>0,ROW(C$17:C$265)),ROWS($1:1))),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,006

    Re: Formula to automatically consolidate list that's changing

    or... an ordinary formula, coped across and down:

    =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$17:C$265)/(C$17:C$265<>0),ROWS($1:1))),"")
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-03-2015
    Location
    Yorkshire
    MS-Off Ver
    2016 32bit
    Posts
    43

    Re: Formula to automatically consolidate list that's changing

    That works great thanks!

    Cheers for removing the middle steps I'd been trying too!

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,006

    Re: Formula to automatically consolidate list that's changing

    You're welcome and thanks for the rep.

+ 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. [SOLVED] Help needed with formula to consolidate data for mailmerge email list
    By stitchlipped in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2016, 06:08 AM
  2. [SOLVED] Need a formula to consolidate a 2 column list
    By mhedge in forum Excel General
    Replies: 8
    Last Post: 08-15-2014, 08:44 PM
  3. Replies: 6
    Last Post: 11-28-2013, 11:54 AM
  4. [SOLVED] Cells will become 0 automatically without changing formula
    By redza in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-25-2013, 02:16 AM
  5. Replies: 4
    Last Post: 05-17-2011, 06:07 PM
  6. Changing the value of a dropdown list automatically
    By Richmate in forum Excel General
    Replies: 3
    Last Post: 06-11-2010, 04:07 AM
  7. Replies: 2
    Last Post: 04-14-2009, 02:40 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