如何在排序列表中合并类似的条目，而不使用VBA / Excel输出到工作表 [英] How to consolidate similar entries in a sorted list without output to a worksheet using VBA/Excel
问题描述
我的源数据是一个工作簿中的一系列12个工作表。每个工作表反映单个日历月。交易次数/运行次数是动态的 - 平均每月60个左右，所以我设置了一个限制为200的循环，因为这应该足以满足业务增长。
$ b $我目前的数据集是这样的，我有几个重复的交付（不同的货物/重量等，但是相同的交付地点）。我想将这些重复/类似的行合并到列表中的单个条目中，总计交付的数量，权重和交付成本，并增加一个计数器，以显示重复发送到该相应站点的数量。 p>
示例：2016年1月
从以下交货：已交付到：编号：重量：成本：
网站A网站B 10 100 $ 120.00
网站A网站C 5 20 $ 80.00
网站B网站C 2 30 $ 45.00
网站C 20 460 $ 375.00
摘要：
交付到：交货数量：件数：重量：成本：
现场B 1 10 100 $ 120.00
现场C 3 27 510 $ 500.00
我可以想办法通过将数据转储到垃圾工作表，但是，我想要一个内部的VBA解决方案，以便不需要这样的便笺本。
是动态的
对于任何给定的位置，重复交货的次数也是动态的。
我发现很难组合一种有效的方式来整合信息我的列表与上述参数，因为我还是非常新的VBA / Excel。
任何建议是赞赏，特别是如果你有示例代码 - 我知道我是什么想要，我只是不知道如何在VBA中实现它。
我的数组加载和传输到列表的示例如下所示（带有变量定义等
Set List = CreateObject（System.Collections.SortedList）
'按路线获取每月数据
对于工作表中的每个ws
如果ws.Name<> 汇总然后
调用DeleteHidden'删除活动工作表中的隐藏行/列如果有
通过ws
'循环通过工作表到207（〜3x
对于RowCount = 7到207
'检查每一行的日期（月/日/年）
d = DateValue（.Cells（RowCount，1））
如果List.Containskey（d）然后
arTemp =列表（d）
Else
ReDim arTemp（12）
如果
'每月总计
arTemp（0）= arTemp（0）+ .Cells（RowCount，1）'抓取条目日期/时间
arTemp（1）= arTemp（1）+ .Cells（RowCount ，2）'抓取交货日期/时间
arTemp（2）= arTemp（2）+ .Cells（RowCount，3）'抓取PU位置
arTemp（3）= arTemp（3）+ .Cells （RowCount，4）'抓住PU街
arTemp（4）= arTemp（4）+ .Cells（RowCount，5）'抓住PU城市/省/ PC
arTemp（5）= arTemp（5）+ .Cells（RowCount，6）
arTemp（6）= arTemp（6）+ .Cells（RowCount，7）'Grab Del Street
arTemp（7）= arTemp（7）+ .Cells（RowCount，8）'Grab Del City /省/ PC
arTemp（8）= arTemp（8）+ .Cells（RowCount，9）'抓取号码
arTemp（9）= arTemp（9）+ .Cells（RowCount，10 ）抓取货物重量（LBS）
arTemp（10）= arTemp（10）+ .Cells（RowCount，11）'抓取成本
'排序的潜在添加点并合并功能数组在数据被添加到列表之前（但是这样会为每个工作表的每个记录运行 - 似乎太低效）
arTemp（12）= arTemp（12）+ 1
列表（d ）= arTemp
下一个RowCount
调用QuickSort（arTemp，0，RowCount - 1）'Sort t他月度数组在月底（可以操纵数组，但列表已经加载...如何操纵/合并列表???）
结束
结束如果
下一步
我在总结中添加了一个月的列。 p>
子摘要（）
Dim ws As Worksheet
Dim iMonth As Integer，x As Long，x1 As Long
Dim Data，key
Dim list（1 To 12）As Object
对于x = 1至12
设置列表（x）= CreateObject（System Collections.SortedList）
下一个
对于每个ws In Worksheets
如果ws.Name<> 摘要然后
调用DeleteHidden删除活动工作表中的隐藏行/列如果有
与ws
对于x = 1到207
如果IsDate（。单元格（x，1））然后
iMonth = Month（.Cells（x，1））
key = .Cells（x，6）'Grab Del位置
如果列表（iMonth）.ContainsKey（key）Then
Data = list（iMonth）（key）
Else
ReDim数据（5）
数据（0）= iMonth
数据（1）= .Cells（x，6）'Grab Del位置
结束如果
数据（2）=数据（2）+ 1
数据（3） =数据（3）+ .Cells（x，9）'抓斗号码
数据（4）=数据（4）+ .Cells（x，10）'抓货重量（LBS）
数据（5）=数据（5）+ .Cells（x，11）'抓取成本
列表（iMonth）（key）= Data
End If
Next
结束与
结束如果
下一个
与工作表（摘要）
对于x = 1到12
对于x1 = 0要列出（x）.Count - 1
.Range（A& .Rows.Count）.End（xlUp）.Offset（1）.Resize（1，6）.Value = list（x）.GetByIndex（x1）
下一个
下一个
结束于
End Sub
I have an array which stores it's values in a sorted list. I have been using this sorted list to organise data, by date in several other spreadsheets.
My source data is a series of 12 worksheets in one workbook. Each worksheet reflecting a single calendar month. Number of transactions/runs is dynamic--averages 60 or so a month so I set a limit to my loop of 200 as this should be more than enough to cover any growth in business.
My current set of data is such that I have several repeat deliveries (different cargo/weights et al. but the same delivery location). I want to consolidate those "repeat"/similar rows into a single entry in the list, sum the number of pieces delivered, weight and delivery cost, and to increment a counter to show the number of repeated deliveries to that respective site.
Example: January, 2016
Delivered from: Delivered to: No. Pieces: Weight: Cost:
Site A Site B 10 100 $120.00
Site A Site C 5 20 $80.00
Site B Site C 2 30 $45.00
Site A Site C 20 460 $375.00
Summary:
Delivered to: No. of Deliveries: No. Pieces: Weight: Cost:
Site B 1 10 100 $120.00
Site C 3 27 510 $500.00
I can think of ways to do this by dumping data to a "scrap" worksheet, however, I want a VBA solution which is "internal" so that no such "scratch pad" is required.
The number of deliveries, in total, is dynamic. The number of repeat deliveries, for any given location, is also dynamic.
I am finding it very difficult to compose an efficient way to consolidate the information in my list with the above parameters as I am still very new to VBA/Excel.
Any suggestions are appreciated, in particular if you have example code--I know what I want, I'm just not sure how to implement it in VBA.
A sample of my array loading and transfer to the list is shown below (with variable definitions et al. omitted).
Set List = CreateObject("System.Collections.SortedList")
'Grab Monthly Data by Route
For Each ws In Worksheets
If ws.Name <> "Summary" Then
Call DeleteHidden 'Delete Hidden Rows/Columns in the active worksheet if any
With ws
'loop through the sheet to 207 (~3x greatest number of deliveries)
For RowCount = 7 To 207
'Check for dates for each row (Month/Day/Year)
d = DateValue(.Cells(RowCount, 1))
If List.Containskey(d) Then
arTemp = List(d)
Else
ReDim arTemp(12)
End If
'Monthly Totals
arTemp(0) = arTemp(0) + .Cells(RowCount, 1) 'Grab Entry Date/Time
arTemp(1) = arTemp(1) + .Cells(RowCount, 2) 'Grab Delivery Date/Time
arTemp(2) = arTemp(2) + .Cells(RowCount, 3) 'Grab PU Location
arTemp(3) = arTemp(3) + .Cells(RowCount, 4) 'Grab PU Street
arTemp(4) = arTemp(4) + .Cells(RowCount, 5) 'Grab PU City/Province/PC
arTemp(5) = arTemp(5) + .Cells(RowCount, 6) 'Grab Del Location
arTemp(6) = arTemp(6) + .Cells(RowCount, 7) 'Grab Del Street
arTemp(7) = arTemp(7) + .Cells(RowCount, 8) 'Grab Del City/Province/PC
arTemp(8) = arTemp(8) + .Cells(RowCount, 9) 'Grab No. Pieces
arTemp(9) = arTemp(9) + .Cells(RowCount, 10) 'Grab Cargo Weight (LBS)
arTemp(10) = arTemp(10) + .Cells(RowCount, 11) 'Grab Cost
'potential add point of a sort and consolidate function if working with the array prior to data being added to the list (but then such would run for each record of each worksheet---seems too inefficient)
arTemp(12) = arTemp(12) + 1
List(d) = arTemp
Next RowCount
Call QuickSort(arTemp, 0, RowCount - 1) 'Sort the Monthly Array at the end of the Month (can manipulate the array but the list is already loaded..how to manipulate/consolidate the list???)
End With
End If
Next
I added a month column in the summary.
Sub Summary()
Dim ws As Worksheet
Dim iMonth As Integer, x As Long, x1 As Long
Dim Data, key
Dim list(1 To 12) As Object
For x = 1 To 12
Set list(x) = CreateObject("System.Collections.SortedList")
Next
For Each ws In Worksheets
If ws.Name <> "Summary" Then
Call DeleteHidden 'Delete Hidden Rows/Columns in the active worksheet if any
With ws
For x = 1 To 207
If IsDate(.Cells(x, 1)) Then
iMonth = Month(.Cells(x, 1))
key = .Cells(x, 6) 'Grab Del Location
If list(iMonth).ContainsKey(key) Then
Data = list(iMonth)(key)
Else
ReDim Data(5)
Data(0) = iMonth
Data(1) = .Cells(x, 6) 'Grab Del Location
End If
Data(2) = Data(2) + 1
Data(3) = Data(3) + .Cells(x, 9) 'Grab No. Pieces
Data(4) = Data(4) + .Cells(x, 10) 'Grab Cargo Weight (LBS)
Data(5) = Data(5) + .Cells(x, 11) 'Grab Cost
list(iMonth)(key) = Data
End If
Next
End With
End If
Next
With Worksheets("Summary")
For x = 1 To 12
For x1 = 0 To list(x).Count - 1
.Range("A" & .Rows.Count).End(xlUp).Offset(1).Resize(1, 6).Value = list(x).GetByIndex(x1)
Next
Next
End With
End Sub
这篇关于如何在排序列表中合并类似的条目，而不使用VBA / Excel输出到工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
