GolfGTIforum.co.uk

General => General discussion => Topic started by: R32UK on 30 November 2012, 12:50

Title: Excel Help
Post by: R32UK on 30 November 2012, 12:50

Been a while since I used excel and need some help from an excel wiz if possible.

e.g. I have a list of random names in a list some of them repeated numerous times.

mat
mat
mat
john
alan
alan

I would like to turn this into a list of names without duplicates and the frequency each name appeared. So the list above would end up like

mat 3
john 1
alan 2

Many thanks!

Title: Re: Excel Help
Post by: logeyboy on 30 November 2012, 13:48

I have a blank spreadsheet with Macros embedded to do this, unfortunately I don't know how to view the macros, but I could e-mail you the sheet?

Title: Re: Excel Help
Post by: logeyboy on 30 November 2012, 13:50

Try this...

Sub RemoveDuplicates()

    ' Sort by column 1...
    Cells.Sort Key1:=Range("A1")
   
    ' Determine number of rows used in worksheet...
    totalrows = ActiveSheet.UsedRange.Rows.Count
   
    ' Initialize counter...
    Count = 1
   
    ' Step through the rows in reverse order with "Step -1".
    For Row = totalrows To 2 Step -1
        ' Check value in column 1 to see if it is the same
        ' as the previous row...
        If Cells(Row, 1).Value = Cells(Row - 1, 1).Value Then
            ' Found a dupe. Delete it and increment counter
            Rows(Row).Delete
            Count = Count + 1
        Else ' Not a duplicate. This is a new record, so...
            ' put the counter value in the spreadsheet for
            ' the previous record:
            Cells(Row, 3).Value = Count
            Count = 1 ' reset counter for new record
        End If
    Next Row
    ' Don't forget last record (which is actually first)...
    Cells(1, 3).Value = Count
   
End Sub

Title: Re: Excel Help
Post by: Ridg on 30 November 2012, 15:05

alternatively select data from the ribbon, select the cells, click group and then click subtotal which gives you a drill down for each item.

You might want to consider using access and not excel for this.

Title: Re: Excel Help
Post by: R32UK on 30 November 2012, 15:07

Thanks guys. Help very much appreciated!

You would think its much easier than this... and it is. Only I was wasnt using a full version of excel. With a full version downloaded I was able to do this very simply using a pivot table.

All sorted now  :smiley:

Thanks again  :cool:

Title: Re: Excel Help
Post by: Egbutt Wash on 30 November 2012, 18:31

Wack it each time with summat like this

Sub Auto_Open()
Application.OnSheetActivate = "Numpty"
End Sub

Sub Numpty()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
   ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub