基于重复值连接单元格值? [英] Concatenating cell value based on duplicate values?
问题描述
基本上,我的数据(项目编号及其相关维度)在两列中是这样的:
FOX6215A - 双尺寸W x D x H):38.6x 1.3x 59.8
FOX6215A - 全尺寸(宽x高x高):53.6x 1.3x 59.8
FOX6215A - 女王尺寸D x H):60.6x 1.3x 59.8
FOX6215A - King尺寸(宽x高x高):76.6x 1.3x 59.8
FOX6215B - 双尺寸(宽x深x高) H):38.6x 1.3x 59.8
FOX6215B - 全尺寸(宽x高x高):53.6x 1.3x 59.8
FOX6215B - 女王尺寸(宽x深x高) :60.6x 1.3x 59.8
FOX6215B - King尺寸(宽x高x高):76.6x 1.3x 59.8
FOX6215C - 双尺寸(宽x高x高):38.6 x 1.3x 59.8
FOX6215C - 全尺寸(宽x高x高):53.6x 1.3x 59.8
如果列A具有重复值,则需要连接列B中的维度(分隔每个大小的换行符)。所以想要的是:
FOX6215A - 双尺寸(宽x高x高):38.6x 1.3x 59.8
- 全尺寸(宽x高x高):53.6x 1.3x 59.8
- 女王尺寸(宽x高x高):60.6x 1.3x 59.8
...
因为 FOX6215A 有多种尺寸任何想法?
如果您有Office 365 Excel与TEXTJOIN():
获取项目编号的唯一列表,然后使用此数组公式:
= TEXTJOIN(CHAR(10),TRUE,IF($ A $ 1:$ A $ 10 = D1,$ B $ 1:$ B $ 10,))
是一个数组公式,需要在退出编辑模式时使用Ctrl-Shift-Enter而不是Enter进行确认,如果完成,Excel将会放入 记住在输出单元格上启用 如果您没有Office 365 Excel: 将此代码放在附加到工作簿的模块中,并使用上述公式: / p> Basically my data (item number and it's associated dimensions) is like this in two columns: I need to concatenate the dimensions in column B (with line breaks separating each size) if column A has duplicate values. So the desired out come would be: Because FOX6215A has multiple sizes. I'm completely stumped on how to do this. Any ideas? If you have Office 365 Excel with TEXTJOIN(): Get a unique list of the item numbers then use this array formula: being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put Remember to enable If you do not have Office 365 Excel: Put this code in a module attached to the workbook and use the formula as described above:
这篇关于基于重复值连接单元格值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! { $>
Wrap Text
并相应地调整大小。
函数TEXTJOIN(delim As String,skipblank As Boolean,arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long,y As Long
t = -1
y = -1
如果TypeName(arr)=RangeThen
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2,2)
y = UBound(arr2,1)
On Error GoTo 0
如果t> = 0而y> = 0则
对于c = LBound(arr2,1)To UBound(arr2,1)
对于d = LBound(arr2,1)到UBound(arr2,2 )
如果arr2(c,d)<> 还是不skipblank然后
TEXTJOIN = TEXTJOIN& arr2(c,d)& delim
结束如果
下一步d
下一步c
Else
对于c = LBound(arr2)到UBound(arr2)
如果arr2(c)< ;> 还是不skipblank然后
TEXTJOIN = TEXTJOIN& arr2(c)& delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN,Len(TEXTJOIN) - Len(delim))
结束函数
FOX6215A - Twin Dimensions (W x D x H): 38.6" x 1.3" x 59.8"
FOX6215A - Full Dimensions (W x D x H): 53.6" x 1.3" x 59.8"
FOX6215A - Queen Dimensions (W x D x H): 60.6" x 1.3" x 59.8"
FOX6215A - King Dimensions (W x D x H): 76.6" x 1.3" x 59.8"
FOX6215B - Twin Dimensions (W x D x H): 38.6" x 1.3" x 59.8"
FOX6215B - Full Dimensions (W x D x H): 53.6" x 1.3" x 59.8"
FOX6215B - Queen Dimensions (W x D x H): 60.6" x 1.3" x 59.8"
FOX6215B - King Dimensions (W x D x H): 76.6" x 1.3" x 59.8"
FOX6215C - Twin Dimensions (W x D x H): 38.6" x 1.3" x 59.8"
FOX6215C - Full Dimensions (W x D x H): 53.6" x 1.3" x 59.8"
FOX6215A - Twin Dimensions (W x D x H): 38.6" x 1.3" x 59.8"
- Full Dimensions (W x D x H): 53.6" x 1.3" x 59.8"
- Queen Dimensions (W x D x H): 60.6" x 1.3" x 59.8"
...
=TEXTJOIN(CHAR(10),TRUE,IF($A$1:$A$10=D1,$B$1:$B$10,""))
{}
around the formula.Wrap Text
on the output cells and size accordingly.
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0
If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function