Excel help for Reconciliation

[mariothefool]

I was wondering if there is a formula that I could use to look up a date and match that date and amount against another set of data?

I am simply doing a reconciliation and need to compare that one set of data is in another and the only information I can look up against is Date and Amount. I am trying to find out which transactions are missing using a formula.

Thanks for the help.

Mike

[etaf]

do you have a sample of how the data is structured
i would assume multiple entries for the same dates?
maybe a sumifs() might work to compare

[mariothefool]

They both have a Date, debit and credit. I am trying to find out which ones are missing and not on both.

I was trying to create a unique number by using ABS(Debit + Credit columns) so I could use the unique number to count the ones not matched in a pivot table, but it doesn't seem to be working.

[mariothefool]

An example would be:

In G.L

Date Debit Credit
1/1/2020 0 25

On Statement Debit Credit
Date 1/1/2020 25 0

I am trying to create a reference number that could match these up and find ones that aren't matched. I am not sure what formula to create a reference number that will match these both so if I filter or use a pivot table, I can use a count formula to show the ones matched.

Thanks!

[etaf]

i assume you would also have
Date Debit Credit
1/1/2020 0 25
1/1/2020 15 0
1/1/2020 10 0
1/1/2020 0 15
1/1/2020 0 10


On Statement Debit Credit
Date
1/1/2020 25 0
1/1/2020 0 15
1/1/2020 0 10
1/1/2020 15 0
1/1/2020 10 0

If you attach a sample spreadsheet

[mariothefool]

how do I attach a sample spreadsheet? I don't see a way to attach a file. Thanks in advance.

[etaf]

should be shown how on a yellow bar across the top of the thread

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.