Matching and moving values to the correct cell

[INNOCENTLES]

Good day ,

I am in finance, and I have to match the payments received in advance with claims made against payment.

for example, guest " A" makes a payment of $5000 in January for a reservation in March which will create a credit to be used in future. They then come in March to enjoy our facilities and it can be for the exact amount or less, in this instance let's say they used the whole 5k . The reports for payments and claims are from separate sources and I have to consolidate them to make a reconciliation to track how much each guest has left on their account. The only thing I can use to make sure I reconcile each claim to the correct Payment is the reservation number.

I need a formula or a VBA that can move the claim value to the payment row line where the claim and the payment have the same exact reservation number. The balance will be the sum of payment column and claim column.
please also factor in that the
thank you

RESERVATION NUMBER	GUEST NAME	PAYMENT	CLAIM	BALANCE
23252563​	A	5,000.00	I need the 5k claim moved to this cell	5,000.00
23652552​	B	3,500.00		3,500.00
23144552​	C	1,500.00		1,500.00
23954445​	D	9,500.00		9,500.00
23669843​	E	6,000.00		6,000.00
23587412​	F	1,200.00		1,200.00
23578951​	G	1,000.00		1,000.00
23655774​	H	3,200.00		3,200.00
23578951​	G		(990.00)	(990.00)
23655774​	H		(3,200.00)	(3,200.00)
23669843​	E		(5,500.00)	(5,500.00)
23587412​	F		(1,200.00)	(1,200.00)
23252563​	A		(5,000.00)	(5,000.00)
23652552​	B		(3,000.00)	(3,000.00)
23144552​	C		(1,500.00)	(1,500.00)
23954445​	D		(9,450.00)	(9,450.00)

***...please also factor in that the values do not have to be duplicated/copied but moved.

thanks

 

[Vogateer]

So you have two separate sheets elsewhere, one with the Payments and one with the voucher-like Claims? Are they tables so that you can use column names for references?

If so, my first thought would be to pull in the separate sheets with Power Query/Get Data, then to create a list of Reservation Numbers from both lists using VSTACK to combine the Reservation columns, UNIQUE to get rid of duplicates, then maybe SORT if you want them in order. Then the Payment and Claim columns are just a matter XLOOKUPs or INDEX-MATCH.

If that doesn't make sense, let me know. I would need more info about the other sheets that have the Payments and Claims on them before I could create useful formulas.

 

[My Aswer Is This]

Your image does not help me.

Is Reservation Number in column A ?

 

[INNOCENTLES]

Hi , thank you for your reply .

Ignoring the part where there's multiple sources of information and just working on the image I provided above can you please do that formula for me .Assuming that columns above are A,B,C,D AND E. THANK YOU

 

[INNOCENTLES]

Your image does not help me.

Is Reservation Number in column A ?

Hi, hope all is well and thanks for the reply. yes, assume that columns above are A, C, D, D AND E. THANK YOU

 

[INNOCENTLES]

Your image does not help me.

Is Reservation Number in column A ?

yes column A

 

[My Aswer Is This]

You said in a previous post:
Quote:
"Please do that formula for me"

I'm really no good at writing difficult formulas.
I mostly deal with Vba
Maybe someone else on the forum will be able to help you. I will keep watching and see what I can learn. Take care and have a nice day.