+ Reply to Thread
Results 1 to 6 of 6

Removing specific elements from an Array based on matching elements in a different Array

  1. #1
    Registered User
    Join Date
    05-26-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    49

    Removing specific elements from an Array based on matching elements in a different Array

    I have a 2 column Main Array with a string of specific numbers in each column. The Main Array is very long > 100,000 rows. I need to remove specific rows from this Main Array. The specific rows I need to remove are in a "Bad" Array with about 200 rows. The "Bad Array" also contains 2 columns. So if a specific Column A, Column B element from Bad Array match the same specific Column A, Column B element from Main Array, then that row needs to be deleted from the Main Array. That's one part of it. But there is a second part. The code also needs to compare that same row in the Bad Array but this time Column B, Column A against the Main Array and if there is a match then delete that row wherever it exists from the Main Array as well. There should be 2 deletions in the Main Array for each row in the Bad Array. Column A, Column B Deletion....and Column B, Column A Deletion

    For example...
    My Main Array
    123124, 255643
    845814, 584894
    845814, 684948
    203049, 483949
    346446, 694506
    904392, 494078
    293938, 636320
    684948, 845814
    636320, 293398

    Bad Array with Rows that need to be removed from the main array
    845814, 684948
    293398, 636320

    My code needs to delete from the Main Array...
    845814, 684948
    684948, 845814
    293398, 636320
    636320, 233398

    Ultimately resulting in a final Main Array that looks like this...
    Final Main Array
    123124, 255643
    845814, 584894
    203049, 483949
    346446, 694506
    904392, 494078

    I am not great at coding. I wasn't sure whether to "walk" down the Main Array or the Bad Array. My hope is that experienced programmers have seen this before and might have some code that can help me. I hope my explanation was clear. Thank you very much for your help.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Removing specific elements from an Array based on matching elements in a different Arr

    Here you go:

    Please Login or Register  to view this content.
    Change worksheet / column references as necessary
    Last edited by Olly; 01-24-2014 at 04:18 PM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    05-26-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Removing specific elements from an Array based on matching elements in a different Arr

    OllyXLS...BreakingBad...you're funny. I'll check this out and walk through it. If I have questions I will let you know. Thank you for what you have done thus far. I really appreciate the prompt response.

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Removing specific elements from an Array based on matching elements in a different Arr

    It was too good a pun to miss!

    Would appreciate feedback from live testing. Does what it should on your sample data, so hoping it works for you. Nice to work with clearly explained requirements

  5. #5
    Registered User
    Join Date
    05-26-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Removing specific elements from an Array based on matching elements in a different Arr

    Olly...your code is PERFECT and exactly what I needed it to do! (Which I am sure you already knew but I had to slowly walk through it). Thank you very very much!

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Removing specific elements from an Array based on matching elements in a different Arr

    Quote Originally Posted by jamesdeuce View Post
    Olly...your code is PERFECT and exactly what I needed it to do! (Which I am sure you already knew but I had to slowly walk through it). Thank you very very much!
    Splendid Thanks for the feedback

+ 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. help assigning elements of dictionary to array and then pasting array into worksheet
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-05-2013, 10:37 AM
  2. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM
  3. Refer to specific elements in an array
    By MCCCLXXXV in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-13-2009, 02:31 PM
  4. Removing array elements
    By panthers7171 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2009, 10:26 AM
  5. Resize array based on number of 'used' elements
    By John in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2006, 02:30 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