+ Reply to Thread
Results 1 to 3 of 3

Partial matching over several columns

  1. #1
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Partial matching over several columns

    Hi Guys
    I have almost no idea as to how to start matching these columns.
    I have two tables. One is a set of data from PayPal and the other is a set of payments from a credit card. I simply want to match one against the other so I can reconcile the payments.
    I have uploaded a workbook with two sheets “What I have” and “What I want”. These are from other sheets. I have created the matches manually by sorting. Two tables A:F PayPal and H:O Barclaycard. Columns to match are
    Total paid to Debit amount
    Date to Transaction date
    Name PayPal to Name Barclaycard (These are the truncated)

    Also there are far more Barclay card payments than PayPal. Needless to say I am searching for an answer. There may be occasions when there are legitimately two payment to the same party on the same day. PayPal has time if that would be needed. I had some idea that the search would move the date as it found it so therefore the next search would not see what has been moved.
    I hope I have asked this question clearly.
    Thanks in advance.
    Mark
    PS I have been trying all sorts of solution found getting close but it might take me a few days. Could manually match it in an hour but that’s not the point. Thanks again
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Partial matching over several columns

    So I think you're going to have a hard time getting a decent solution because the set of rules to make the matches (the algorithm) isn't well defined at all. What I mean is that it looks like it takes some actual intelligence to make these pairs.

    The two best approaches I know of are Range.Find with xlPartial, and Like, both using wildcard characters.

    However, I don't think either will deliver the results you expect. Let's look at a couple approaches:
    1. Take the first 3 or four characters from the Barclay list and match them against the first 3 or 4 characters in the paypal list. Downside: false positives when EssexHome is paired with Essential Goods.
    2. Remove all the spaces and caps from the paypal list and do direct comparisons between the two lists. Downsides: Not all the items in the Barclay list are the same as the paypal list, even though they are pairs. Ex: Sealstools and sealants and tools direct, Toolbox and The Toolbox
    3. Try to use Like with wildcards. Downside: The wildcards could go anywhere. To match Toolbox with The Toolbox, wildcard goes at the beginning. To match Imd Trading with IMD Trading LTD, wildcard goes at the end. To match Land Trac with Land-trac, wildcard goes in the middle.


    There are surely more approaches, but you get the idea.

    Moreover, it's not clear to me at all why some got paired. Why are "To US Dollar" and "Bicwarehous" paired? How would the program know to pair them.

    I'm not saying this task is impossible, just that you would need a huge algorithm to even attempt to capture all of the varying ways that items on the lists can be paired. You're using intelligence to do it, which the program wouldn't have.

    If there is a well defined method for making pairs, describe it in pseudo-code and I can try to translate it to code. I just don't see it, though. Good luck.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  3. #3
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Partial matching over several columns

    Hi Walruesggman
    My day research has come to the same conclusion. The example you mentioned I just remembered was correct. And others are as equally crazy. What I am looking at now is to somehow reduce the numbers that require manual linking. I have come accross some seriously complicated stuff. Why do the banks have to "" it all up. Surely they could like PayPal use the actual company name unless they are still using Lotus.
    Thanks for your thoughts.

    Mark
    PS hope its a great day in Texas. her in London its sunny but the Autumn is closing in.

+ 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. Matching partial data help
    By Danwxm in forum Excel General
    Replies: 1
    Last Post: 03-28-2014, 11:30 AM
  2. [SOLVED] Matching with partial text
    By grkchakri in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2012, 03:33 PM
  3. Partial matching two columns of data?
    By tmedia in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-23-2012, 06:42 PM
  4. Partial matching two columns of data
    By tmedia in forum Excel General
    Replies: 0
    Last Post: 02-08-2012, 06:45 PM
  5. matching partial text within one cell to partial text within another
    By Solstice in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-03-2010, 09:13 PM
  6. Bank reconciliation - Many to one matching and partial cell matching
    By maartendelaet in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-25-2010, 08:21 PM
  7. Matching Partial data in columns
    By ap_naveen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2007, 05:57 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