Counting unique text values and making dynamic dashboard in VBA

Question

I have a reference file for a dashboard I am making. I want to be able to count the unique org names in a reference file column and make that number the amount of column headers in my separate dashboard. I used macro recorder to make it unique, but I am unsure of how to translate this into making a dynamic number of columns for my dashboard based on the count of unique org names in the ref file. Here's a picture attachment example of what the reference file could look like. So, if it counts that there are 5 unique names, I would like the separate dashboard to make 5 columns with those names as headers in each column.

  Sub Macro1()


 '     Macro1 Macro

Columns("F:F").Select
Range("F1:F10000000000").AdvancedFilter Action:=xlFilterCopy,  CopyToRange:=Columns _
    ("O:O"), Unique:=True

ActiveCell.FormulaR1C1 = "=ROWS(R[-11]C:R[-2]C)"

 End Sub

score 1 · Accepted Answer · answered Jul 12 '16 at 16:03

1

Here's a way to do it by loading the unique results into an array. This assumes the column headers go into A1.

Sub Macro1()

Dim wsRef As Worksheet
Dim wsDB As Worksheet

Set wsRef = Worksheets("reference")
Set wsDB = Worksheets("Dashboard")

With wsRef
    .Range("C1:C9").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("F1"), Unique:=True

    Dim arrValues As Variant
    arrValues = .Range("F2", .Range("F" & .Rows.Count).End(xlUp))

End With

With wsDB
    .Range(.Range("A1"), .Cells(1, UBound(arrValues))).Value = Application.WorksheetFunction.Transpose(arrValues)
End With

End Sub

answered Jul 12 '16 at 16:03

Scott Holtzman

27,099
5
37
72

this works! the column headers will start at row F and loop every other row..any way to do that instead of every row at A1? – beks123 Jul 12 '16 at 18:08
change `A` to `F` and add a counter to step 2 for the row number in the cell reference. – Scott Holtzman Jul 12 '16 at 18:10
for step 2 do you add a for loop? – beks123 Jul 12 '16 at 18:16
@beks123 - probably, but its hard to say without seeing you construct. research it. if you get stuck, post another question and someone will help you figure it out – Scott Holtzman Jul 12 '16 at 18:20

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

Use a Dictionary object to get your unique values, then loop through the dictionary items to make your column headers. eg,

myCol = 1
For Each item in oDic.items
    'Presuming you want your headers to start at A1
    Cells(1, myCol).Value = item
    myCol = myCol + 1
Next

edited May 23 '17 at 11:44

Community

1
1

answered Jul 12 '16 at 15:54

Tim

2,701
3
26
47

Counting unique text values and making dynamic dashboard in VBA

2 Answers2