Scenario is download csv transaction data from a bank account and (say) a credit card for a period of time.
There are payments from the bank account to the credit card every now and then.
Sometimes the payments appear in the credit card csv dated the same day, often not and dated a day or two days later.
Both csv files import/open directly into Excel with no problem.
There are payment references in both, but none of these match, ever.
I could list the things I have tried, but do not want to limit the inspiration of possible answers.
I have struggled with this problem for many years.
There are various similarities between the transactions to be matched, for example the amounts are the same, the dates are close to each other when not the same.
I am interested to hear any ideas to solve this intractable problem.
Second level solution involves adding a column to each csv and manually adding some sort of index column - I have been thinking of giving each relevant payment a numeric sequence within its month, so payment 01 02 03 etc which IF these are coming from only one source which is also tagged as such, might work - but I get stuck with what to do when a month end is interspersed between payment out by bank and payment in date by credit card - I suppose I could make the index sequence annual.
Current method is, with both csv files loaded into one sheet, as one table, payment in are negative, create an abs(olute) value column and sort on that, which draws together all the similar amounts, which at this point are "so close but so far" from being tallied each with their partners. I quote this just to open a door to additional columns, sorting (ordering), formulae and so on, as well as the csv files being combined into one with column heads (fields) suitably matched.
CONCLUSION to my question
I am not looking for a VBA solution.
I think this is more of an Excel (or even Access) logic problem.
However, for me it is a long time problem I have always had to solve manually, using the human brain provided to me.
My first real question ever and why I have signed up to Experts Exchange, I have this question and one other similar, but the other one is more complicated as the amounts do not match so it can wait.
This first post is aimed at those who do not need examples of the data to understand the problem - which may be more about indexing than anything else.
I have simplified the problem, which in real life involves multiple accounts cross paying each other, and paypal which is another kettle of fish.
I have looked for cross references within each source but never found any.
Having in the past seen the level of genius illustrated in here I will not be surprised to be amazed.
Speed of answer is NOT of the essence here.