Get Unique Value list in excel

问题:

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

回答1:

Now if you don't mind them being in different cells then:

=IFERROR(INDEX($C$2:$C$6,MATCH(1,IF(($B$2:$B$6=$F1)*(COUNTIF($F1:F1,$C$2:$C$6)=0),1,0),0)),"")

This is an array formula and as such must be confirmed with Ctrl-Shift-Enter instead of Enter or Tab to exit edit mode.

回答2:

This can be done in an extra step. As in you will need an intermediate table.

So in my example, I take the data you have given (which seems sorted for Column car), but assume that it is not sorted (unsorted data in the screenshot).

Step 1:

Unsorted data is sorted, and table formed in rows 9-14. Add a column in D9, and in cell D10 put this formula:

=IF(B10=B9,CONCATENATE(VLOOKUP(B9,B9:D10,3,0),", ",C10),C10)

What this formula does is it looks up and concatenates values at the same time.

I find no of unique entries in the table in rows 9 and 14 with a formula in row 16. (This is just for convenience)

B16 =SUMPRODUCT(1/COUNTIF($B$10:$B$14,$B$10:$B$14))

Step 2:

In rows 18-20 (the no of unique entries kind of gave an idea how big this table will be),

B19=INDEX($B$10:$B$14,MATCH(0,INDEX(COUNTIF($B$18:B18,$B$10:$B$14),0,0),0))

This formula lists out unique car names in colB. In colC, you LOOKUP the last concatenated value for a car name (because we had kept on concatenating earlier, the last entry for a car name would have your result)

C19=LOOKUP(2,1/($B$9:$B$14=B19),$D$9:$D$14)

That should give you the resultant table! (Have pasted all of them as values in colG onwards so that you see the output)

回答3:

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