Vlookup to Find Matches with Negative Numbers

MacIndy

New Member
Joined
Dec 30, 2019
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
I'm attempting to use a Cust Order # to find a monetary value on a different worksheet. I'm only looking for negative values though. This is an accounting audit thing. I'm attempting to find out why a dollar amount on a Customer Order that should have been charged wasn't the final amount that was actually charged. I believe, in many cases this would be due to a credit being applied by Accounting. So, I am attempting to Match the Order # on each worksheet and look to the right for a negative number only.

In other words, a give Customer Order may be listed twice on the spreadsheet I'm doing my lookup to but I only want to see the negative number that offset the original amount. Better yet, if there is more than one negative amount it might also be nice to do a SumIf on the negative amounts so I have the total credits applied.

Here's my current Vlookup:
=VLOOKUP(A4,PostedInv20Jan2023All!C2:E19037,3,FALSE)

Here's a SumIf I tried that does work. Just not sure how to combine the two and only Sun negative numbers to find my credits
=SUMIF($C$2:$C$19037,C338,$E$2:$E$19037)
 

Attachments

  • CustomerOrderVlookuptoPostedInvoices.png
    CustomerOrderVlookuptoPostedInvoices.png
    37.2 KB · Views: 14
  • Posted Invoices Data.png
    Posted Invoices Data.png
    12.9 KB · Views: 14

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try

Excel Formula:
=SUMIFS($E$2:$E$19037,$C$2:$C$19037,C338,$E$2:$E$19037,"<0")
 
Upvote 0
Thanks for the quick reply. The data is on two different worksheets. Its two different tables in our ERP system. I have to match the Customer Order# and then lookup values.
 
Upvote 0
Then show some rows of your data, how it looks.
 
Upvote 0
Here's a sample of the Customer Order Data:

Compare COtoPostedInv.xlsx
ABCDEFGHIJKLM
1OrderFreightMisc ChargesSales TaxTotal PriceAccum FreightFreight on Posted?MatchesFreight CostFreight InvoicedStatusOrder DateIf Neg Amt Exists?
2TRANSFER010043,631.88666,944.480#N/AOrdered3/16/2022
3TaxAdj000200000#N/A01Complete1/4/2023
4lb000040780005,085.90217.220FALSE217.221Complete10/26/2022-217.22
5RB00000002000500.40#N/AComplete3/18/2022
6RB000000010003,449.000#N/A657.881Complete2/8/2022
7PM00001432001.724.410#N/AOrdered1/19/2023
8PM000014310021.89313.820#N/AOrdered1/19/2023
9PM000014300003,182.720#N/AOrdered1/17/2023
10PM00001429000220.70#N/AOrdered1/17/2023
11PM00001428000718.070#N/AOrdered1/17/2023
12PM00001427000239.770#N/AOrdered1/17/2023
13PM00001426000328.710#N/AOrdered1/17/2023
14pm00001425000341.220#N/AOrdered1/17/2023
15PM000014240001,134.490#N/AOrdered1/17/2023
16PM00001423000206.840#N/AOrdered1/16/2023
17PM00001422000214.30#N/AOrdered1/16/2023
18PM00001421000488.140#N/AOrdered1/16/2023
19PM00001420000336.290#N/AOrdered1/16/2023
20PM000014190012.04783.920#N/AOrdered1/16/2023
21PM00001418000889.610#N/AOrdered1/16/2023
22PM00001417000182.640#N/AOrdered1/16/2023
23PM00001416000147.470#N/AOrdered1/16/2023
24lb000029890002,282.002300FALSE2301Complete7/19/2022230.00
25lb000021270004,559.96201.960FALSEComplete4/25/2022201.96
26PM000014120001,223.590#N/AOrdered1/10/2023
27PM000014110001,077.160#N/AOrdered1/10/2023
28PM000014100001,918.730#N/AOrdered1/10/2023
29PM00001409000169.510#N/AOrdered1/10/2023
30PM00001408000792.270#N/AOrdered1/10/2023
31PM00001407000216.590#N/AOrdered1/9/2023
32PM00001406000530.550#N/AOrdered1/9/2023
33PM000014050001,123.030#N/AOrdered1/9/2023
34PM0000140400061.950#N/AOrdered1/9/2023
35PM00001403000288.490#N/AOrdered1/9/2023
36PM00001402000538.110#N/AOrdered1/9/2023
37PM00001401000241.440#N/AOrdered1/9/2023
38PM00001400000721.20#N/AOrdered1/9/2023
39PM00001399000107.320#N/AOrdered1/9/2023
40PM00001398000718.990#N/AOrdered1/9/2023
41PM000013970001,009.310#N/AOrdered1/9/2023
42PM000013960001,235.000#N/AOrdered1/9/2023
43PM00001395000509.170#N/AOrdered1/9/2023
44PM000013940001,985.800#N/AOrdered1/9/2023
45PM00001393000662.550#N/AOrdered1/9/2023
46PM00001392000458.620#N/AOrdered1/9/2023
47PM00001391000387.920#N/AOrdered1/9/2023
48PM00001390000.81721.020#N/A10.811Ordered1/9/2023
49PM000013890001,809.330#N/AOrdered1/9/2023
50PM00001388000376.770#N/AOrdered1/9/2023
51PM000013870001,069.840#N/AComplete1/3/2023
52PM00001386000364.260#N/AComplete1/3/2023
53PM00001385000149.610#N/AComplete1/3/2023
54PM00001384000676.320#N/AComplete1/3/2023
55PM00001383000377.770#N/AComplete1/3/2023
56PM000013820001,129.500#N/AComplete1/3/2023
57PM00001381000196.440#N/AComplete1/3/2023
58PM00001380000910.70#N/AComplete1/3/2023
59PM00001379000769.410#N/AComplete1/3/2023
60PM0000137800094.110#N/AComplete12/27/2022
61PM00001377000382.660#N/AComplete12/27/2022
62PM00001376000646.350#N/AComplete12/27/2022
63PM00001375000128.720#N/AComplete12/27/2022
64PM00001374000268.270#N/AComplete12/27/2022
65PM00001373000345.510#N/AComplete12/27/2022
66PM00001372000310.660#N/AComplete12/27/2022
67PM00001371000438.880#N/AComplete12/27/2022
68PM00001370000730.130#N/AComplete12/27/2022
69PM00001369000356.530#N/AComplete12/27/2022
70PM00001368000352.310#N/AComplete12/27/2022
71PM00001367000236.210#N/AComplete12/27/2022
72PM000013660001,477.010#N/AComplete12/27/2022
73PM000013650001,564.270#N/AComplete12/27/2022
74PM00001364000750.190#N/AComplete12/27/2022
75lb000020000004,206.92331.020FALSEComplete4/12/2022-331.02
76PM00001362000140.8400Complete12/20/2022
77PM000013610001,545.1400Complete12/20/2022
78PM00001360000754.2100Complete12/20/2022
79PM000013590001,333.4900Complete12/20/2022
80PM00001358000157.900Complete12/20/2022
81PM00001357000318.9800Complete12/20/2022
82PM00001356000539.6300Complete12/20/2022
83PM00001355000184.8100Complete12/20/2022
84PM000013540001,927.4800Complete12/20/2022
85PM0000135300090.0700Complete12/19/2022
86PM00001352000547.4600Complete12/19/2022
87PM00001351000440.2100Complete12/19/2022
88PM000013500001,155.7400Complete12/19/2022
89PM00001349000440.0600Complete12/19/2022
90PM000013480001,237.7900Complete12/19/2022
91PM00001347000941.4100Complete12/19/2022
92PM00001346000323.3200Complete12/19/2022
93LB000008510009,593.49273.990FALSEComplete12/9/20210.00
94PM00001344000247.300Complete12/13/2022
95PM00001343000675.0500Complete12/13/2022
96PM00001342000197.100Complete12/13/2022
97PM00001341000656.8400Complete12/13/2022
98PM000013400001,381.5000Complete12/13/2022
99PM00001339000767.7200Complete12/13/2022
100PM00001338000197.3700Complete12/13/2022
101PM00001337000827.200Complete12/13/2022
102PM00001336000168.300Complete12/13/2022
103PM00001335000675.9200Complete12/13/2022
104PM000013340001,848.9400Complete12/13/2022
105PM00001333000699.1500Complete12/13/2022
106PM0000133200055.7500Complete12/13/2022
107PM00001331000256.0400Complete12/13/2022
108PM00001330000350.8900Complete12/13/2022
109PM00001329000414.7300Complete12/13/2022
110PM000013280002,092.3000Complete12/13/2022
111PM000013270001,268.0300Complete12/13/2022
112PM00001326000921.3200Complete12/13/2022
113PM00001325000325.100Complete12/12/2022
114PM00001324000439.700Complete12/12/2022
115PM00001323000238.500Complete12/12/2022
116PM00001322000124.4800Complete12/12/2022
117PM00001321000462.9100Complete12/12/2022
118PM00001320000368.2700Complete12/12/2022
119PM00001319000312.2400Complete12/12/2022
120PM00001318000935.8200Complete12/12/2022
121PM00001317000692.7800Complete12/12/2022
122PM0000131600052.400Complete12/8/2022
123CO000071280005,734.21411.210FALSEComplete11/4/2022411.21
124PM00001314000173.0100Complete12/6/2022
125PM000013130001,455.8500Complete12/6/2022
126PM00001312000673.7900Complete12/6/2022
127PM00001311000220.9400Complete12/6/2022
128PM00001310000830.1400Complete12/6/2022
129PM00001309000855.8100Complete12/6/2022
130PM00001308000807.9400Complete12/5/2022
131PM00001307000381.6900Complete12/5/2022
132PM00001306000626.1200Complete12/5/2022
133PM00001305000115.4500Complete12/5/2022
134PM00001304000780.4300Complete12/5/2022
135PM00001303000160.2500Complete12/5/2022
136PM00001302000109.30001Complete12/5/2022
137PM00001301000409.8900Complete12/5/2022
138PM000013000001,050.5600Complete12/5/2022
139PM00001299000230.9800Complete11/29/2022
140PM00001298000462.2400Complete11/29/2022
141PM00001297000407.6600Complete11/29/2022
142PM00001296000570.0700Complete11/29/2022
143PM000012950003,858.0200Complete11/29/2022
144PM00001294000297.3400Complete11/29/2022
145PM00001293000215.5500Complete11/29/2022
146PM000012920004,360.3500Complete11/29/2022
147PM00001291000405.800Complete11/29/2022
148PM00001290000140.7500Complete11/29/2022
149PM00001289000492.3100Complete11/29/2022
150PM00001288000187.9100Complete11/29/2022
151PM00001287000639.1900Complete11/29/2022
152PM00001286000330.6400Complete11/29/2022
153PM000012850001,074.4300Complete11/29/2022
154PM00001284000364.8500Complete11/29/2022
155PM000012830002,038.5600Complete11/29/2022
156PM00001282000454.4500Complete11/29/2022
157PM00001281000437.1300Complete11/29/2022
158PM000012800001,499.770001Complete11/29/2022
159PM00001279000724.8600Complete11/29/2022
160PM00001278000636.7600Complete11/28/2022
161PM00001277000295.3900Complete11/28/2022
162CO000063960003,296.22253.020FALSE00Complete8/15/2022253.02
163PM00001275000214.3500Complete11/22/2022
164PM00001274000673.9600Complete11/22/2022
165PM00001273000179.5100Complete11/22/2022
166PM00001272000819.7100Complete11/22/2022
167PM000012710001,269.5400Complete11/22/2022
168PM00001270000554.100Complete11/21/2022
169PM00001268000283.8300Complete11/21/2022
170PM00001267000360.8400Complete11/21/2022
171PM00001266000725.5300Complete11/21/2022
172PM00001265000802.1500Complete11/21/2022
173PM00001264000275.7500Complete11/21/2022
174PM00001263000550.200Complete11/21/2022
175PM00001262000276.9200Complete11/21/2022
176PM00001261000155.900Complete11/21/2022
177PM00001260000939.5300Complete11/15/2022
178PM00001259000601.0700Complete11/15/2022
179PM000012580002,849.1100Complete11/15/2022
180PM00001257000984.8100Complete11/15/2022
181PM00001256000257.5200Complete11/15/2022
182PM00001255000748.8300Complete11/15/2022
183PM00001254000394.3100Complete11/15/2022
184PM000012530001,041.5000Complete11/15/2022
185PM000012520001,025.0300Complete11/15/2022
186PM00001251000538.2900Complete11/14/2022
187PM00001250000899.3600Complete11/14/2022
188PM000012490002,048.4700Complete11/14/2022
189PM00001248000318.5500Complete11/14/2022
190PM00001247000471.3900Complete11/14/2022
191PM000012460001,060.1100Complete11/14/2022
192PM00001245000153.600Complete11/14/2022
193PM00001244000452.200Complete11/14/2022
194PM000012430002,225.4900Complete11/14/2022
195PM00001242000773.7900Complete11/14/2022
196PM00001241000335.6700Complete11/14/2022
197PM000012400002,193.8200Complete11/14/2022
198PM00001239000381.20095.891Complete11/11/2022
199PM00001238000232.2900Complete11/8/2022
200PM00001237000996.4700Complete11/8/2022
201PM000012360001,426.8100Complete11/8/2022
AllCos_20Jan2023
Cell Formulas
RangeFormula
H4,H162,H123,H93,H75,H24:H25H4=F4=G4
M4,M162,M123,M93,M75,M24:M25M4=VLOOKUP(A4,PostedInv20Jan2023All!C2:E19037,3,FALSE)
G2G2=VLOOKUP(A2,PostedSumIfRef!C1:E5478,3,FALSE)
G3,G5:G23,G26:G74,G76:G92,G94:G122,G124:G161,G163:G201G3=VLOOKUP(A3,PostedInv20Jan2023Ref!C339:F17837,4,FALSE)
G4G4=VLOOKUP(A4,PostedInv20Jan2023Ref!C2621:F20119,4,FALSE)
G24G24=VLOOKUP(A24,PostedInv20Jan2023Ref!C3693:F21191,4,FALSE)
G25G25=VLOOKUP(A25,PostedInv20Jan2023Ref!C4552:F22050,4,FALSE)
G75G75=VLOOKUP(A75,PostedInv20Jan2023Ref!C4678:F22176,4,FALSE)
G93G93=VLOOKUP(A93,PostedInv20Jan2023Ref!C5820:F23318,4,FALSE)
G123G123=VLOOKUP(A123,PostedInv20Jan2023Ref!C7356:F24854,4,FALSE)
G162G162=VLOOKUP(A162,PostedInv20Jan2023Ref!C8083:F25581,4,FALSE)
 
Upvote 0
Here's a sample of the Posted Invoice Data:

Compare COtoPostedInv.xlsx
ABCDEFGHIJKLMN
1Invoice NumberCustomerOrderAmountFreightSumIfMisc ChargesVAT Adjusted Sales TaxStateCreated ByRecord DateNameInvoice DateTerms Code
2CR00000703ALSA001TaxAdj000251.4000-3.6AKsa1/4/2023Cintas Corporation1/11/2019N60
34826598ALSA001TaxAdj000251.40003.6AKsa1/4/2023Generation Tux Inc.8/31/2021N30
4CR00000751ATLA001RMA000030400000KYar@indyhanger.com1/19/2023Robinson Cleaners-Don't do business with8/31/2021N30
5CR00000749TDCM002RMA0000303128.08000-8.1OHar@indyhanger.com1/17/2023Cintas Culpeper VA8/31/2021N60
6CR00000748COLU001RMA000030262.50000OHar@indyhanger.com1/17/2023Springdale Cleaners-4 Monfort Heights8/31/2021CC
7CR00000747MILT001RMA0000301263.22000-17.22INar@indyhanger.com1/10/2023Cintas Salt Lake City UT (S 4370)8/31/2021N60
8CR00000746DELU001RMA000030093.250000OHar@indyhanger.com1/18/2023Cintas Vidalia GA8/27/2021N60
9CR00000745TDCS004RMA0000299526.50000NJar@indyhanger.com1/10/2023Springdale Cleaners-2 Northland8/31/2021CC
10CR00000744DALE001RMA000029869.50000OHar@indyhanger.com1/12/2023Cj's Dry Cleaning7/28/2022DUE
11CR00000743SUMM001RMA000029700000INar@indyhanger.com1/10/2023Clean & Fresh8/24/2021CC
12CR00000736DELU001RMA000029654000-3.51OHar@indyhanger.com1/18/2023A-One Fine Dry Cleaning8/2/2022CC
13CR00000735PLYM001RMA00002951,167.60000-81.73INar@indyhanger.com1/18/2023American Dry Cleaners8/2/2022CC
14CR00000699DONE001RMA0000294267000-18.69INar@indyhanger.com1/4/2023Classic Cleaners (IN)8/3/2022N30
15CR00000697MBLE004RMA000029342000-3.28OHar@indyhanger.com12/29/2022Pleasant Run Cleaners8/8/2022DUE
16CR00000696CLAS001RMA0000292462000-32.34INar@indyhanger.com12/29/2022Sitex Corp. CSC8/8/2022N30
17CR00000694TDCG001RMA00002911,462.75000-1.49OHar@indyhanger.com12/22/2022Unifirst Indianapolis IN8/1/2022N45
18CR00000693TDCV001RMA0000290541.2000-40.59FLar@indyhanger.com12/29/2022Suits 20/209/7/2021CC
19CR00000692GRIF001RMA000028949.650000OHar@indyhanger.com12/27/2022Bailey's Cleaners7/19/2022N30
20CR00000691MILT001RMA0000288365000-25.55INar@indyhanger.com12/29/2022Cintas Graham NC9/8/2021CIN
21CR00000687DELU001RMA000028749.750000OHar@indyhanger.com12/20/2022Continental Linen - CSC10/1/2021N30
22CR00000686DELU003RMA000028667.89000-5.3OHar@indyhanger.com12/27/2022Classic Cleaners (IN)9/30/2021N30
23CR00000685PIER001RMA0000285264.12000-35.07OHar@indyhanger.com12/12/2022Tide Cleaners Parkland FL9/30/2021N30
24CR00000685PIER001RMA0000285238.5000-35.07OHar@indyhanger.com12/12/2022New Style Cleaners9/30/2021CC
25CR00000684COLU001RMA0000284146.47000-10.22OHar@indyhanger.com12/12/2022FDR Services Corp of NY9/30/2021N30
26CR00000683MORE001RMA0000283126000-8.82INar@indyhanger.com12/13/2022Nu-Yale Glacier Cleaners9/30/2021N30
27CR00000682CRDN001RMA000028242000-2.94INar@indyhanger.com12/29/2022Nu-Yale Glacier Cleaners9/30/2021N30
28CR00000680SAVE001RMA00002811020000NVar@indyhanger.com1/6/2023Classic Cleaners (IN)9/30/2021N30
29CR00000679TDCA001RMA0000280119.980000OHar@indyhanger.com12/9/2022Unifirst Garden City KS9/26/2021CC
30CR00000670MBLE003RMA0000279139.29000-10.08OHar@indyhanger.com11/30/2022City Cleaners7/6/2022CC
31CR00000669EAST002RMA000027859.45000-4.64OHar@indyhanger.com12/7/2022Dry Clean City9/20/2021CC
32CR00000667MILT001RMA0000277142.68000-9.99INar@indyhanger.com12/19/2022Carriage Cleaners-Maryland Heights7/13/2022CC
33CR00000653KRON001RMA00002764.490000OHar@indyhanger.com11/28/2022Laxa Cleaners7/14/2022N30
34CR00000652DELU001RMA0000275431.33000-26.33OHar@indyhanger.com11/17/2022Cintas Ladson SC9/9/2021CIN
35CR00000650WEST002RMA0000274-0.290000OHar@indyhanger.com11/16/2022New Style Cleaners9/9/2021CC
36CR00000650WEST002RMA000027410.290000OHar@indyhanger.com11/16/2022Clean X Press8/11/2022CC
37CR00000649TDCL001RMA00002732060000OHar@indyhanger.com12/6/2022Hamilton Trading Co-Don't Do Business With8/11/2022N30
38CR00000648TDCL002RMA000027231.20000OHar@indyhanger.com12/6/2022Milto Cleaners8/12/2021N30
39CR00000647SUIT001RMA00002712440000ILar@indyhanger.com1/3/2023Hamilton Trading Co-Don't Do Business With8/11/2022N30
40CR00000646SNOW002RMA0000270212.85000-15.96OHar@indyhanger.com11/17/2022Saveon Corp.9/6/2022CC
41CR00000645HERI001RMA000026961.76000-4.47OHar@indyhanger.com11/16/2022Milto Cleaners7/19/2021N30
42CR00000644CROS001RMA000026800000INar@indyhanger.com12/7/2022LKH Inc. / Dionne Supply7/8/2021N30
43CR00000643TDCM002RMA0000267141.70000OHar@indyhanger.com12/7/2022Cintas Milford OH7/8/2021N60
44CR00000641ATLA001RMA0000266106.50000KYar@indyhanger.com11/11/2022Dale Cleaners9/15/2022CC
45CR00000640TDCP003RMA0000265210000OHar@indyhanger.com11/7/2022A-One Fine Dry Cleaning7/7/2021CC
46CR00000639LEGA001RMA00002641,579.500000OKar@indyhanger.com12/6/2022Cintas Olathe KS9/20/2022CIN
47CR00000638WEST002RMA000026357.290000OHar@indyhanger.com11/7/2022Big Apple Cleaners9/20/2022CC
48CR00000634MILT001RMA0000262135.47000-8.86INar@indyhanger.com10/28/2022Miller Textile Services CSC7/1/2021N30
49CR00000629DALE001RMA0000261155.85000-11.69OHar@indyhanger.com10/27/2022Miller Textile Services CSC7/1/2021N30
50CR00000628APPE002RMA0000260106.50000OHar@indyhanger.com10/24/2022Sandy's One Hour Cleaners9/21/2022DUE
51CR00000627TDCL001RMA000025972.560000OHar@indyhanger.com10/24/2022Wildman Uniforms CSC6/30/2021N30
52CR00000625SUMM001RMA000025852.88000-3.7INar@indyhanger.com1/9/2023Ontario Inc/JR Holdings 17091076/30/2021N30
53CR00000624APPE002RMA0000257300.83000-20.83OHar@indyhanger.com10/19/2022Wildman Uniforms CSC6/30/2021N30
54CR00000623MBLE002RMA000025670.5000-4.61OHar@indyhanger.com10/19/2022Munro's Uniform Services CSC6/30/2021N30
55CR00000621ALSL003RMA0000255730.10000NVar@indyhanger.com10/10/2022West Chester Cleaner's7/20/2021N30
56CR00000620PRES002RMA000025467.75000-4.74INar@indyhanger.com10/10/2022Cintas Salt Lake City UT (S 4370)10/1/2021CIN
57CR00000619YOUR001RMA000025384.03000-5.67OHar@indyhanger.com10/10/2022Appearance Plus Cleaners, Inc.7/27/2021N30
58CR00000618COLU001RMA000025248.38000-3.38OHar@indyhanger.com10/10/2022Miller Textile Services CSC7/27/2021N30
59CR00000617PEER001RMA000025152.43000-3.43INar@indyhanger.com10/3/2022Cintas Sacramento CA National Dr8/12/2021N60
60CR00000616TRIN001RMA000025029.25000-2.05INar@indyhanger.com10/19/2022Haldur Inc DBA Penn 60 Minute Cleaners3/23/2022CC
61CR00000615GENE004RMA00002497,879.36000-442.38KYar@indyhanger.com11/18/2022Cintas Culpeper VA8/18/2022CIN
62CR00000615GENE004RMA0000249861.64000-442.38KYar@indyhanger.com11/18/2022EJ Thomas - Fairfield Oh8/18/2022N30
63CR00000612TDCB002RMA00002488270000OHar@indyhanger.com1/11/2023Groesbeck Cleaners8/19/2022DUE
64CR00000609MBLE003RMA000024715.63000-1.13OHar@indyhanger.com9/27/2022Marion County Auditor's Office8/19/2022
65CR00000608MORE001RMA000024657.29000-4.01INar@indyhanger.com10/4/2022LKH Inc. / Dionne Supply8/19/2022N30
66CR00000606MBLE003RMA000024564.41000-4.66OHar@indyhanger.com9/21/2022Milford Cleaners8/19/2022DUE
67CR00000605CLAS001RMA0000244152.37000-15.54INar@indyhanger.com9/21/2022Jubilee Dry Cleaners North8/19/2022DUE
68CR00000605CLAS001RMA00002445.72000-15.54INar@indyhanger.com9/21/2022Mr. Kelley's Kleaners8/4/2021N30
69CR00000605CLAS001RMA000024479.45000-15.54INar@indyhanger.com9/21/2022Marion County Auditor's Office8/23/2022
70CR00000601CANN001RMA00002431050000GAar@indyhanger.com1/19/2023Dunhill Tuxedos8/1/2021N30
71CR00000600MART003RMA000024282.35000-6.18OHar@indyhanger.com9/26/2022Save Cleaners8/1/2021N30
72CR00000598SOUT002RMA000024128.62000-1.62KYar@indyhanger.com9/14/2022Fabricare Center Sandy Springs8/1/2021N30
73CR00000597GRAN001RMA000024042000-2.94INar@indyhanger.com9/20/2022Virginia Linen Petersburg VA CSC8/30/2022N30
74CR00000596TRIN001RMA000023946.07000-5.29INar@indyhanger.com9/13/2022Appearance Plus Cleaners, Inc.7/27/2021N30
75CR00000596TRIN001RMA000023934.72000-5.29INar@indyhanger.com9/13/2022Your Cleaners6/30/2021N30
76CR00000593CAPRI01RMA000023823.85000-1.79OHar@indyhanger.com9/8/2022LKH Inc. / Dionne Supply10/1/2021N30
77CR00000592DELU001RMA0000237330000OHar@indyhanger.com9/9/2022A-One Fine Dry Cleaning10/1/2021CC
78CR00000591APPE002RMA00002362130000OHar@indyhanger.com9/6/2022Cintas Salt Lake City UT (S 4370)11/8/2021CIN
79CR00000588YANK001RMA000023458.750000NJar@indyhanger.com8/30/2022Cintas Houston TX (Kress)5/31/2022CIN
80CR00000585TDCP003RMA0000233169.69000-7.68OHar@indyhanger.com9/1/2022Cintas Houston TX (Kress)5/31/2022CIN
81CR00000584TDCG001RMA00002321680000OHar@indyhanger.com9/29/2022Cintas Corporation11/7/2021CIN
82CR00000582Hald001RMA000023110.25000-0.72INar@indyhanger.com8/24/2022Haldur Inc DBA Penn 60 Minute Cleaners6/1/2022N30
83CR00000583TDCL002RMA000023063.89000-3.9OHar@indyhanger.com8/25/2022Saveon Corp.6/2/2022CC
84CR00000579ATLA001RMA0000229106.50000KYar@indyhanger.com8/26/2022Cintas Spartanburg SC11/8/2021CIN
85CR00000578Hald001RMA0000228104000-7.28INar@indyhanger.com8/19/2022Fabriclean Supply Lenexa Ks11/1/2021N30
86CR00000573SUNS002RMA0000227225.85000-13.55KYar@indyhanger.com8/19/2022A-One Fine Dry Cleaning11/1/2021CC
87CR00000571LIBE001RMA000022669.50000OHar@indyhanger.com8/18/2022Unifirst Simpsonville SC6/3/2022CC
88CR00000570MORE001RMA000022552000-3.64INar@indyhanger.com8/23/2022Save Cleaners10/28/2021CC
89CR00000569CINC012RMA000022450.650000VAar@indyhanger.com8/18/2022EJ/NS Farrington & Co10/27/2021N30
90CR00000566POIN001RMA000022321.96000-1.54OHar@indyhanger.com9/26/2022Unifirst Fort Worth TX10/19/2021N45
91CR00000565DELU003RMA000022217.1000-1.33OHar@indyhanger.com8/16/2022Cintas Normal IL10/18/2021CIN
92CR00000564SPRI002RMA000022141.95000-3.27OHar@indyhanger.com8/24/2022Cintas Pico Rivera CA6/3/2022CIN
93CR00000563SPRI004RMA000022057.5000-4.49OHar@indyhanger.com8/19/2022Big Apple Cleaners11/8/2021CC
94CR00000554GENE004RMA0000219233.2000-13.2KYar@indyhanger.com8/8/2022Martinizing Gahanna11/9/2021CC
95CR00000553ROCK001RMA000021857.15000-4.29OHar@indyhanger.com8/11/2022Columbus Lace11/10/2021N30
96CR00000551GRAN001RMA000021757.29000-4.01INar@indyhanger.com8/9/2022Cintas Gilroy CA11/19/2021CIN
97CR00000548STER002RMA0000216241.54000-18.12OHar@indyhanger.com11/10/2022Unifirst Phoenix AZ5/19/2022N45
98CR00000547SIMP001RMA0000215239.96000-16.2OHar@indyhanger.com9/23/2022Cintas Gilroy CA11/19/2021CIN
99CR00000545FRESH01RMA0000214105.76000-4.12OHar@indyhanger.com8/24/2022Cintas Gilroy CA11/19/2021CIN
100CR00000542UNIF001RMA00002136,340.500000OHar@indyhanger.com7/28/2022Unifirst Indianapolis IN5/19/2022N45
101CR00000544MILT001RMA00002121,153.46000-75.46INar@indyhanger.com7/28/2022Wausau Chemical11/18/2021N30
102CR00000543MART004RMA0000211121.5000-8.51OHar@indyhanger.com7/28/2022Cintas Lancaster NY11/18/2021CIN
103CR00000541LAXA001RMA000021064.95000-4.55INar@indyhanger.com7/28/2022Cintas Lancaster NY11/18/2021CIN
104CR00000538UNIW002RMA00002091,924.200000NYar@indyhanger.com7/18/2022Tide-RDR Family Ventures ZZZ, LLC11/18/2021N30
105CR00000540BART001RMA0000208150.5000-11.29OHar@indyhanger.com7/28/2022Tide Cleaners Madison Heights MI11/17/2021CC
106CR00000539LACL001RMA000020762.01000-4.06OHar@indyhanger.com7/25/2022Magic Cleaners and Laundry5/23/2022N30
107CR00000536UNIS006RMA0000206384.650000CTar@indyhanger.com7/11/2022Tide-RDR Family Ventures ZZZ, LLC11/16/2021N30
108CR00000535GENE004RMA000020510000-0.6KYar@indyhanger.com7/21/2022Tide Cleaners Omaha NE11/16/2021CC
109CR00000529CITY003RMA00002041,040.00000-67.6OHar@indyhanger.com7/6/2022Sitex Corp. CSC5/25/2022N30
110CR00000528Hald001RMA000020358.74000-6.15INar@indyhanger.com7/5/2022Carriage Cleaners-Maryland Heights5/25/2022CC
111CR00000528Hald001RMA000020335.31000-6.15INar@indyhanger.com7/5/2022Cintas Salt Lake City UT (S 5350)10/18/2021CIN
112CR00000517DALE001RMA000020179.55000-5.55OHar@indyhanger.com6/23/2022Cintas Bedford VA10/18/2021CIN
113CR00000515FOXC001RMA00002001,169.15000-87.69OHar@indyhanger.com6/22/2022Cintas Vidalia GA10/18/2021CIN
114CR00000511MAMC001RMA000019961.81000-4.31OHar@indyhanger.com6/16/2022Unifirst Indianapolis IN10/18/2021N45
115CR00000510PERF001RMA000019863.67000-4.17INar@indyhanger.com6/14/2022Unifirst Jacksonville FL10/14/2021N45
116CR00000503UNIN002RMA00001971,371.500000TNar@indyhanger.com6/28/2022Morrell's Cleaners10/13/2021CC
117CR00000499CAPRI01RMA000019645.8000-3.44OHar@indyhanger.com6/10/2022Morellis Cleaners10/13/2021CC
118CR00000498MILT001RMA00001951130000INar@indyhanger.com6/8/2022Classic Cleaners (IN)10/13/2021N30
119CR00000498MILT001RMA0000195430000INar@indyhanger.com6/8/2022Cintas Emmaus PA10/13/2021CIN
120CR00000497SPRI002RMA000019429.250000OHar@indyhanger.com6/8/2022Cintas Tampa FL10/13/2021CIN
121CR00000496GRAN001RMA000019365000-4.55INar@indyhanger.com6/14/2022Cintas Phoenix AZ (Hadley)10/13/2021CIN
122CR00000487MILT001RMA00001921960000INar@indyhanger.com5/27/2022Cintas Greenville SC10/12/2021CIN
123CR00000486CLOT002RMA000019159.99000-4.5OHar@indyhanger.com5/27/2022Ajax Linen & Uniform CSC-Out of business6/22/2022N30
124CR00000477TDCG001RMA00001906840000OHar@indyhanger.com5/19/2022Ajax Linen & Uniform CSC-Out of business6/22/2022N30
125CR00000476TDCL001RMA000018922.63000-1.38OHar@indyhanger.com5/19/2022Cintas Decatur GA10/1/2021CIN
126CR00000475SNOW002RMA0000188188.88000-13.18OHar@indyhanger.com5/19/2022Cintas Frankfort IN6/27/2022CIN
127CR00000474YOSE001RMA0000187259.50000CAar@indyhanger.com5/23/2022Cintas Midland TX6/27/2022CIN
128CR00000464TDCI001RMA0000186134.82000-8.82INar@indyhanger.com5/16/2022Cintas Tampa FL6/27/2022CIN
129CR00000458DELU001RMA000018535.08000-2.14OHar@indyhanger.com5/13/2022Milford Cleaners6/27/2022DUE
130CR00000457CRDN001RMA000018457.29000-4.01INar@indyhanger.com5/23/2022Cintas Kennesaw GA10/15/2021CIN
131CR00000456APPE002RMA000018314.01000-1.01OHar@indyhanger.com5/13/2022Fabricare Center Sandy Springs10/1/2021N30
132CR00000454CRDN002RMA000018235.02000-3.19OHar@indyhanger.com5/9/2022Cintas Albany GA10/15/2021CIN
133CR00000454CRDN002RMA000018210.67000-3.19OHar@indyhanger.com5/9/2022Cintas Columbus OH10/15/2021CIN
134CR00000453ROSS002RMA000018194000-7.05OHar@indyhanger.com5/20/2022Cintas Indianapolis IN (Georgetown)10/18/2021CIN
135CR00000452KRON001RMA000018056.850000OHar@indyhanger.com5/9/2022Cintas Midland TX10/18/2021CIN
136CR00000451APPE002RMA0000179130000OHar@indyhanger.com5/9/2022Cintas Indianapolis IN (Park Davis)10/18/2021CIN
137CR00000430MART004RMA000017752000-3.64OHar@indyhanger.com4/22/2022Cintas Greenville SC10/18/2021CIN
138CR00000421CRDN001RMA0000176148.5000-10.4INar@indyhanger.com4/26/2022Cintas Bossier City LA10/18/2021CIN
139CR00000420MART004RMA000017517.55000-1.23OHar@indyhanger.com4/22/2022Unifirst Brooklyn Heights OH6/14/2022N45
140CR00000419MART002RMA0000174110000-8.25OHar@indyhanger.com4/19/2022Eastern Hills Dry Cleaners6/14/2022CC
141CR00000416TDCM002RMA000017341.50000OHar@indyhanger.com4/13/2022Unifirst Pompano Beach FL6/14/2022N45
142CR00000415TDCP003RMA000017240000-2.8OHar@indyhanger.com4/19/2022Unifirst Amarillo TX6/14/2022N45
143CR00000418ROBI001RMA00001711,764.48000-137.63WIar@indyhanger.com4/25/2022Cintas Midland TX6/14/2022CIN
144CR00000410GRAN001RMA000016973.83000-4.83INar@indyhanger.com3/31/2022Unifirst Pompano Beach FL6/14/2022N45
145CR00000403ARMS001RMA0000168438.9000-30.72INar@indyhanger.com3/28/2022Cintas Dallas TX10/15/2021CIN
146CR00000402MILT001RMA0000167119.80000INar@indyhanger.com4/25/2022Cintas Gilroy CA10/15/2021CIN
147CR00000401Hald001RMA000016625.63000-1.68INar@indyhanger.com3/24/2022Cintas Dallas TX10/15/2021CIN
148CR00000399TRIN001RMA000016588.28000-5.78INar@indyhanger.com3/21/2022Unifirst Brooklyn Heights OH10/15/2021N45
149CR00000351APPE002RMA0000164287.83000-20.83OHSecretary@IndyHanger.com3/10/2022Cintas North Jackson OH10/15/2021CIN
150CR00000348MILT001RMA00001631220000INSecretary@IndyHanger.com3/9/2022Get Pressed Drycleaner11/19/2021N30
151CR00000342CINB004RMA00001614,631.400000LASecretary@IndyHanger.com3/2/2022Kingdom Cleaners6/30/2021N30
152CR00000341CINP008RMA00001603950000ORSecretary@IndyHanger.com12/29/2022Appearance Plus Cleaners, Inc.6/30/2021N30
153CR00000328CLAS001RMA0000159171.47000-15.4INSecretary@IndyHanger.com3/1/2022Plymate CSC2/9/2021N30
154CR00000328CLAS001RMA000015963.93000-15.4INSecretary@IndyHanger.com3/1/2022Clothes Clinic - CSC2/9/2021N30
155CR00000327GRAN001RMA0000158114.58000-8.02INSecretary@IndyHanger.com3/3/2022City Uniforms & Linens CSC2/9/2021N30
156CR00000326Hald001RMA0000157151.85000-9.93INSecretary@IndyHanger.com3/1/2022Cintas Corporation2/9/2021N60
157CR00000325MILT001RMA0000156370000INSecretary@IndyHanger.com3/1/2022Cintas Corporation2/9/2021N60
158CR00000320APPE002RMA00001550.18000-2.57OHSecretary@IndyHanger.com2/25/2022Cintas Corporation2/9/2021N60
159CR00000320APPE002RMA000015535.34000-2.57OHSecretary@IndyHanger.com2/25/2022Ajax Linen & Uniform CSC-Out of business2/9/2021N30
160CR00000319SPRI002RMA000015492.5000-7.22OHSecretary@IndyHanger.com3/14/2022Unifirst Indianapolis IN2/9/2021N45
161CR00000318CINL008RMA000015310,605.60000-636.34KYSecretary@IndyHanger.com2/24/2022Andy's Cleaners2/8/2021N30
162CR00000314TDCL002RMA000015251.65000-3.15OHSecretary@IndyHanger.com2/22/2022Cintas Corporation2/5/2021N60
163CR00000313ATLA001RMA0000151277000-16.62KYSecretary@IndyHanger.com3/1/2022Cintas Corporation2/5/2021N60
164CR00000312Hald001RMA000015083.46000-5.46INSecretary@IndyHanger.com2/21/2022Cintas Corporation2/5/2021N60
165CR00000317YOSE001RMA0000149216.60000CASecretary@IndyHanger.com5/17/2022Cintas Corporation2/5/2021N60
166CR00000316YOSE001RMA0000148393.20000CASecretary@IndyHanger.com5/17/2022Cintas Corporation2/5/2021N60
167CR00000310MILT001RMA00001471960000INSecretary@IndyHanger.com2/21/2022Cintas Corporation2/9/2021N60
168CR00000309ROCK001RMA000014592.45000-6.45OHSecretary@IndyHanger.com2/21/2022Clean Uniform Highland IL CSC2/9/2021N30
169CR00000308TDCA001RMA000014448.50000OHSecretary@IndyHanger.com2/21/2022Cintas Corporation2/9/2021N60
170CR00000307DELU001RMA000014242.6000-2.6OHSecretary@IndyHanger.com2/21/2022Cintas Corporation2/9/2021N60
171CR00000306AONE001RMA0000141413.06000-29.56OHSecretary@IndyHanger.com2/21/2022Cintas Corporation2/10/2021N60
172CR00000306AONE001RMA0000141-4.5000-29.56OHSecretary@IndyHanger.com2/21/2022Cintas Corporation2/10/2021N60
173CR00000305TDCA001RMA0000140970000OHSecretary@IndyHanger.com2/21/2022Cintas Corporation2/10/2021N60
174CR00000304SPRI005RMA000013948.99000-7.49OHSecretary@IndyHanger.com2/21/2022Phelps Uniforms CSC2/10/2021N30
175CR00000304SPRI005RMA000013954.5000-7.49OHSecretary@IndyHanger.com2/21/2022Sunshine Cleaners IN2/10/2021N30
176CR00000303UNIM001RMA00001382,940.300000ILSecretary@IndyHanger.com3/2/2022Cintas Corporation2/10/2021N60
177CR00000302UNIM001RMA00001374,943.250000ILSecretary@IndyHanger.com3/2/2022Cintas Corporation2/9/2021N60
178CR00000301CINP003RMA00001363,652.680000OHSecretary@IndyHanger.com4/8/2022Cintas Corporation2/9/2021N60
179CR00000300STAR007RMA000013558.32000-3.82INSecretary@IndyHanger.com2/21/2022Cintas Corporation2/9/2021N60
180CR00000299APEX001RMA00001341090000NVSecretary@IndyHanger.com2/21/2022Cintas Corporation2/9/2021N60
181CR00000298UNIO002RMA00001331,061.450000CASecretary@IndyHanger.com2/21/2022National Uniform Rentals/National Cleaners2/9/2021N30
182CR00000297TDCF001RMA0000132134.82000-8.82INSecretary@IndyHanger.com2/21/2022Cintas Corporation2/9/2021N60
183CR00000296TDCF001RMA000013154.57000-3.57INSecretary@IndyHanger.com2/21/2022Cintas Corporation2/9/2021N60
184CR00000291UNIL002RMA00001306,545.000000KYSecretary@IndyHanger.com2/3/2022Cintas Corporation2/9/2021N60
185CR00000290TDCB002RMA0000129510000OHSecretary@IndyHanger.com3/23/2022Cintas Corporation2/9/2021N60
186CR00000289MART003RMA000012850.50000OHSecretary@IndyHanger.com2/1/2022Cintas Corporation2/5/2021N60
187CR00000288TDCL001RMA000012744.2000-2.7OHSecretary@IndyHanger.com2/1/2022Cintas Corporation2/5/2021N60
188CR00000270LACL001RMA0000126119.98000-8.4OHSecretary@IndyHanger.com3/23/2022Paris Cleaners2/5/2021N30
189CR00000269TRIN001RMA000012526.26000-1.72INSecretary@IndyHanger.com1/25/2022Cintas Corporation2/5/2021N60
190CR00000267TDCM002RMA00001249.650000OHSecretary@IndyHanger.com1/27/2022Cintas Corporation2/3/2021N60
191CR00000266GEIS001RMA000012369.55000-4.55INSecretary@IndyHanger.com1/21/2022Village Cleaners2/3/2021N30
PostedInv20Jan2023All
Cell Formulas
RangeFormula
F2:F191F2=SUMIF($C$2:$C$19037,C2,$E$2:$E$19037)
 
Upvote 0
Perhaps it would help if we did this a little differently. Unless you have a simpler way to do it.

The Accum Freight property on the Customer Order is supposed to be dead accurate. It gets updated when a shipment is created.

The Posted Invoices Table is all Invoices and adjustments that have been applied against every order. There can be multiple shipments against the same order and multiple RMAS and credits. So, there is often more than one row with the same order number. So, I tried shrinking it down to one row to make my initial comparisons. This is how I did it:


1. I inserted a column called SumIF with the following formula: =SUMIF($C$2:$C$19037,C2,$E$2:$E$19037) The intent here was to basically turn the One (Customer Orders) to Manay (Posted Invoices) relationship into a One to One by basically giving me a Accum Freight column (SumIf) on Posted Invoices.
2. I then highlighted all data and went to Data --> Filter
3. I then clicked on the column header drop down for the Sumif column and unselected 0 dollar values to remove all the freight options that sum'd as 0

The above allowed me to reduce the total rows I was looking at from 19036 to 5453
4. I then highlighted all data --> Clicked Data --> Remove Duplicates --> Unselected all --> Selected Orders (Needed to do this because I already have a Sumif with the total of all freight costs Invoiced against each order, so I don't need multiple rows for the same order.

Removing duplicates got me down to 3,654 records.

5. I then added a column for VLookup and inserted the following formula: =VLOOKUP(C296, AllCos_20Jan2023All!$A$2:$F$14048,6,FALSE) This allowed me to put the actual Accum Freight from the Customer Orders onto the same worksheet I built the Sumif column (Posted Invoices)
6. I then inserted another column and did a simple match between the Sumif and the Accum Freight I brought in from Customer orders.
7. I then filtered the Match column to only show results of False. These would be the ones where the Accum Freight from the Customer Order Table doesn't match the SumIf result I created on this one. This got me down to 415 records. Now at this point I'm trying to figure out why these 414 discrepancies exist without going through them one at a time.
8. On the 415 discrepancies I noticed I noticed the Accum Freight from Customer Orders is always higher than the Posted Invoice Amounts. So, I inserted another column that subtracts the SumIf column from the Accum Freight column. This is where I discovered, if the Accum Freight information from the Customer Order is correct, then we have undercharged freight to the tune of $197,847.60.

My theory is that the Accum Freight data may not be 100% accurate even though I'm told its supposed to be.

Now I have a list of Customer Orders (414) with discrepancies between Custom Order Accum Freight and Posted Invoice Freight. So...
I'm wondering if the best approach for me now is to do this:
1. Insert at least 10 columns to the right of my data for the 414 records
2. Column 1 - Create a formula that looks at an unaltered original version of the Posted Invoices Dump that still has over 19,000 rows and display the first Freight amount it finds
3. Column 2 - same as above but for the second Freight Amount it finds
4. Column 3-10 - And so on.

Is there a formula that would look for the 1st, 2nd, and 3rd, and so on records it finds that matches a criteria in a separate worksheet?
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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