CODEWITHSUNDEEP
excelvba
Nikhil
Nikhil

Reputation: 753

Put unique distinct values into a list from multiple columns of an array

I'm new to Excel and the journey has been good so far, but I haven't been able to resolve this particular issue by myself. I'm dealing with a table as under:

enter image description here

Essentially, I'm looking to refer to the array of tags in columns from B3:E6, and do the following:

Create a "Unique Tags" column: Create a unique list of "tags" in column H by removing duplicates.

Create a "Maximum Marks" column: Look for each of the unique tags in the array in each row, and return the marks from the marks column in the same row. If the tag appears in multiple rows. the sum of the corresponding marks in these multiple rows should be returned in the maximum marks column in column I. For example, 'EASY' appears in E3 as well as E5. Thus in the 'Unique Tags' List 'EASY' should correspond to Maximum Marks = 4 (2+2).

I could do this manually using formulas such as SUMIF, but I'm looking for a way to automate it since I might have to do this operation for a similar dataset with additional rows & columns. I'm open to VBA solutions as well but would prefer some sort of formula.

I hope I've explained it well enough! Thanks and looking forward to your inputs.

Upvotes: 0

Views: 4420

Answers (1)

Wild138
Wild138

Reputation: 573

One way to do this is create a function that returns the array of your unique cells and then multiplies them all by matches in your Marks column.

Create the unique cells with this array function. Note this function uses the Dictionary object. In the VB Editor, go to Tools > References, and make sure Microsoft Scripting Runtime is selected.

Public Function UniqueValues(aRange As Range)
Dim DictValues As New Dictionary
Dim cll As Variant
Dim aryResults() As String
For Each cll In aRange
    If Not DictValues.Exists(cll.Value) Then DictValues.Add cll.Value, "":
Next

UniqueValues = DictValues.Keys
Set DictValues = Nothing
End Function

Enter in cell H3 and press CTRL SHIFT RETURN (as it's an array function)

=TRANSPOSE(uniquevalues(B3:E6))

and drag down to H15 or beyond

We have to use TRANSPOSE as the array comes out in a row from the function.

Next we need to find the matching cells and multiply. Here in C15 enter the formula below 

=INDEX(SUM((($B$3:$E$6=H3)*1)*$F$3:$F$6),1)

Drag this down to H15.

Upvotes: 1

Related Questions