Results 1 to 4 of 4

Thread: Macro or filter to extract/delete data on value match

  1. #1

    Macro or filter to extract/delete data on value match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Everyone,

    I'll try and explain my problem as best I can.

    Per my attachment (which is a dummy sample of data from a sheet I have of over 3000 lines). I would like to extract data on a 'found' match (between columns B & E) or delete on a 'not' found ...which ever is easier to do.
    The Data in blue is from my system (about 400 lines in my real excel sheet). The data in Green is from a clients (goes down to about 2,500 lines), and this is the data I am trying to extract/delete.

    I have not dealt with macros before but I believe the solution would lie in a script that would
    check (as an example) if E7 is in Column B,
    if ('Not' is found): delete rows including and between C103428(E7) & 'Total'(E9)
    This would then leave the data I would like to use ...or is there a way to group r filter as such?

    Sorry if this is convoluted, but I'm pulling my hair out trying to find a solution. I appreciate any help given.

    Cheers.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I think I'd try to tackle this in the following manner:
    • Split "Your" data to one sheet and "Client" data in another
    • Define a named range to cover "Your" data
    • Put "Client" data starting in column B
    • Fill "Client" data column A with a formula to identify if the item is found or not
    • Use an Autofilter to delete all "not found" rows


    So the trick here would be to figure out what the formula needs to be in order to have it correctly return Found or Not Found.

    Some questions:
    • Is the client number always 6 digits long?
    • Does it always start with a letter followed by 5 numbers?
    • Is there always a Total row in the client data?
    • Is there always a blank row after every total row?
    • Is there always a blank row in row 2?


    Now, I haven't split the sheets up, but if the questions above are all true, you could throu the following formula in D2 and copy down:
    Code:
    =IF(OR(OFFSET(E2,-1,0)="Posting Date",OFFSET(E2,-1,0)=""),IF(ISNA(MATCH(E2,$B$3:$B$11,0)),"Not Found", "Found"),DOFFSET(D2,-1,0)
    That would give you repeating blocks of Found/Not Found. From here you could use Autofilter to filter the "Not Found" records then delete them. (And yes, we could use code to make that automatic.)

    Just in case you're wondering about the formula, I used OFFSET to make the formula a bit more dynamic. This way you can delete the row about and it won't blow up the formula.

    At any rate, let me know if you think that would work and we can look at automating the process.

    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Ken thanks a lot for the massive amount of help. I have been to 5 excel forums so far and you have been one of the only ones to reply and you have given by far the most comprehensive answer.

    I have now been told by the client that I can in fact get all the information on one line which will help me for my short term fix, but I will make note of your solution for a future problem I can see arising.

    Once again your help is much appreciated

    Cheers



    Quote Originally Posted by Ken Puls View Post
    I think I'd try to tackle this in the following manner:
    • Split "Your" data to one sheet and "Client" data in another
    • Define a named range to cover "Your" data
    • Put "Client" data starting in column B
    • Fill "Client" data column A with a formula to identify if the item is found or not
    • Use an Autofilter to delete all "not found" rows


    So the trick here would be to figure out what the formula needs to be in order to have it correctly return Found or Not Found.

    Some questions:
    • Is the client number always 6 digits long?
    • Does it always start with a letter followed by 5 numbers?
    • Is there always a Total row in the client data?
    • Is there always a blank row after every total row?
    • Is there always a blank row in row 2?


    Now, I haven't split the sheets up, but if the questions above are all true, you could throu the following formula in D2 and copy down:
    Code:
    =IF(OR(OFFSET(E2,-1,0)="Posting Date",OFFSET(E2,-1,0)=""),IF(ISNA(MATCH(E2,$B$3:$B$11,0)),"Not Found", "Found"),DOFFSET(D2,-1,0)
    That would give you repeating blocks of Found/Not Found. From here you could use Autofilter to filter the "Not Found" records then delete them. (And yes, we could use code to make that automatic.)

    Just in case you're wondering about the formula, I used OFFSET to make the formula a bit more dynamic. This way you can delete the row about and it won't blow up the formula.

    At any rate, let me know if you think that would work and we can look at automating the process.

    Cheers,

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Not a worry, glad to help!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •