Automated Transfer Of Non-Contiguous Data To Columnar Table

Sour Deaux

New Member
Joined
Sep 19, 2019
Messages
1
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.

ABCDEFGHIJKLMNOPQRST
1
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
6
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
24
25
26
27
28
29
30

<tbody>
</tbody>
Cost Sheet




ABCDEFGHIJKLMNO
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

<tbody>
</tbody>
Desired Output
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,095,277
Messages
5,443,501
Members
405,237
Latest member
rezaria

This Week's Hot Topics

Top