Automated Transfer Of Non-Contiguous Data To Columnar Table

Sour Deaux

New Member
Sep 19, 2019
I am working with a user form (Cost Sheet) that allows for input into disjointed fields that was created for aesthetic :LOL: purposes, but does not lend itself to easy data analysis. I'm attempting to automate the process of collecting all of the data line combinations and put them in columnar table format. When I am analyzing these forms, there are several of them that have to be processed. I have been able to put together VBA to identify a selected folder and copy ranges from each workbook, but not sure how to arrange it by combining each element of the input sections. I need someone to help me get started with VBA for this process. Thank you.

2Supplier NameSupplier AAlignment to Warehouses
3City, StateChicago, ILLos Angeles, CAChicago, ILLos Angeles, CA
4Account Number123456789Zip60007900016000790001
5Collect Pricing & Capacity by Ship PointPrepaid Pricing by Item by Warehouse
7City, State5-Digit ZipShipping Location 1Shipping Location 22010202020302040
8Item InformationLos Angeles, CA90001Chicago, IL60007New Orleans, LAPhoenix, AZSt. Louis, MOLas Vegas, NV
9Shipping Location 1Los Angeles, CA90001Collec CostCaseCollect CostCaseDelivered PriceDelivered PriceDelivered PriceDelivered Price
10Shipping Location 2Chicago, IL60007Product NumberDescriptionWeightCubePrice/CaseCapacityPrice/CaseCapacity
11Shipping Location 3111Widget 15.70.3$1.001,000$1.501,000$1.60$1.20$1.55$1.10
12Shipping Location 4222Widget 25.20.4$1.001,000$1.501,000$1.60$1.20$1.55$1.10
13Shipping Location 5
14Shipping Location 6
15Shipping Location 7
16Shipping Location 8
17Shipping Location 9
18Shipping Location 10
19Shipping Location 11
20Shipping Location 12
21Shipping Location 13
22Shipping Location 14
23Shipping Location 15

Cost Sheet

1SupplierAccount NumberShipping CityShipping StateShipping ZipProduct NumberProduct DescriptionProduct WeightProduct CubeFreight TermCase CostShipping Location CapacityWarehouseTransportation Cost (added by a separate process)Warehouse Landed Cost
2Supplier A123456789Los AngelesCA90001111Widget 15.70.3Collect$1.001,0002010$0.10$1.10
3Supplier A123456789Los AngelesCA90001111Widget 15.70.3Collect$1.001,0002020$0.07$1.07
4Supplier A123456789Los AngelesCA90001111Widget 15.70.3Collect$1.001,0002030$0.05$1.05
5Supplier A123456789Los AngelesCA90001111Widget 15.70.3Collect$1.001,0002040$0.08$1.08
6Supplier A123456789ChicagoIL60007111Widget 15.70.3Collect$1.501,0002010$0.10$1.60
7Supplier A123456789ChicagoIL60007111Widget 15.70.3Collect$1.501,0002020$0.07$1.57
8Supplier A123456789ChicagoIL60007111Widget 15.70.3Collect$1.501,0002030$0.05$1.55
9Supplier A123456789ChicagoIL60007111Widget 15.70.3Collect$1.501,0002040$0.08$1.58
10Supplier A123456789Los AngelesCA90001222Widget 25.20.4Collect$1.001,0002010$0.10$1.10
11Supplier A123456789Los AngelesCA90001222Widget 25.20.4Collect$1.001,0002020$0.07$1.07
12Supplier A123456789Los AngelesCA90001222Widget 25.20.4Collect$1.001,0002030$0.05$1.05
13Supplier A123456789Los AngelesCA90001222Widget 25.20.4Collect$1.001,0002040$0.08$1.08
14Supplier A123456789ChicagoIL60007222Widget 25.20.4Collect$1.501,0002010$0.10$1.60
15Supplier A123456789ChicagoIL60007222Widget 25.20.4Collect$1.501,0002020$0.07$1.57
16Supplier A123456789ChicagoIL60007222Widget 25.20.4Collect$1.501,0002030$0.05$1.55
17Supplier A123456789ChicagoIL60007222Widget 25.20.4Collect$1.501,0002040$0.08$1.58
18Supplier A123456789Los AngelesCA90001111Widget 15.70.3Prepaid$1.201,0002020$0.00$1.20
19Supplier A123456789Los AngelesCA90001111Widget 15.70.3Prepaid$1.101,0002040$0.00$1.10
20Supplier A123456789ChicagoIL60007111Widget 15.70.3Prepaid$1.601,0002010$0.00$1.60
21Supplier A123456789ChicagoIL60007111Widget 15.70.3Prepaid$1.551,0002030$0.00$1.55
22Supplier A123456789Los AngelesCA90001222Widget 25.20.4Prepaid$1.201,0002020$0.00$1.20
23Supplier A123456789Los AngelesCA90001222Widget 25.20.4Prepaid$1.101,0002040$0.00$1.10
24Supplier A123456789ChicagoIL60007222Widget 25.20.4Prepaid$1.601,0002010$0.00$1.60
25Supplier A123456789ChicagoIL60007222Widget 25.20.4Prepaid$1.551,0002030$0.00$1.55

Desired Output

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Latest member

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
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 "".
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