Compare, Match and Copy based upon 2 variables

[goobee]

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

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

<tbody>
</tbody>

Payments Received Table

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

<tbody>
</tbody>

Output Report Example

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

<tbody>
</tbody>

 

[alansidman]

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.

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

	A​	B​	C​	D​	E​
1​	Customer No​	Check Amount​	Check Number​	Invoices.Invoice Number​	Invoices.Purchase Amount​
2​	15935​	5000​	104727370​	07213588-1​	5000​
3​	21128​	1250​	119204419​	​	​
4​	142559​	3500​	142946325​	09559387-1​	3500​
5​	21128​	1450​	145930023​	​	​
6​	185735​	8581​	146842182​	09592476-1​	8581​
7​	109225​	10235​	147330779​	​	​
8​	196899​	9200​	137189591​	07355651-1​	9200​
9​	128912​	6533​	145558906​	​	​
10​	279579​	6568.77​	148727437​	01001349-1​	6568.77​
11​	319381​	3500​	145927908​	08928126-1​	3500​
12​	349797​	100​	142446881​	06677269-1​	100​
13​	349797​	100​	142446881​	06677269-1​	100​
14​	349797​	100​	142446882​	06677269-1​	100​
15​	349797​	100​	142446882​	06677269-1​	100​
16​	349797​	100000​	147747165​	06677269-1​	100000​
17​	306326​	25000​	147166738​	​	​
18​	350927​	3500​	130843967​	04169385-1​	3500​
19​	350927​	3500​	130843967​	04169385-1​	3500​
20​	350927​	3500​	147096693​	04169385-1​	3500​
21​	350927​	3500​	147096693​	04169385-1​	3500​
22​	419764​	15000​	123466494​	08927751-1​	15000​
23​	421443​	1500​	100599091​	06764633-1​	1500​
24​	436840​	3500​	134345889​	09015276-1​	3500​
25​	438463​	3500​	145734790​	09016534-1​	3500​
26​	471948​	5000​	144172551​	09041007-1​	5000​
27​	481330​	5000​	131034064​	09048932-1​	5000​
28​	493391​	5000​	128081007​	02331161-1​	5000​
29​	526320​	3500​	123845749​	03359918-1​	3500​
30​	437252​	1500​	139573296​	​	​
31​	545502​	75000​	144259893​	03316973-1​	75000​
32​	452126​	4500​	147099095​	​	​
33​	583199​	3500​	130042365​	06887036-1​	3500​
34​	583199​	3500​	130042365​	06887036-1​	3500​
35​	583199​	3500​	142366423​	06887036-1​	3500​
36​	583199​	3500​	142366423​	06887036-1​	3500​
37​	606823​	4500​	124372621​	07664043-1​	4500​
38​	620270​	5000​	143212783​	04429721-1​	5000​
39​	540944​	250000​	149143010​	​	​
40​	633542​	7500​	143804189​	04442762-1​	7500​
41​	547478​	9253​	132728054​	​	​
42​	605244​	35000​	145650826​	​	​
43​	605244​	7500​	146085435​	​	​
44​	656817​	7500​	125486623​	​	​
45​	700698​	1500​	143981597​	​	​
46​	737960​	406​	129955189​	07761962-1​	406​
47​	779532​	2000​	147098652​	03898704-1​	2000​
48​	815124​	9253​	146586643​	​	​
49​	883960​	3500​	144992649​	07871511-1​	3500​
50​	930482​	35000​	148813121​	​	​
51​	941457​	7500​	109629786​	09396825-1​	7500​
52​	947913​	7500​	117616433​	​	​
53​	947913​	7500​	145157309​	​	​
54​	961675​	611.4​	147942078​	09411797-1​	611.4​
55​	970168​	3500​	141559228​	07936233-1​	3500​
56​	974325​	7500​	145156081​	09422044-1​	7500​
57​	974325​	7500​	145156081​	09422044-1​	7500​

 

[goobee]

Thanks but Power Query is not available to us. We are running Excel 2013.

 

[Fluff]

How about

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

 

[goobee]

Thanks Fluff, I can work with this.

 

[alansidman]

Power query is a free add in to Excel 2013. It is included standard in 2016 and later.

https://www.microsoft.com/en-us/dow...lationId=2ab346d2-647f-4f23-a4a3-e8ec2cc5872f

 

[Fluff]

Thanks Fluff, I can work with this.

You're welcome & thanks for the feedback