+ Reply to Thread
Results 1 to 5 of 5

Data advanced filtering

  1. #1
    Registered User
    Join Date
    08-28-2013
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    3

    Smile Data advanced filtering

    Hello all!

    I'm a new user here and also relatively new with VBA in Excel. So I am facing a problem that seems to be easy but is really "stealing" my time lately. To the point...
    I have 6 columns of data:
    WF WT F FA ST ET
    A 1 10 30
    A 1 10 30
    A 2 0 50
    A 2 0 50
    B 1 5 40
    B 3 10 30
    B 3 10 30
    ... ... ... ...

    Columns F and FA also contain data but we're not gonna use them in this case. What I want to do is to keep unique values from columns WF and WT (or better, unique combinations of the two columns) but also display in the result the values of ST and ET for the first unique combination it has found. So, the expected result would be:
    WF WT ST ET
    A 1 10 30
    A 2 0 50
    B 1 5 40
    B 3 10 30

    I have tried the AdvancedFilter application but since my only criteria is unique values for specific columns it's getting tricky!

    Thanks in advance!!!
    Last edited by Chris_-_; 08-29-2013 at 04:18 AM. Reason: Mistake

  2. #2
    Registered User
    Join Date
    08-28-2013
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Data advanced filtering

    Well, I'm realizing there are some formatting issues. So, the values (A,B,C...) belong to WF, (1,2,3...) to WT, (0,5,10) to ST and (30,40,50..) to ET!

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Data advanced filtering

    Hi,

    if i understand your problem correctly it should be doable with advancedfilter - dunno how skillful you´re with that feature but you can use multiple criteria with it (with the AND / OR property) and the windth of the outcome is solemnly aswell in your hands, might need a sample to give you step-by-step guidance (also to try it out, its been a while i used AF)

    Best regards

    Soul

  4. #4
    Registered User
    Join Date
    08-28-2013
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Data advanced filtering

    Hmmm...I cannot see it being solved with advanced filter only. My only criteria is unique combinations of columns WF and WT, that's doable. What I cannot do is to get the values of columns ST and ET, because for these columns I have no criteria. I just wanna get the data from them for the first unique combination no matter if they are unique or not.
    Anyway, I kind of solved my problem for now but I suppose there is a faster way. What I have done is: Filter only the first 2 columns and copy them to another sheet, then I have created a loop in VBA that takes the unique combinations, finds them in the original database, picks the values from the last 2 columns and exits the loop.

    Please Login or Register  to view this content.
    Worksheets("1").Range("B:C").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="" _
    , CopyToRange:=Worksheets("2").Range("A1:B100"), Unique:=True

    For i = 2 To Worksheets("2").Cells(Rows.Count, "A").End(xlUp).Row
    WFUNIQUE(i) = Worksheets("2").Cells(i, 1)
    WTUNIQUE(i) = Worksheets("2").Cells(i, 2)
    Next i

    For j = 2 To Worksheets("2").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Worksheets("1").Cells(Rows.Count, "A").End(xlUp).Row
    If WFUNIQUE(j) = WF(i) And WTUNIQUE(j) = WT(i) Then
    Worksheets("2").Cells(j, 3) = ST(i)
    Worksheets("2").Cells(j, 4) = ET(i)
    Exit For
    End if
    Next i
    Next j

    Well, if there is nothing faster to suggest let's consider it solved ;-)

  5. #5
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: Data advanced filtering

    As I´ve said, its hard to tell you how to do it if i actualy dont have the sample, but to give you at least an idea maybe for next time:

    if the variance in wf and wt is not insane - like there is xx.xxx uniq possible combination etc. than you can use the kritera for the AF where you place these uniq combination in two columns (lets say A1= A; B1=1 -- this make the uniq comb. no.1 if you fill these column in this patern - ofc that could be done with formula or macro but as for now, i cant tell that of the bat since im used to work with real data rather than speculations) and u use this are the criteria range for the AF and as for the outcome, you copy& paste in new sheet the header which you desire as to be your output and the AF do the trick (also, name the input area so you can fill the first field of AF just with the name, cause you need to stand in the sheet !-WHERE-! you gonna be pasting)

    and as for the uniq combo kriteria creation, I´m not that much skilled of how to make excel count instead of in numbers (1,2,3...) in alphabet (a,b,c....) but anyways i suppose there is a way to tell him to do that and than there would be absolutely no problem to make a loop to create such a range

    EDIT: typo

    &

    INFO: if you use cells next to each other in the AF criteria it means it has to fit both cells to be considered true and put that to outcome; if you place em beneath each other its same as OR function
    Last edited by SoulPrisoner; 08-30-2013 at 04:56 AM. Reason: typo & add info

+ 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 with Advanced Filtering/Data Formatting
    By Benrenicus in forum Excel General
    Replies: 3
    Last Post: 06-07-2011, 11:23 AM
  2. Replies: 3
    Last Post: 05-15-2011, 11:46 AM
  3. Replies: 2
    Last Post: 01-24-2011, 08:35 PM
  4. Advanced filtering & updating data
    By alpruett in forum Excel General
    Replies: 2
    Last Post: 09-02-2009, 02:23 PM
  5. Extraction of data - Advanced Filtering
    By Harlequin in forum Excel General
    Replies: 2
    Last Post: 02-22-2007, 10:40 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