根据 A 列的通用值合并 B 列的值 [英] Merge values of column B based on common values on column A
本文介绍了根据 A 列的通用值合并 B 列的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在 Excel 中,如何根据 Column A
上的通用值合并 Column B
的值?
In Excel, how I can merge values of Column B
based on common values on Column A
?
基本上我需要的是这样的东西
Basically what I need is some thing like this
推荐答案
您可以使用此 UDF:
You can use this UDF:
Function TEXTJOINIFS(rng As Range, delim As String, ParamArray arr() As Variant)
Dim rngarr As Variant
rngarr = Intersect(rng, rng.Parent.UsedRange).Value
Dim condArr() As Boolean
ReDim condArr(1 To Intersect(rng, rng.Parent.UsedRange).Rows.Count) As Boolean
Dim i As Long
For i = LBound(arr) To UBound(arr) Step 2
Dim colArr() As Variant
colArr = Intersect(arr(i), arr(i).Parent.UsedRange).Value
Dim j As Long
For j = LBound(colArr, 1) To UBound(colArr, 1)
If Not condArr(j) Then
Dim charind As Long
charind = Application.Max(InStr(arr(i + 1), ">"), InStr(arr(i + 1), "<"), InStr(arr(i + 1), "="))
Dim opprnd As String
If charind = 0 Then
opprnd = "="
Else
opprnd = Left(arr(i + 1), charind)
End If
Dim t As String
t = """" & colArr(j, 1) & """" & opprnd & """" & Mid(arr(i + 1), charind + 1) & """"
If Not Application.Evaluate(t) Then condArr(j) = True
End If
Next j
Next i
For i = LBound(rngarr, 1) To UBound(rngarr, 1)
If Not condArr(i) Then
TEXTJOINIFS = TEXTJOINIFS & rngarr(i, 1) & delim
End If
Next i
TEXTJOINIFS = Left(TEXTJOINIFS, Len(TEXTJOINIFS) - Len(delim))
End Function
你可以这样称呼它:
=IF(MATCH(A1,A:A,0)=ROW(A1),TEXTJOINIFS(B:B,", ",A:A,A1),"")
现在不管数据是否排序,它只会将输出放在列 A 中的值第一次出现的列 C 中.
Now it does not matter if the data is sorted or not it will only put the output in column C where the value in Column A first appears.
这篇关于根据 A 列的通用值合并 B 列的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文