+ Reply to Thread
Results 1 to 3 of 3

Deleting rows (matrix formule)

  1. #1
    Registered User
    Join Date
    02-05-2016
    Location
    Denekamp, Netherlands
    MS-Off Ver
    2010
    Posts
    11

    Deleting rows (matrix formule)

    Hello,

    I hope someone can help me:

    I have a problem with deleting rows
    I have a column with data, witch also contains empty cells, I want all the empty cells filtered out. Filtering is not an option!
    I use the following matrix formule:
    {=ALS(RIJ()>SOM(--(A2:$A$500<>""));"";INDEX(A2:$A$500;KLEINSTE(ALS(A2:$A$500<>"";RIJ(A2:$A$500));RIJ(A2:$A$500))))}

    I think it's this converted to English?:
    {=IF(ROW()>SUM(--(A3:$A$500<>""));"";INDEX(A3:$A$500;SMALLEST(ALS(A3:$A$500<>"";ROW(A3:$A$500));ROW(A3:$A$500))))}
    But it doesn't work for me.
    Does someone have a solution?

    Thanks
    Attached Files Attached Files
    Last edited by martsanders; 02-12-2016 at 08:59 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,207

    Re: Deleting rows (matrix formule)

    Try

    =IFERROR(INDEX(A2:$A$500,SMALL(IF($A$2:$A$500<>"",ROW($A$2:$A$500)-ROW(A2)+1,""),ROWS($A$2:A2))),"")

    Enter with Ctrl+Shift+Enter

  3. #3
    Registered User
    Join Date
    02-05-2016
    Location
    Denekamp, Netherlands
    MS-Off Ver
    2010
    Posts
    11

    Re: Deleting rows (matrix formule)

    JohnTopley,

    Thanks! it works..

+ 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. Rearrange rows in matrix (excel)
    By Pax2Garo in forum Excel General
    Replies: 1
    Last Post: 06-25-2015, 04:44 PM
  2. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  3. Replies: 2
    Last Post: 08-20-2013, 07:52 AM
  4. [SOLVED] Matrix into new rows
    By jennymc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2013, 11:11 AM
  5. [SOLVED] Deleting part of a column without deleting whole rows.
    By dstrdOne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2013, 11:42 AM
  6. deleting matrix value
    By dabrows in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2011, 02:20 PM
  7. matrix columns to rows
    By J_J in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-03-2005, 02:05 AM

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