+ Reply to Thread
Results 1 to 9 of 9

find a column by date and filter that column by removing blanks

  1. #1
    Registered User
    Join Date
    11-17-2011
    Location
    Sri lanka
    MS-Off Ver
    Excel 2003
    Posts
    34

    find a column by date and filter that column by removing blanks

    hello,

    i have attached relevant excel sheet with this.
    what i want to do is find the previous working date from a column and filter that column by removing blanks
    as an example
    if it's 22-Mar today, i need to filter the Kth column(relevant to the 21-Mar) and remove the blanks from kth column .
    ash color columns are holidays.so they should be ignored,that means
    if today is 27-Mar, i need to filter the Mth column relevant to the 22-Mar(i don't need to filter those ash colored columns )

    I'd prefer to this using a macro.but i have no idea how to do it
    could you please help me to to this task

    thanks very much
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-17-2011
    Location
    Sri lanka
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: find a column by date and filter that column by removing blanks

    currently I'm doing this by applying filters to 2nd row and then uncheck the blank box in previous day filter.
    i want to automate this task by a macro
    if you have any idea please let me know
    thanks

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: find a column by date and filter that column by removing blanks

    this is what i can come up with, doing recording and a little coding. see if it helps:

    Please Login or Register  to view this content.
    if you encounter a problem, upload the file & state where's the error, what it should be, & why so

    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

  4. #4
    Registered User
    Join Date
    11-17-2011
    Location
    Sri lanka
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: find a column by date and filter that column by removing blanks

    Quote Originally Posted by benishiryo View Post
    this is what i can come up with, doing recording and a little coding. see if it helps:

    Please Login or Register  to view this content.
    if you encounter a problem, upload the file & state where's the error, what it should be, & why so


    thanks very much beni.
    i tried the macro but it gives me a error message.i ran the macro step by step and noticed that it find the correct column relevant to today's date.but it doesn't filter the previous date column correctly.
    i think it's impotent to attach actual workbook for your convenient.
    in sheet 1 shows the actual work sheet.
    in sheet 2 shows the expected sheet if i run this macro on 27th March
    in sheet 3 shows the expected sheet if i run this macro on 28th march.

    i think macro code should run according to the following procedure.

    step 1: find the cell which contain yesterday date(today-1) from Y2:BD2 (although ending column may very,it does not exceed BD3 column )
    step 2:find the color of cell below to yesterday date cell
    if it's white
    remove(filter) the blank cells from that column
    else
    go to the cell which contain day before yesterday(today-2)

    run this loop until find a white column and filter it

    i tried to write a macro program to this procedure.but it's difficult to me.
    i think now you got the idea about my problem.
    if you need any clarification let me know.

    thanks very much
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-17-2011
    Location
    Sri lanka
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: find a column by date and filter that column by removing blanks

    hi beni.
    i tried the code with TdyX = Date-1 and now it seems to work properly .
    but when i try it with actual sheet(which attached in previous comment) it find a another cell for today date because there are several cells contain today or yesterday date.
    so i need to find yesterday date from 2nd row (or from Y2:BD2 range).
    can you please help me to edit the code so that it will work with actual excel sheet
    thanks

  6. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: find a column by date and filter that column by removing blanks

    2 new assumptions i added here.
    1) filter starts from column A instead of column E
    2) there are dates existing outside row 2.

    Please Login or Register  to view this content.
    hope that helps

  7. #7
    Registered User
    Join Date
    11-17-2011
    Location
    Sri lanka
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: find a column by date and filter that column by removing blanks

    Quote Originally Posted by benishiryo View Post
    2 new assumptions i added here.
    1) filter starts from column A instead of column E
    2) there are dates existing outside row 2.

    Please Login or Register  to view this content.
    hope that helps

    Hi beni,
    it works perfectly
    i'm working with a excel project these days and difficult parts done with your help and only few parts remaining.
    today is my B'day and you made my day by solving the problem.
    hope you will help me future also
    thanks a million

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: find a column by date and filter that column by removing blanks

    a very happy birthday to you, rsami! glad to be able to help

    please help to mark the thread as "Solved". also, try not to quote the whole post if unnecessary. thanks
    =)

  9. #9
    Registered User
    Join Date
    11-17-2011
    Location
    Sri lanka
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: find a column by date and filter that column by removing blanks

    thanks very much beni
    i'll mark it as solved.
    rep+

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1