Match Bills of Lading Broken Up into Varying Numbers of Rows - Mini Sheet Attached

T

toonces

New Member
Joined
Dec 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

I've spent 2 days trying to figure out a solution to this but my brain is in knots :)

The attached spreadsheet is Bill of Lading data from 2 different sources that has been combined into one file. I need to find the BOL's that match between Sources depending on the total Billed Units (column E) for the BOL number (column F), and Product Code (column I) . The problem is that it's not always a 1-row for 1-row comparison between the 2 sources. In the example below, you can see where the 2 rows in dark green are actually a 1-row for 1-row match: BOL 248247, same product code, same billed units, each from a different source. But the next 3 rows (in blue) are different--it's still a match between the BOL number, total billed units and product code--but ABC Source is only on one row with 12,001 billed units and Libra Source is broken up into 2 rows with 1,100 units on one row and 10,901 units on the other row.

I'm trying to find a way to label these rows as a match even though the units are sometimes broken up into multiple rows. The number of rows is not consistent so I need a solution that will sum and total by BOL number and Product Code and Source, then see if there's a match between Source Libra and Source ABC.

I can use helper rows or other sheets.

As a side note, I already have some formulas in the sheet to pull out BOL's where the billed units net to zero, and other classifications that were easier to make with if statements.

The attached spreadsheet is a sample, my real data has about 16,000 rows. I color coded the rows that match by BOL number. My hope was that I could do this match calculation in a helper row, then add the result of that row to my IF statement in the Classification column (column B).

Any help would be greatly appreciated!!

BOL Matching Sample.xlsx
ABCDEFGHIJKLMNOP
1Period:11/1/20211 - Match - Report from Libra5 - Diversion - Report from Libra
2Sort by BOL, BOL Date, Prod Code, Billed Units2 - Product Mismatch - Report from Libra 6 - Report Libra - 3rd party
33 - In RA but not Libra - Report from RA7 - Nets to zero
44 - In Libra but not RA - Report Libra8 - Prior period
5
6False = Nets to Zero - Same SourceClassificationNA-SourceFileNameBilledUnitsBillOfLadingNumberInvoiceDateInvoiceNumberProductCodeBuyerLegalNameSellerLegalNameCustom_String_04BillofLadingDatePeriodfiling_entityDiversionNumber
7FALSE8 - Prior periodLIBRANY-Superway-Recp-202111-24981647402021-11-162103871640555SUPERWAY LLCMart Goods Company LPSUWY10/4/20212021-10SUWY
8FALSE8 - Prior periodLIBRANY-Superway-Recp-20211124981647402021-11-022103714655555SUPERWAY LLCMart Goods Company LPSUWY10/4/20212021-10SUWY
9TRUE8 - Prior periodLIBRANY-Superway-Recp-20211124981647402021-11-162103871639555SUPERWAY LLCMart Goods Company LPSUWY10/4/20212021-10SUWY
10FALSE8 - Prior periodLIBRANY-Superway-Recp-202111-19981647412021-11-162103871660555SUPERWAY LLCMart Goods Company LPSUWY10/4/20212021-10SUWY
11FALSE8 - Prior periodLIBRANY-Superway-Recp-20211119981647412021-11-022103714654555SUPERWAY LLCMart Goods Company LPSUWY10/4/20212021-10SUWY
12TRUE8 - Prior periodLIBRANY-Superway-Recp-20211119981647412021-11-162103871642555SUPERWAY LLCMart Goods Company LPSUWY10/4/20212021-10SUWY
13FALSE8 - Prior periodLIBRANY-Superway-Recp-202111-54971648752021-11-162103871658555SUPERWAY LLCMart Goods Company LPSUWY10/6/20212021-10SUWY
14FALSE8 - Prior periodLIBRANY-Superway-Recp-20211154971648752021-11-162103871637555SUPERWAY LLCMart Goods Company LPSUWY10/6/20212021-10SUWY
15TRUE8 - Prior periodLIBRANY-Superway-Recp-20211154971648752021-11-022103714656555SUPERWAY LLCMart Goods Company LPSUWY10/6/20212021-10SUWY
16TRUE6 - Report Libra - 3rd partyLIBRANY-Superway-Recp-20211140342342902021-11-0310938505555SUPERWAY LLCSMITHSUWY11/3/20212021-11SUWY
17TRUE6 - Report Libra - 3rd partyLIBRANY-Superway-Recp-20211130332347202021-11-0610972619555SUPERWAY LLCSMITHSUWY11/6/20212021-11SUWY
18TRUE6 - Report Libra - 3rd partyLIBRANY-Superway-Recp-20211150372347212021-11-0610972618555SUPERWAY LLCSMITHSUWY11/6/20212021-11SUWY
19TRUE6 - Report Libra - 3rd partyLIBRANY-Superway-Recp-20211140312368892021-11-2611005228555SUPERWAY LLCSMITHSUWY11/26/20212021-11SUWY
20TRUE6 - Report Libra - 3rd partyLIBRANY-Superway-Recp-20211140312368932021-11-2611005172555SUPERWAY LLCSMITHSUWY11/26/20212021-11SUWY
21TRUE#N/ALIBRANY-Superway-Recp-20211148502482472021-11-022103710980E88SUPERWAY LLCMart Goods Company LPSUWY11/1/20212021-11SUWY
22TRUE#N/AABCNY-ABC to SUWY-Sale-202111485024824711/2/2021 0:002103710980E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/1/20212021-11SUWY
23TRUE#N/ALIBRANY-Superway-Recp-20211111002482492021-11-022103711466E88SUPERWAY LLCMart Goods Company LPSUWY11/1/20212021-11SUWY
24TRUE#N/ALIBRANY-Superway-Recp-202111109012482492021-11-022103711466E88SUPERWAY LLCMart Goods Company LPSUWY11/1/20212021-11SUWY
25TRUE#N/AABCNY-ABC to SUWY-Sale-2021111200124824911/2/2021 0:002103711466E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/1/20212021-11SUWY
26TRUE#N/ALIBRANY-Superway-Recp-20211133502482662021-11-022103711765E88SUPERWAY LLCMart Goods Company LPSUWY11/1/20212021-11SUWY
27TRUE#N/AABCNY-ABC to SUWY-Sale-202111485024826611/2/2021 0:002103711765E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/1/20212021-11SUWY
28TRUE#N/ALIBRANY-Superway-Recp-20211115002482672021-11-042103739196967SUPERWAY LLCMart Goods Company LPSUWY11/1/20212021-11SUWY
29TRUE#N/AABCNY-ABC to SUWY-Sale-202111150024826711/4/2021 0:002103739196967Superway LLC - LOI-WHMart Goods Company LPSUWY11/1/20212021-11SUWY
30TRUE#N/ALIBRANY-Superway-Recp-202111104002482672021-11-042103739196E88SUPERWAY LLCMart Goods Company LPSUWY11/1/20212021-11SUWY
31TRUE#N/AABCNY-ABC to SUWY-Sale-2021111040024826711/4/2021 0:002103739196E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/1/20212021-11SUWY
32TRUE#N/ALIBRANY-Superway-Recp-20211125002483302021-11-032103724398967SUPERWAY LLCMart Goods Company LPSUWY11/2/20212021-11SUWY
33TRUE#N/AABCNY-ABC to SUWY-Sale-202111250024833011/3/2021 0:002103724398967Superway LLC - LOI-WHMart Goods Company LPSUWY11/2/20212021-11SUWY
34TRUE#N/ALIBRANY-Superway-Recp-20211111002483302021-11-032103724398E88SUPERWAY LLCMart Goods Company LPSUWY11/2/20212021-11SUWY
35TRUE#N/ALIBRANY-Superway-Recp-20211179002483302021-11-032103724398E88SUPERWAY LLCMart Goods Company LPSUWY11/2/20212021-11SUWY
36TRUE#N/AABCNY-ABC to SUWY-Sale-202111900024833011/3/2021 0:002103724398E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/2/20212021-11SUWY
37TRUE#N/ALIBRANY-Superway-Recp-20211114862492332021-11-162103866830E88SPEEDWAY LLCMart Goods Company LP11/15/20212021-11SUWY
38TRUE#N/ALIBRANY-Superway-Recp-20211114862492332021-11-162103866830E88SPEEDWAY LLCMart Goods Company LP11/15/20212021-11SUWY
39TRUE#N/ALIBRANY-Superway-Recp-20211158502492332021-11-162103866830E88SPEEDWAY LLCMart Goods Company LP11/15/20212021-11SUWY
40TRUE#N/AABC - RightangleNY-ABC to SUWY-Sale-202111882224923311/16/2021 0:002103866830E88Superway LLC - LOI-WHMart Goods Company LP11/15/20212021-11SUWY
41TRUE#N/ALIBRANY-Superway-Recp-202111120002492512021-11-172103880251E88SPEEDWAY LLCMart Goods Company LP11/16/20212021-11SUWY
42TRUE#N/AABC - RightangleNY-ABC to SUWY-Sale-2021111200024925111/17/2021 0:002103880251E88Superway LLC - LOI-WHMart Goods Company LP11/16/20212021-11SUWY
43FALSE7 - Nets to zeroLIBRANY-Superway-Recp-202111-120005805722021-11-232103939784E88SUPERWAY LLCMart Goods Company LPSUWY11/19/20212021-11SUWY
44TRUE#N/ALIBRANY-Superway-Recp-20211125005805722021-11-232103939811E88SUPERWAY LLCMart Goods Company LPSUWY11/19/20212021-11SUWY
45TRUE#N/AABCNY-ABC to SUWY-Sale-202111250058057211/23/2021 0:002103939811E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/19/20212021-11SUWY
46TRUE#N/ALIBRANY-Superway-Recp-20211195005805722021-11-232103939789E88SUPERWAY LLCMart Goods Company LPSUWY11/19/20212021-11SUWY
47TRUE#N/AABCNY-ABC to SUWY-Sale-202111950058057211/20/2021 0:002103917872E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/19/20212021-11SUWY
48FALSE7 - Nets to zeroLIBRANY-Superway-Recp-202111120005805722021-11-202103917872E88SUPERWAY LLCMart Goods Company LPSUWY11/19/20212021-11SUWY
49TRUE#N/AABCNY-ABC to SUWY-Sale-2021114200111330411/30/2021 0:002104011954E88Superway LLC - LOI-WHMart Goods Company LPSUWY11/29/20212021-11SUWY
50TRUE#N/ALIBRANY-Superway-Recp-20211110003830266702021-11-302104013005E88SUPERWAY LLCMart Goods Company LPSUWY11/29/20212021-11SUWY
51TRUE#N/ALIBRANY-Superway-Recp-20211150003830266702021-11-302104013005E88SUPERWAY LLCMart Goods Company LPSUWY11/29/20212021-11SUWY
Sheet1
Cell Formulas
RangeFormula
A7:A51A7=(COUNTIFS($E$7:E7,E7,$F$7:F7,F7,$I$7:I7,I7,$C$7:C7,C7)>(COUNTIFS($E$7:$E$15315,-E7,$F$7:$F$15315,F7,$I$7:$I$15315,I7,$C$7:$C$15315,C7)))
B7:B51B7=IFS(M7<$B$1,$M$4,K7<>"Mart Goods Company LP",$M$2,A7=FALSE,$M$3)
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi toonces,
what about using a Pivot Table to sort this out? So do a sum of billed units (col E) and use column C as a column header and the other columns as row headers. After the pivot table you could do 2 "simple" formulas with GETPIVOTDATA GETPIVOTDATA function to pull in the difference of the totals for ABC & LIBRA.
Good luck,
Koen
 
Upvote 0
Rijnsent said:
Hi toonces,
what about using a Pivot Table to sort this out? So do a sum of billed units (col E) and use column C as a column header and the other columns as row headers. After the pivot table you could do 2 "simple" formulas with GETPIVOTDATA GETPIVOTDATA function to pull in the difference of the totals for ABC & LIBRA.
Good luck,
Koen
Click to expand...
Actually that is what I ended up going with. I was thinking there was a way to do this with formulas so I wouldnt have to create another sheet but this still works. Thanks for the reply!
 
Upvote 0
toonces said:
Actually that is what I ended up going with. I was thinking there was a way to do this with formulas so I wouldnt have to create another sheet but this still works. Thanks for the reply!
Click to expand...
It is probably the smartest way: if you were to use formulas, they will recalculate every time and will recalculate over the complete range that's in your formula. As that's quite big in your case, that would mean a really slow recalculation. So the pivottable makes that much faster too.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
415
NaushadAnwar
N
S
  • Question
Macro to extract Columns based on multiple criteria
Replies
6
Views
3K
Trebor76
T
L
  • Solved
INDEX MATCH MAXIFS / AGGREGATE - Multiple criteria
Replies
3
Views
297
Fluff
Fluff
K
  • Solved
Index/Match Formula?
Replies
6
Views
496
Peter_SSs
Peter_SSs
K
  • Question
compare data across sheets for 6000 rows for each sheet individual based on sheet name
Replies
0
Views
193
KalilMe
K

Forum statistics

Threads
1,214,908
Messages
6,122,187
Members
449,071
Latest member
cdnMech

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