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

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...