VBA to clear items for bank rec with a unique reference and move to other sheet?

Hi there,

I am new to VBA and now have a bank rec with matching items by the reference but with a different date, therefore the list is always getting bigger.

Is there a easy way to match off items from 2 lists in one tab?

e.g. Date in column A, reference in column B, amount in column C for 1 list
Date in column E, refererence in column F, amount in column G for list comparing the above 2.

The references should be the same.

Ideally it would be nice to match of the items and move them to another tab?

Is this possible? Your help would be much appreciated.

Regards

Hi there,
Yes the references and amounts are the same. If they are not the same, leave as it is, i.e. not remove the reference / amounts to another sheet.
Hope you can help?

Where is the 2nd list shown? Is that to the right of the list 1? We need to know so as not delete items off of list 2 when removing from list 1.

Ie, is it ok to delete the entire row, or do you need to delete and shift data up?

2nd list is on same sheet, i.e. to the right of list 1.
Therefore you would be right to say shift data up.

Regards

Hi there all,
Does anyone have any ideas on how to do the above please?
I am stuck!

Regards

If you upload a sample workbook with before/after format, it will help to us understand what you are really trying to do.

Hi there,
Please see attached for a sample.
So for example, details in cells B104 and C104 match against F and G21 (date being different).
Details in B and C 106 match against F and G22.

Therfore I would want to remove these cells into another tab, i.e. match off.

Regards



I hope this is clear?

Prepare a back up file and try the code.
See if this is how you wanted...

Please Login or Register  to view this content.

Sorry if this is not a good question, but being new to VBA, can you guide how I enter the code into the sheet?

Regards

OK, file attached.

Hi there,
thanks for this. Can I ask how and where you entered the code?
There is a downside though which I hope you can help.
You will have noticed the bottom of the spreadsheet does not align after the cells have been removed.
i.e. headings in A-D do not align with headings in E-H. Is there a way to keep this aligned as well?
If not, maybe a code to highlight all references and amounts that match, or if you can suggest anything better?
Thanks for all your help again,

1) to see the code, hit Alt + F11, if you can not see the code, d-click on Mudule1 on the left payne.

2) That's what I was thinking...

Is there a way around this? i.e keep the headings aligned?

Try replace the code with following and see if this is OK.

Please Login or Register  to view this content.

Seems to have worked perfectly for the sample worksheet.
Thanks so much. Will reply again if problems occur on the actual sheet!
Thanks again

OK, just post back if any problem.

As you have experienced here, if we have a sample workbook to work with, you will normally get the response quicker....