+ Reply to Thread
Results 1 to 3 of 3

Format or List Every nth Non-Blank Cell in a Range

  1. 09-20-2012, 10:39 AM #1
    oxv
    oxv is offline
    Registered User
    Join Date
    08-02-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    9

    Format or List Every nth Non-Blank Cell in a Range

    I have a few thousand lines of data in a column and interspersed in the data are blank cells (I cannot sort, as I’d lose relationship with data in other columns).

    257927
    Blank
    Blank
    257928
    Blank
    Blank
    257930
    Blank
    Blank
    Blank
    257932
    Blank
    257937
    Blank
    257940
    Blank
    257956

    I need to identify a sample. e.g. Format yellow or list every 300th non-blank cell in the range.

    I have tried Sampling from the Analysis ToolPak add-in, but that gives me every nth cell – blanks included. I have tried Conditional Formatting, with a few variations of COUNTIFS, but have failed here as well.

    Can someone please shove me in the right direction?
    Reply With QuoteRegister To Reply
  2. 09-20-2012, 11:22 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Format or List Every nth Non-Blank Cell in a Range

    try adding a helper column, and add this formula to B2, copied down (enter a 1 into B1 as a seed), assuming your data starts in A1

    =IF(A2="",B1,B1+1)

    you can then use CF (or other functions) to identify/highlight/extract every 300th value
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 09-20-2012, 11:30 AM #3
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Format or List Every nth Non-Blank Cell in a Range

    hi oxv, assuming your data is in Column A, use this in the conditional formatting:
    =AND(COUNTIF($A$1:$A1,"<>"&"")=300,$A1<>"")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1