Compare, Match and Copy based upon 2 variables

G

goobee

New Member
Joined
Feb 25, 2011
Messages
26
We are looking for an easier way to automate matching payments from customers. Checks that include invoice numbers are easily matched, those are not the problem. It is for the payments that are received without invoice numbers that require manual review to determine where the money should be allocated to.

Below, I've included 2 sample tables, one that contains the orders and another that contains the payments received. Although Customers Numbers are common on both tables, simply matching the Customer Numbers do not work as customers can have more than one order and can have more than one check received. (Tried using Vlookup which did not work due to multiple instances of Customer Numbers and multiple instances of same dollar values. Index & Match didn't work either)

Ideally, we would a macro that can do these things:


  • Compare and match Customer Numbers
  • If Customer Number match is found, compare and match dollar amounts
  • If dollar matches found, copy Check Amounts and Check Numbers to output report (see output report)
  • If no matches found, enter "Not Found"

I imagine this may be too complicated for macro to handle but it doesn't hurt to ask.

Thanks in advance.

Customer Orders Table

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="width: 88"]Customer No[/TD]
[TD="width: 107"]Invoice Number[/TD]
[TD="width: 117"]Purchase Amount[/TD]
[/TR]
[TR]
[TD="align: right"]15935[/TD]
[TD]07213588-1[/TD]
[TD] 5,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]142559[/TD]
[TD]09559387-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]185735[/TD]
[TD]09592476-1[/TD]
[TD] 8,581.00[/TD]
[/TR]
[TR]
[TD="align: right"]196899[/TD]
[TD]07355651-1[/TD]
[TD] 9,200.00[/TD]
[/TR]
[TR]
[TD="align: right"]279579[/TD]
[TD]01001349-1[/TD]
[TD] 6,568.77[/TD]
[/TR]
[TR]
[TD="align: right"]319381[/TD]
[TD]08928126-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD]06677269-1[/TD]
[TD] 100.00[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD]06677269-1[/TD]
[TD] 100.00[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD]06677269-1[/TD]
[TD] 100,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]350927[/TD]
[TD]04169385-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]350927[/TD]
[TD]04169385-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]419764[/TD]
[TD]08927751-1[/TD]
[TD] 15,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]421443[/TD]
[TD]06764633-1[/TD]
[TD] 1,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]436840[/TD]
[TD]09015276-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]438463[/TD]
[TD]09016534-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]471948[/TD]
[TD]09041007-1[/TD]
[TD] 5,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]481330[/TD]
[TD]09048932-1[/TD]
[TD] 5,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]493391[/TD]
[TD]02331161-1[/TD]
[TD] 5,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]526320[/TD]
[TD]03359918-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]526320[/TD]
[TD]03359918-1[/TD]
[TD] 3,510.00[/TD]
[/TR]
[TR]
[TD="align: right"]545502[/TD]
[TD]03316973-1[/TD]
[TD] 75,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]579778[/TD]
[TD]03636433-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]583199[/TD]
[TD]06887036-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]583199[/TD]
[TD]06887036-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]606823[/TD]
[TD]07664043-1[/TD]
[TD] 4,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]620270[/TD]
[TD]04429721-1[/TD]
[TD] 5,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]620270[/TD]
[TD]04429721-1[/TD]
[TD] 7,631.20[/TD]
[/TR]
[TR]
[TD="align: right"]633542[/TD]
[TD]04442762-1[/TD]
[TD] 7,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]737960[/TD]
[TD]07761962-1[/TD]
[TD] 406.00[/TD]
[/TR]
[TR]
[TD="align: right"]779532[/TD]
[TD]03898704-1[/TD]
[TD] 2,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]883960[/TD]
[TD]07871511-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]941457[/TD]
[TD]09396825-1[/TD]
[TD] 7,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]961675[/TD]
[TD]09411797-1[/TD]
[TD] 611.40[/TD]
[/TR]
[TR]
[TD="align: right"]970168[/TD]
[TD]07936233-1[/TD]
[TD] 3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]971750[/TD]
[TD]08673382-1[/TD]
[TD] 9,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]974325[/TD]
[TD]09422044-1[/TD]
[TD] 7,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]974325[/TD]
[TD]09422044-1[/TD]
[TD] 7,500.00[/TD]
[/TR]
</tbody>[/TABLE]

Payments Received Table

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="width: 88"]Customer No[/TD]
[TD="width: 117"]Check Amount[/TD]
[TD="width: 99"]Check Number[/TD]
[/TR]
[TR]
[TD="align: right"]15935[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]104727370[/TD]
[/TR]
[TR]
[TD="align: right"]21128[/TD]
[TD] 1,250.00[/TD]
[TD="align: right"]119204419[/TD]
[/TR]
[TR]
[TD="align: right"]21128[/TD]
[TD] 1,450.00[/TD]
[TD="align: right"]145930023[/TD]
[/TR]
[TR]
[TD="align: right"]109225[/TD]
[TD] 10,235.00[/TD]
[TD="align: right"]147330779[/TD]
[/TR]
[TR]
[TD="align: right"]128912[/TD]
[TD] 6,533.00[/TD]
[TD="align: right"]145558906[/TD]
[/TR]
[TR]
[TD="align: right"]142559[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]142946325[/TD]
[/TR]
[TR]
[TD="align: right"]185735[/TD]
[TD] 8,581.00[/TD]
[TD="align: right"]146842182[/TD]
[/TR]
[TR]
[TD="align: right"]196899[/TD]
[TD] 9,200.00[/TD]
[TD="align: right"]137189591[/TD]
[/TR]
[TR]
[TD="align: right"]279579[/TD]
[TD] 6,568.77[/TD]
[TD="align: right"]148727437[/TD]
[/TR]
[TR]
[TD="align: right"]306326[/TD]
[TD] 25,000.00[/TD]
[TD="align: right"]147166738[/TD]
[/TR]
[TR]
[TD="align: right"]319381[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]145927908[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD] 100.00[/TD]
[TD="align: right"]142446881[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD] 100.00[/TD]
[TD="align: right"]142446882[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD] 100,000.00[/TD]
[TD="align: right"]147747165[/TD]
[/TR]
[TR]
[TD="align: right"]350927[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]130843967[/TD]
[/TR]
[TR]
[TD="align: right"]350927[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]147096693[/TD]
[/TR]
[TR]
[TD="align: right"]419764[/TD]
[TD] 15,000.00[/TD]
[TD="align: right"]123466494[/TD]
[/TR]
[TR]
[TD="align: right"]421443[/TD]
[TD] 1,500.00[/TD]
[TD="align: right"]100599091[/TD]
[/TR]
[TR]
[TD="align: right"]436840[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]134345889[/TD]
[/TR]
[TR]
[TD="align: right"]437252[/TD]
[TD] 1,500.00[/TD]
[TD="align: right"]139573296[/TD]
[/TR]
[TR]
[TD="align: right"]438463[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]145734790[/TD]
[/TR]
[TR]
[TD="align: right"]452126[/TD]
[TD] 4,500.00[/TD]
[TD="align: right"]147099095[/TD]
[/TR]
[TR]
[TD="align: right"]471948[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]144172551[/TD]
[/TR]
[TR]
[TD="align: right"]481330[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]131034064[/TD]
[/TR]
[TR]
[TD="align: right"]493391[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]128081007[/TD]
[/TR]
[TR]
[TD="align: right"]526320[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]123845749[/TD]
[/TR]
[TR]
[TD="align: right"]540944[/TD]
[TD] 250,000.00[/TD]
[TD="align: right"]149143010[/TD]
[/TR]
[TR]
[TD="align: right"]545502[/TD]
[TD] 75,000.00[/TD]
[TD="align: right"]144259893[/TD]
[/TR]
[TR]
[TD="align: right"]547478[/TD]
[TD] 9,253.00[/TD]
[TD="align: right"]132728054[/TD]
[/TR]
[TR]
[TD="align: right"]583199[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]130042365[/TD]
[/TR]
[TR]
[TD="align: right"]583199[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]142366423[/TD]
[/TR]
[TR]
[TD="align: right"]605244[/TD]
[TD] 35,000.00[/TD]
[TD="align: right"]145650826[/TD]
[/TR]
[TR]
[TD="align: right"]605244[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]146085435[/TD]
[/TR]
[TR]
[TD="align: right"]606823[/TD]
[TD] 4,500.00[/TD]
[TD="align: right"]124372621[/TD]
[/TR]
[TR]
[TD="align: right"]620270[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]143212783[/TD]
[/TR]
[TR]
[TD="align: right"]633542[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]143804189[/TD]
[/TR]
[TR]
[TD="align: right"]656817[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]125486623[/TD]
[/TR]
[TR]
[TD="align: right"]700698[/TD]
[TD] 1,500.00[/TD]
[TD="align: right"]143981597[/TD]
[/TR]
[TR]
[TD="align: right"]737960[/TD]
[TD] 406.00[/TD]
[TD="align: right"]129955189[/TD]
[/TR]
[TR]
[TD="align: right"]779532[/TD]
[TD] 2,000.00[/TD]
[TD="align: right"]147098652[/TD]
[/TR]
[TR]
[TD="align: right"]815124[/TD]
[TD] 9,253.00[/TD]
[TD="align: right"]146586643[/TD]
[/TR]
[TR]
[TD="align: right"]883960[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]144992649[/TD]
[/TR]
[TR]
[TD="align: right"]930482[/TD]
[TD] 35,000.00[/TD]
[TD="align: right"]148813121[/TD]
[/TR]
[TR]
[TD="align: right"]941457[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]109629786[/TD]
[/TR]
[TR]
[TD="align: right"]947913[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]117616433[/TD]
[/TR]
[TR]
[TD="align: right"]947913[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]145157309[/TD]
[/TR]
[TR]
[TD="align: right"]961675[/TD]
[TD] 611.40[/TD]
[TD="align: right"]147942078[/TD]
[/TR]
[TR]
[TD="align: right"]970168[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]141559228[/TD]
[/TR]
[TR]
[TD="align: right"]974325[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]145156081[/TD]
[/TR]
</tbody>[/TABLE]

Output Report Example

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="width: 88"]Customer No[/TD]
[TD="width: 107"]Invoice Number[/TD]
[TD="width: 107"]Invoice Number[/TD]
[TD="width: 117"]Purchase Amount[/TD]
[TD="width: 98"]Check Amount[/TD]
[TD="width: 99"]Check Number[/TD]
[/TR]
[TR]
[TD="align: right"]15935[/TD]
[TD]07213588-1[/TD]
[TD]07213588-1[/TD]
[TD] 5,000.00[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]104727370[/TD]
[/TR]
[TR]
[TD="align: right"]142559[/TD]
[TD]09559387-1[/TD]
[TD]09559387-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]142946325[/TD]
[/TR]
[TR]
[TD="align: right"]185735[/TD]
[TD]09592476-1[/TD]
[TD]09592476-1[/TD]
[TD] 8,581.00[/TD]
[TD] 8,581.00[/TD]
[TD="align: right"]146842182[/TD]
[/TR]
[TR]
[TD="align: right"]196899[/TD]
[TD]07355651-1[/TD]
[TD]07355651-1[/TD]
[TD] 9,200.00[/TD]
[TD] 9,200.00[/TD]
[TD="align: right"]137189591[/TD]
[/TR]
[TR]
[TD="align: right"]279579[/TD]
[TD]01001349-1[/TD]
[TD]01001349-1[/TD]
[TD] 6,568.77[/TD]
[TD] 6,568.77[/TD]
[TD="align: right"]148727437[/TD]
[/TR]
[TR]
[TD="align: right"]319381[/TD]
[TD]08928126-1[/TD]
[TD]08928126-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]145927908[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD]06677269-1[/TD]
[TD]06677269-1[/TD]
[TD] 100.00[/TD]
[TD] 100.00[/TD]
[TD="align: right"]142446881[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD]06677269-1[/TD]
[TD]06677269-1[/TD]
[TD] 100.00[/TD]
[TD] 100.00[/TD]
[TD="align: right"]142446882[/TD]
[/TR]
[TR]
[TD="align: right"]349797[/TD]
[TD]06677269-1[/TD]
[TD]06677269-1[/TD]
[TD] 100,000.00[/TD]
[TD] 100,000.00[/TD]
[TD="align: right"]147747165[/TD]
[/TR]
[TR]
[TD="align: right"]350927[/TD]
[TD]04169385-1[/TD]
[TD]04169385-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]130843967[/TD]
[/TR]
[TR]
[TD="align: right"]350927[/TD]
[TD]04169385-1[/TD]
[TD]04169385-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]147096693[/TD]
[/TR]
[TR]
[TD="align: right"]419764[/TD]
[TD]08927751-1[/TD]
[TD]08927751-1[/TD]
[TD] 15,000.00[/TD]
[TD] 15,000.00[/TD]
[TD="align: right"]123466494[/TD]
[/TR]
[TR]
[TD="align: right"]421443[/TD]
[TD]06764633-1[/TD]
[TD]06764633-1[/TD]
[TD] 1,500.00[/TD]
[TD] 1,500.00[/TD]
[TD="align: right"]100599091[/TD]
[/TR]
[TR]
[TD="align: right"]436840[/TD]
[TD]09015276-1[/TD]
[TD]09015276-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]134345889[/TD]
[/TR]
[TR]
[TD="align: right"]438463[/TD]
[TD]09016534-1[/TD]
[TD]09016534-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]145734790[/TD]
[/TR]
[TR]
[TD="align: right"]471948[/TD]
[TD]09041007-1[/TD]
[TD]09041007-1[/TD]
[TD] 5,000.00[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]144172551[/TD]
[/TR]
[TR]
[TD="align: right"]481330[/TD]
[TD]09048932-1[/TD]
[TD]09048932-1[/TD]
[TD] 5,000.00[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]131034064[/TD]
[/TR]
[TR]
[TD="align: right"]493391[/TD]
[TD]02331161-1[/TD]
[TD]02331161-1[/TD]
[TD] 5,000.00[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]128081007[/TD]
[/TR]
[TR]
[TD="align: right"]526320[/TD]
[TD]03359918-1[/TD]
[TD]03359918-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]123845749[/TD]
[/TR]
[TR]
[TD="align: right"]526320[/TD]
[TD]03359918-1[/TD]
[TD]03359918-1[/TD]
[TD] 3,500.00[/TD]
[TD] Not Found[/TD]
[TD]Not Found[/TD]
[/TR]
[TR]
[TD="align: right"]545502[/TD]
[TD]03316973-1[/TD]
[TD]03316973-1[/TD]
[TD] 75,000.00[/TD]
[TD] 75,000.00[/TD]
[TD="align: right"]144259893[/TD]
[/TR]
[TR]
[TD="align: right"]579778[/TD]
[TD]03636433-1[/TD]
[TD]03636433-1[/TD]
[TD] 3,500.00[/TD]
[TD] Not Found[/TD]
[TD]Not Found[/TD]
[/TR]
[TR]
[TD="align: right"]583199[/TD]
[TD]06887036-1[/TD]
[TD]06887036-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]130042365[/TD]
[/TR]
[TR]
[TD="align: right"]583199[/TD]
[TD]06887036-1[/TD]
[TD]06887036-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]142366423[/TD]
[/TR]
[TR]
[TD="align: right"]606823[/TD]
[TD]07664043-1[/TD]
[TD]07664043-1[/TD]
[TD] 4,500.00[/TD]
[TD] 4,500.00[/TD]
[TD="align: right"]124372621[/TD]
[/TR]
[TR]
[TD="align: right"]620270[/TD]
[TD]04429721-1[/TD]
[TD]04429721-1[/TD]
[TD] 5,000.00[/TD]
[TD] 5,000.00[/TD]
[TD="align: right"]143212783[/TD]
[/TR]
[TR]
[TD="align: right"]620270[/TD]
[TD]04429721-1[/TD]
[TD]04429721-1[/TD]
[TD] 5,000.00[/TD]
[TD] Not Found[/TD]
[TD]Not Found[/TD]
[/TR]
[TR]
[TD="align: right"]633542[/TD]
[TD]04442762-1[/TD]
[TD]04442762-1[/TD]
[TD] 7,500.00[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]143804189[/TD]
[/TR]
[TR]
[TD="align: right"]737960[/TD]
[TD]07761962-1[/TD]
[TD]07761962-1[/TD]
[TD] 406.00[/TD]
[TD] 406.00[/TD]
[TD="align: right"]129955189[/TD]
[/TR]
[TR]
[TD="align: right"]779532[/TD]
[TD]03898704-1[/TD]
[TD]03898704-1[/TD]
[TD] 2,000.00[/TD]
[TD] 2,000.00[/TD]
[TD="align: right"]147098652[/TD]
[/TR]
[TR]
[TD="align: right"]883960[/TD]
[TD]07871511-1[/TD]
[TD]07871511-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]144992649[/TD]
[/TR]
[TR]
[TD="align: right"]941457[/TD]
[TD]09396825-1[/TD]
[TD]09396825-1[/TD]
[TD] 7,500.00[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]109629786[/TD]
[/TR]
[TR]
[TD="align: right"]961675[/TD]
[TD]09411797-1[/TD]
[TD]09411797-1[/TD]
[TD] 611.40[/TD]
[TD] 611.40[/TD]
[TD="align: right"]147942078[/TD]
[/TR]
[TR]
[TD="align: right"]970168[/TD]
[TD]07936233-1[/TD]
[TD]07936233-1[/TD]
[TD] 3,500.00[/TD]
[TD] 3,500.00[/TD]
[TD="align: right"]141559228[/TD]
[/TR]
[TR]
[TD="align: right"]971750[/TD]
[TD]08673382-1[/TD]
[TD]08673382-1[/TD]
[TD] 3,500.00[/TD]
[TD] Not Found[/TD]
[TD]Not Found[/TD]
[/TR]
[TR]
[TD="align: right"]974325[/TD]
[TD]09422044-1[/TD]
[TD]09422044-1[/TD]
[TD] 7,500.00[/TD]
[TD] 7,500.00[/TD]
[TD="align: right"]145156081[/TD]
[/TR]
[TR]
[TD="align: right"]974325[/TD]
[TD]09422044-1[/TD]
[TD]09422044-1[/TD]
[TD] 7,500.00[/TD]
[TD] Not Found[/TD]
[TD]Not Found[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Using Power Query/Get and Transform. Upload each table to PQ. Close and Load to a Connection. Merge the two tables as shown in the following Mcode.

Code: 
let
    Source = Table.NestedJoin(Payments, {"Check Amount", "Customer No"}, Invoices, {"Purchase Amount", "Customer No"}, "Invoices", JoinKind.LeftOuter),
    #"Expanded Invoices" = Table.ExpandTableColumn(Source, "Invoices", {"Invoice Number", "Purchase Amount"}, {"Invoices.Invoice Number", "Invoices.Purchase Amount"})
in
    #"Expanded Invoices"

Data Range
[Table="class: grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]
Customer No​
[/td][td]
Check Amount​
[/td][td]
Check Number​
[/td][td]
Invoices.Invoice Number​
[/td][td]
Invoices.Purchase Amount​
[/td][/tr]


[tr][td]
2​
[/td][td]
15935​
[/td][td]
5000​
[/td][td]
104727370​
[/td][td]
07213588-1​
[/td][td]
5000​
[/td][/tr]


[tr][td]
3​
[/td][td]
21128​
[/td][td]
1250​
[/td][td]
119204419​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
4​
[/td][td]
142559​
[/td][td]
3500​
[/td][td]
142946325​
[/td][td]
09559387-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
5​
[/td][td]
21128​
[/td][td]
1450​
[/td][td]
145930023​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
6​
[/td][td]
185735​
[/td][td]
8581​
[/td][td]
146842182​
[/td][td]
09592476-1​
[/td][td]
8581​
[/td][/tr]


[tr][td]
7​
[/td][td]
109225​
[/td][td]
10235​
[/td][td]
147330779​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
8​
[/td][td]
196899​
[/td][td]
9200​
[/td][td]
137189591​
[/td][td]
07355651-1​
[/td][td]
9200​
[/td][/tr]


[tr][td]
9​
[/td][td]
128912​
[/td][td]
6533​
[/td][td]
145558906​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
10​
[/td][td]
279579​
[/td][td]
6568.77​
[/td][td]
148727437​
[/td][td]
01001349-1​
[/td][td]
6568.77​
[/td][/tr]


[tr][td]
11​
[/td][td]
319381​
[/td][td]
3500​
[/td][td]
145927908​
[/td][td]
08928126-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
12​
[/td][td]
349797​
[/td][td]
100​
[/td][td]
142446881​
[/td][td]
06677269-1​
[/td][td]
100​
[/td][/tr]


[tr][td]
13​
[/td][td]
349797​
[/td][td]
100​
[/td][td]
142446881​
[/td][td]
06677269-1​
[/td][td]
100​
[/td][/tr]


[tr][td]
14​
[/td][td]
349797​
[/td][td]
100​
[/td][td]
142446882​
[/td][td]
06677269-1​
[/td][td]
100​
[/td][/tr]


[tr][td]
15​
[/td][td]
349797​
[/td][td]
100​
[/td][td]
142446882​
[/td][td]
06677269-1​
[/td][td]
100​
[/td][/tr]


[tr][td]
16​
[/td][td]
349797​
[/td][td]
100000​
[/td][td]
147747165​
[/td][td]
06677269-1​
[/td][td]
100000​
[/td][/tr]


[tr][td]
17​
[/td][td]
306326​
[/td][td]
25000​
[/td][td]
147166738​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
18​
[/td][td]
350927​
[/td][td]
3500​
[/td][td]
130843967​
[/td][td]
04169385-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
19​
[/td][td]
350927​
[/td][td]
3500​
[/td][td]
130843967​
[/td][td]
04169385-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
20​
[/td][td]
350927​
[/td][td]
3500​
[/td][td]
147096693​
[/td][td]
04169385-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
21​
[/td][td]
350927​
[/td][td]
3500​
[/td][td]
147096693​
[/td][td]
04169385-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
22​
[/td][td]
419764​
[/td][td]
15000​
[/td][td]
123466494​
[/td][td]
08927751-1​
[/td][td]
15000​
[/td][/tr]


[tr][td]
23​
[/td][td]
421443​
[/td][td]
1500​
[/td][td]
100599091​
[/td][td]
06764633-1​
[/td][td]
1500​
[/td][/tr]


[tr][td]
24​
[/td][td]
436840​
[/td][td]
3500​
[/td][td]
134345889​
[/td][td]
09015276-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
25​
[/td][td]
438463​
[/td][td]
3500​
[/td][td]
145734790​
[/td][td]
09016534-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
26​
[/td][td]
471948​
[/td][td]
5000​
[/td][td]
144172551​
[/td][td]
09041007-1​
[/td][td]
5000​
[/td][/tr]


[tr][td]
27​
[/td][td]
481330​
[/td][td]
5000​
[/td][td]
131034064​
[/td][td]
09048932-1​
[/td][td]
5000​
[/td][/tr]


[tr][td]
28​
[/td][td]
493391​
[/td][td]
5000​
[/td][td]
128081007​
[/td][td]
02331161-1​
[/td][td]
5000​
[/td][/tr]


[tr][td]
29​
[/td][td]
526320​
[/td][td]
3500​
[/td][td]
123845749​
[/td][td]
03359918-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
30​
[/td][td]
437252​
[/td][td]
1500​
[/td][td]
139573296​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
31​
[/td][td]
545502​
[/td][td]
75000​
[/td][td]
144259893​
[/td][td]
03316973-1​
[/td][td]
75000​
[/td][/tr]


[tr][td]
32​
[/td][td]
452126​
[/td][td]
4500​
[/td][td]
147099095​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
33​
[/td][td]
583199​
[/td][td]
3500​
[/td][td]
130042365​
[/td][td]
06887036-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
34​
[/td][td]
583199​
[/td][td]
3500​
[/td][td]
130042365​
[/td][td]
06887036-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
35​
[/td][td]
583199​
[/td][td]
3500​
[/td][td]
142366423​
[/td][td]
06887036-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
36​
[/td][td]
583199​
[/td][td]
3500​
[/td][td]
142366423​
[/td][td]
06887036-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
37​
[/td][td]
606823​
[/td][td]
4500​
[/td][td]
124372621​
[/td][td]
07664043-1​
[/td][td]
4500​
[/td][/tr]


[tr][td]
38​
[/td][td]
620270​
[/td][td]
5000​
[/td][td]
143212783​
[/td][td]
04429721-1​
[/td][td]
5000​
[/td][/tr]


[tr][td]
39​
[/td][td]
540944​
[/td][td]
250000​
[/td][td]
149143010​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
40​
[/td][td]
633542​
[/td][td]
7500​
[/td][td]
143804189​
[/td][td]
04442762-1​
[/td][td]
7500​
[/td][/tr]


[tr][td]
41​
[/td][td]
547478​
[/td][td]
9253​
[/td][td]
132728054​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
42​
[/td][td]
605244​
[/td][td]
35000​
[/td][td]
145650826​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
43​
[/td][td]
605244​
[/td][td]
7500​
[/td][td]
146085435​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
44​
[/td][td]
656817​
[/td][td]
7500​
[/td][td]
125486623​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
45​
[/td][td]
700698​
[/td][td]
1500​
[/td][td]
143981597​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
46​
[/td][td]
737960​
[/td][td]
406​
[/td][td]
129955189​
[/td][td]
07761962-1​
[/td][td]
406​
[/td][/tr]


[tr][td]
47​
[/td][td]
779532​
[/td][td]
2000​
[/td][td]
147098652​
[/td][td]
03898704-1​
[/td][td]
2000​
[/td][/tr]


[tr][td]
48​
[/td][td]
815124​
[/td][td]
9253​
[/td][td]
146586643​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
49​
[/td][td]
883960​
[/td][td]
3500​
[/td][td]
144992649​
[/td][td]
07871511-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
50​
[/td][td]
930482​
[/td][td]
35000​
[/td][td]
148813121​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
51​
[/td][td]
941457​
[/td][td]
7500​
[/td][td]
109629786​
[/td][td]
09396825-1​
[/td][td]
7500​
[/td][/tr]


[tr][td]
52​
[/td][td]
947913​
[/td][td]
7500​
[/td][td]
117616433​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
53​
[/td][td]
947913​
[/td][td]
7500​
[/td][td]
145157309​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
54​
[/td][td]
961675​
[/td][td]
611.4​
[/td][td]
147942078​
[/td][td]
09411797-1​
[/td][td]
611.4​
[/td][/tr]


[tr][td]
55​
[/td][td]
970168​
[/td][td]
3500​
[/td][td]
141559228​
[/td][td]
07936233-1​
[/td][td]
3500​
[/td][/tr]


[tr][td]
56​
[/td][td]
974325​
[/td][td]
7500​
[/td][td]
145156081​
[/td][td]
09422044-1​
[/td][td]
7500​
[/td][/tr]


[tr][td]
57​
[/td][td]
974325​
[/td][td]
7500​
[/td][td]
145156081​
[/td][td]
09422044-1​
[/td][td]
7500​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
How about
Code: 
Sub goobee()
    Dim OrdAry As Variant, PayAry As Variant
    Dim i As Long, j As Long
    
    With Sheets("Sheet1")
        OrdAry = .Range("A1").CurrentRegion.Resize(, 5).Value2
    End With
    With Sheets("Sheet3")
        PayAry = .Range("A1").CurrentRegion.Value2
    End With
    For i = 2 To UBound(OrdAry)
        For j = 2 To UBound(PayAry)
            If OrdAry(i, 1) = PayAry(j, 1) And OrdAry(i, 3) = PayAry(j, 2) Then
                OrdAry(i, 4) = PayAry(j, 2)
                OrdAry(i, 5) = PayAry(j, 3)
                PayAry(j, 1) = ""
                Exit For
            End If
        Next j
    Next i
    Sheets("Sheet1").Range("A1").CurrentRegion.Resize(UBound(OrdAry), 5).Value2 = OrdAry
End Sub
Change sheet names to suit
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top