将多个具有相同值的行组合成一个加上更多...在Excel中 [英] Combine multiple rows with same value into one plus more... in Excel
问题描述
隐藏这个:
订单产品
158 866
161 960
163 976
163 884
164 1010
173 834
174 981
177 935
177 832
177 934
这个:
订单产品
158 866
161 960
163 976,884
164 1010
173 834
174 981
177 935,832,934
@Nick提供的链接为您想要的地方提供了一个非常好的非VBA解决方案保持原始数据不变。
如果要修改数据仅留下合并的行,则需要VBA解决方案。
这是一个将合并的宏。
注意:
-
假设没有其他有用的数据在纸上
-
它在活动表单上工作
-
它在内存变体数组中合并以提供谐振速度。循环单元格/行将产生更简单的代码,但会运行得更慢
。
Sub MergeRows()
Dim rng As Range
Dim vSrc As Variant
Dim vDst()As Variant
Dim i As Long,j As Long
'假设数据从单元格A2开始,向下延伸,没有空单元格
设置rng =范围([A2],[A2] .End (xlDown))
'计算列A中的唯一值
j = Application.Evaluate(SUM(IF(FREQUENCY(_
& rng.Address& & rng.Address&)> 0,1)))
ReDim vDst(1 To j,1 To 2)
j = 1
'获取原始数据到数组
vSrc = rng.Resize(,2)
'创建新数组,列A中每个唯一值的一行
vDst(1,1) = vSrc(1,1)
vDst(1,2)='& vSrc(1,2)
对于i = 2 To UBound(vSrc,1)
如果vSrc(i - 1,1)= vSrc(i,1)然后
vDst(j, 2)= vDst(j,2)& ,& vSrc(i,2)
Else
j = j + 1
vDst(j,1)= vSrc(i,1)
vDst(j,2)=' &安培; vSrc(i,2)
结束如果
下一个
'删除旧数据
rng.EntireRow.Delete
'将新数据放在
中设置rng = [A2] .Resize(j,2)
rng = vDst
End Sub
I have a list of product ids and order numbers. Each order number can have multiple product ids(since a person can buy more than one item). My goal is to combine each order number and product ids associated with it into one row. If there are more than one product id for each order then separate them my comma. Please see illustration below. I would like to avoid using manual approach as I have around 13000 rows. Please advise. Thanks.
Covert this: order product 158 866 161 960 163 976 163 884 164 1010 173 834 174 981 177 935 177 832 177 934 to this: order product 158 866 161 960 163 976,884 164 1010 173 834 174 981 177 935,832,934
The link given by @Nick provides a good non-VBA solution for where you want to keep the original data intact.
If you want to modify the data to leave only the merged rows, then a VBA solution will be required.
Here's a macro that will do the merging.
NOTES:
It assumes there is no other useful data on the sheet. If there is it will be deleted and or overwritten.
It works on the active sheet
It does the merging in memory variant arrays to provide resonable speed. Looping over the cells/rows would produce simpler code, but would run much slower
.
Sub MergeRows()
Dim rng As Range
Dim vSrc As Variant
Dim vDst() As Variant
Dim i As Long, j As Long
' Assumes data starts at cell A2 and extends down with no empty cells
Set rng = Range([A2], [A2].End(xlDown))
' Count unique values in column A
j = Application.Evaluate("SUM(IF(FREQUENCY(" _
& rng.Address & "," & rng.Address & ")>0,1))")
ReDim vDst(1 To j, 1 To 2)
j = 1
' Get original data into an array
vSrc = rng.Resize(, 2)
' Create new array, one row for each unique value in column A
vDst(1, 1) = vSrc(1, 1)
vDst(1, 2) = "'" & vSrc(1, 2)
For i = 2 To UBound(vSrc, 1)
If vSrc(i - 1, 1) = vSrc(i, 1) Then
vDst(j, 2) = vDst(j, 2) & "," & vSrc(i, 2)
Else
j = j + 1
vDst(j, 1) = vSrc(i, 1)
vDst(j, 2) = "'" & vSrc(i, 2)
End If
Next
' Remove old data
rng.EntireRow.Delete
' Put new data in sheet
Set rng = [A2].Resize(j, 2)
rng = vDst
End Sub
这篇关于将多个具有相同值的行组合成一个加上更多...在Excel中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!