获取excel中的唯一值列表 [英] Get Unique Value list in excel
问题描述
我有以下元素
+ ---- + --------- + ------------ +
| A | B | C |
| nr |车|模型|
| 1 |福特|野马|
| 2 |福特|焦点|
| 3 |福特|焦点|
| 4 |法拉利| 458 |
| 5 |法拉利| Testarossa |
+ ---- + --------- + ------------ +
如何获得以下结果
+ --- + -------- + ----------------- +
| 1 |福特|野马,焦点|
| 2 |法拉利| 458,Testarossa |
+ --- + --------- + ----------------- +
每个值都是唯一的。我试过Vlookup,但它只返回1个元素与1值。示例:与福特的VLookup将仅返回第一个结果Mustang,但不会聚焦
如果可能,请只使用公式:)
我看过类似的问题但没有答案
将其放在D2
($ B1,C2,IF(C2-C1,IF(D1 =,C2,D1,C2)))
向下拖动
nr车型ColD
1福特野马野马
2福特福克斯野马,焦点
3福特福克斯
4法拉利458 458
5法拉利Testarossa 458,Testarossa
每辆车的最后一个模型的首次出现都具有您想要的数据。
如果您可以删除列A并使用Excel的删除重复功能,这将会更容易。
如果要使用代码,这将工作:
Sub ConcatByMasterColumn()
Dim X As Long,M yString As String
范围(F1:G1)。Formula = Array(Car,Model)
对于X = 2 To Range(A& Rows.Count).End(xlUp).Row + 1
如果Range(B& X)范围(B& X-1)而X> 2 Then'MyString = Range(B& X).Text
Range(F& Range(F& Rows.Count).End(xlUp).Offset(1,0) .Row).Formula = Range(B& X-1).Text
Range(G& Range(G& Rows.Count).End(xlUp).Offset(1 ,0).Row).Formula = Right(MyString,Len(MyString) - 2)
MyString =
End If
如果Range(C& X) >范围(C& X - 1)然后MyString = MyString& ,&范围(C& X).Text
下一个
End Sub
结果:
汽车模型
福特野马,焦点
法拉利458,Testarossa
I am quite novice at Excel formulas, so could anyone help me
I have elements as follows
+----+---------+------------+
| A | B | C |
| nr | car | model |
| 1 | Ford | Mustang |
| 2 | Ford | Focus |
| 3 | Ford | Focus |
| 4 | Ferrari | 458 |
| 5 | Ferrari | Testarossa |
+----+---------+------------+
How could I get the results as follows
+---+---------+-----------------+
| 1 | Ford | Mustang, Focus |
| 2 | Ferrari | 458, Testarossa |
+---+---------+-----------------+
Where every value is unique. I Tried Vlookup, but it only returns 1 element with 1 value. Example: VLookup with Ford would return only the first result Mustang, but not Focus
If possible, please use only Formulas :) I've seen similar questions but no answers
Put this in D2
=IF(B2<>B1,C2,IF(C2<>C1,IF(D1="",C2,D1&", "&C2),""))
Drag down
nr car model ColD
1 Ford Mustang Mustang
2 Ford Focus Mustang, Focus
3 Ford Focus
4 Ferrari 458 458
5 Ferrari Testarossa 458, Testarossa
first occurrence of the last model per car has the data you want.
This would be a lot easier if you could kill column A and use Excel's remove dupes function.
If you want to do it with code, this will work:
Sub ConcatByMasterColumn()
Dim X As Long, MyString As String
Range("F1:G1").Formula = Array("Car", "Model")
For X = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
If Range("B" & X) <> Range("B" & X - 1) And X > 2 Then 'MyString = Range("B" & X).Text
Range("F" & Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Row).Formula = Range("B" & X - 1).Text
Range("G" & Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Row).Formula = Right(MyString, Len(MyString) - 2)
MyString = ""
End If
If Range("C" & X) <> Range("C" & X - 1) Then MyString = MyString & ", " & Range("C" & X).Text
Next
End Sub
Results:
Car Model
Ford Mustang, Focus
Ferrari 458, Testarossa
这篇关于获取excel中的唯一值列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!