Link to home
Start Free TrialLog in
Avatar of Indie101
Indie101

asked on

How best to represent data in Excel?

I have been given two columns, one is a server column and the other is a list of elevated accounts which have logged into that server

300 entries on both, just looking for advice on the best way to show it, I have tried a pivot table, I want to show how often the accounts are used overall in the best way possible

From what I can see there is a space between each account
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi,

It would be helpful, if you can provide sample file.
From what you say are all logins for each server in one cell separated by a space?
Avatar of Indie101
Indie101

ASKER

Yes the login accounts are represented

A
B
C
D
E
etc all in one cell
Do you want VBA to split these accounts to separate rows?
What about Server Column? How they are displayed? If possible can you send a screen shot?
Hi, here is a test file, in the version i have all the login accounts are aligned vertically (i didnt create original)

There are about 300 rows

I'm not too familar with VBA for this purpose just looking for best way to do it if possible
excel.JPG
This is just a screenshot.  Please attached excel which has few rows, not all 300
Sure here is the test.xlsx

I would same solution would work for real one, for real one there may be as many as 15 logins per server

Thanks
Test.xlsx
So you want to split only Server Column B into separate rows?
Yes and also have a display of logins counted against each server
OK try below:
Option Explicit
Sub BreakIntoRows()
Dim r As Range
Dim c As Range
Dim i As Integer
Dim lBottomRow As Long
Dim Ws As Worksheet
Dim vSplit
Dim LR As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set Ws = Sheets("Sheet1")
Set r = Ws.Range(Ws.Range("B1"), Ws.Range("B1").End(xlDown))
For Each c In r.Cells
    vSplit = Split(c, "\")
    If UBound(vSplit) > 0 Then
        For i = 0 To UBound(vSplit)
            If i = 0 Then
                c = vSplit(0)
            Else
            lBottomRow = c.End(xlDown).Offset(1).Row
            Ws.Cells(lBottomRow, c.Column) = vSplit(i)
            Ws.Cells(lBottomRow, c.Column).Offset(, -1) = c.Offset(, -1)
            End If
        Next
    End If
Next
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Sort.SortFields.Clear
Ws.Sort.SortFields.Add Key:=Range("A2:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("A1:B" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Ws.Range("C2:C" & LR).FormulaR1C1 = "=IF(RC1=R[-1]C1,"""",COUNTIF(C1,RC1))"
Ws.Columns.AutoFit
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks thats on the right track alright
You're Welcome! Glad I was able to help