在excel中获取唯一值列表 [英] Get Unique Value list in excel

问题描述

我是 Excel 公式的新手，所以有人可以帮助我

I am quite novice at Excel formulas, so could anyone help me

我有如下元素

+----+---------+------------+
| 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 |
+---+---------+-----------------+

每个值都是唯一的.我试过 Vlookup，但它只返回 1 个具有 1 个值的元素.示例:使用 Ford 进行 VLookup 将仅返回第一个结果 Mustang，而不返回 Focus

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

推荐答案

将其放入 D2

=IF(B2<>B1,C2,IF(C2<>C1,IF(D1="",C2,D1&", "&C2),""))

下拉

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.

如果您可以删除 A 列并使用 Excel 的删除重复功能，这会容易得多.

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

结果:

Car         Model
Ford        Mustang, Focus
Ferrari     458, Testarossa

这篇关于在excel中获取唯一值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！