+ Reply to Thread
Results 1 to 6 of 6

Filter rows based on unique values in one column + specific value in another column

  1. #1
    Registered User
    Join Date
    05-12-2011
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Question Filter rows based on unique values in one column + specific value in another column

    Good morning experts,

    I have something like this in sheet1:

    NUMBER STATE last update
    584527 INI 24/06/2013 23:28
    584527 TIP 24/06/2013 23:45
    584527 SFR 25/06/2013 00:40
    584527 END 25/06/2013 02:12
    580715 INI 20/06/2013 20:41
    580715 TIP 20/06/2013 20:57
    580715 TIP 20/06/2013 21:08
    580715 TIP 20/06/2013 21:25
    580715 IPS 20/06/2013 21:35
    580715 END 21/06/2013 00:12
    580715 INI 20/06/2013 20:36
    580715 TIP 20/06/2013 20:59
    580715 TIP 20/06/2013 21:03
    580715 TIP 20/06/2013 21:26
    580715 IPS 20/06/2013 21:39
    580715 END 21/06/2013 00:09
    593649 INI 06/07/2013 08:46
    593649 TIP 06/07/2013 12:30
    593649 IPS 06/07/2013 13:39
    593649 IPS 06/07/2013 16:16
    600436 INI 16/07/2013 14:18
    600436 TIP 16/07/2013 14:33
    600436 SFR 16/07/2013 17:30
    600436 END 16/07/2013 19:47


    What I want is to copy the unique values in column NUMBER, if, and only if, the STATE column isn't END and the column last update has the latest time/hour.

    In this case case it would copy only the bold row - "593649 IPS 06/07/2013 16:16". Since its the only NUMBER without and END and 06/07/2013 16:16 is the last update.

    Is this possible to do in VBA?
    Last edited by Dieneces; 07-31-2013 at 09:56 AM. Reason: solved

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,356

    Re: Filter rows based on unique values in one column + specific value in another column

    Hi Dieneces,
    try it
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-12-2011
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Filter rows based on unique values in one column + specific value in another column

    Man, thanks a lot, worked like a charm! I already tried to understand the Dictionary class but it is a little advance for me, I already changed the script a bit just to had some more columns and change the destination sheet and all worked out great. Many many thanks for your help.

  4. #4
    Registered User
    Join Date
    05-12-2011
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Filter rows based on unique values in one column + specific value in another column

    Quote Originally Posted by Dieneces View Post
    Man, thanks a lot, worked like a charm! I already tried to understand the Dictionary class but it is a little advance for me, I already changed the script a bit just to had some more columns and change the destination sheet and all worked out great. Many many thanks for your help.
    Good afternoon nilem (and all other experts of course ),

    I'm trying to understand your code but I'm still far away from grasping this class... Could you please point me in the right directing to add more columns to this arrays?

    What I was trying to change was the number of columns that would be copied. In this example it copies the first 3 columns, but, after this ones there are a lot more from (A to AB), nevertheless I don't need all this information, I just need, lets say, the first three columns and 3 more (columns 7, 11 and 17 for example)?

    Many thanks for your time,
    Last edited by Dieneces; 07-31-2013 at 09:18 AM.

  5. #5
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,356

    Re: Filter rows based on unique values in one column + specific value in another column

    Good evening Dieneces,
    try it
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-12-2011
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Filter rows based on unique values in one column + specific value in another column

    Nevermind,

    Today with a good night of sleep I tried again and all made sense. Just changed the t(0) = with the other needed columns and added them in the If InStr(.Item(k)(0), "END") = 0 also.

    Sorry to pull this threat up...

+ 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] Counting # of specific values in one column for each unique IP in another column
    By Light C in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 06-04-2013, 11:38 AM
  2. Replies: 19
    Last Post: 04-17-2013, 09:51 PM
  3. How to filter a column in a table based on a set of values in another column
    By redhawk87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 06:24 PM
  4. [SOLVED] Edit Recorded Macro to filter column with about 200k rows based on a list of values.
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-19-2012, 03:35 AM
  5. Replies: 3
    Last Post: 11-24-2008, 01:10 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