Matching transactions using formulas [Accounting]-1

[subhashuttur]

Dear All,

I have a reconciling task to match one value with many values. I checked its been nicely explained on another thread "Matching transactions using formulas [Accounting]" but the data i get will need match between two columns (B & C) or within the same column (B or C). any luck to get this done with formula or code ?

regards,
subhash

 

[subhashuttur]

pls let me know if anybody got a way on it

 

[NARAYANK991]

Hi ,

I doubt that what you want can be done , if there are no constraints on the number of items that can be combined to get the amount e.g. :

-68.67 matches another single amount 68.67

-300.30 matches the combination of two other amounts 100 and 200.30

-150 matches the combination of three other amounts 50 , 50 and 50.

Narayan

 

[subhashuttur]

Thanks for looking into this Narayan,

Indeed, thats what the problem with the data, there is no constraint. if you know this can be done with any way, pls let me know.

Thanks
subhas

 

[Marc L]

Hi !

With another criteria like an operation ID it would be easier …
​

 

[subhashuttur]

Thanks Marc, i think you are suggesting to have some unique ID, if so pls suggest how it can help to find the matches...

thanks
subhas

 

[NARAYANK991]

Hi ,

What Marc meant was that if the amount -150 , for instance , had an ID , and the 3 amounts 50 , 50 , 50 all had this same ID , then matching the ID would automatically match the amounts. Matching the ID could be done using native Excel worksheet functions.

As an example if all the payments referred to the relevant invoice , then matching the payments with the invoice would be straightforward.

Narayan

 

[Marc L]

i think you are suggesting to have some unique ID, if so pls suggest how it can help to find the matches...

See with the accountant as it is some accountancy basics :
the ID may be the invoice number for example
as yet suggested by Narayan …
​

 

[subhashuttur]

OK, in my case invoice nr some time will be different. In both the system (SAP vs Local) they may use their own invoice nrs & there is no linking aspects (like unique ID). So i have to find a way to match one nr with multiple nrs...

Thanks,
subhas

 

[Marc L]

In my country, two different systems of invoice numbers is forbidden …

Instead of an invoice number, ID may be the original invoice date …
​

 

[subhashuttur]

i will have to bring such consistencies, until implementation i have to do manual reconcile....