Removing Repeating Rows in Excel

I have a spreadsheet that has a number of repeating rows which I would like to reformat (see attached spreadsheet).  I would like it to look like this:

Abeera Halim            1
Adrian Kelly               7
Adrianne Gilbert        7
Edward Safo               1
.
.
.
 In other words, without all the repeating rows, i.e., just the first column (without the word "Count") and the totals. Short of doing it manually, is there an easy way to obtain it in the format that I need, for example by using a formula, macro, or some other means? The spreadsheet is over 2000 rows long, I just posted a abbreviated version of it.
Student-Names-and-Totals.xlsx
geeta_m9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ButlerTechnologyCommented:
The following VBA code should handle the removing of the "repeating rows" and preserve the count.
Public Sub StudentCount()
Dim RowCount As Integer

RowCount = 0
Range("A1").Activate

' Find Row Count
  Do
    RowCount = RowCount + 1
    ActiveCell.Offset(1, 0).Activate
  Loop Until ActiveCell.Value = "" And ActiveCell.Offset(0, 1).Value = ""

Range("A1").Activate
  
' Copy/Paste Values -- removes dependancy on formula
  Range("A1:B" & RowCount).Copy
  Range("A1:B" & RowCount).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  
Range("A1").Select
Range("B1").Activate

' Remove Rows that don't have a number
  Do
    If IsNumeric(ActiveCell) Then
      ActiveCell.Offset(1, 0).Activate
    Else
      ActiveCell.EntireRow.Delete
    End If
  Loop Until ActiveCell.Value = ""
  
Range("A1").Activate
End Sub

Open in new window


Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geeta_m9Author Commented:
I haven't used VBA in Excel. Where should I put the code?
0
ButlerTechnologyCommented:
You can put in in the active workbook or your personal macro-work book.  In Excel, you can push Alt-F11, this will open the VBA Window.  If you the Personal.XLSB in the project window, I would recommend right clicking and selecting inset |module.  If you don't see the Personal.xlsb, the right click on the current workbook and select insert | Module.  Copy the code.  I would recommend test on a copy.  Make sure the worksheet is active and go back to the VBA screen and push F5.  This will run the macro.

Tom
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

geeta_m9Author Commented:
Never mind, I figured it out.

Thanks. The code works perfectly!
0
geeta_m9Author Commented:
Tom:

I have another spreadsheet that that is similar to the one I attached, except this does not have the first column that has the name of the student and the word, "Count". How can I adjust the code to give me the same results?

Thanks.
Names-of-students-and-number-of-.xlsx
0
ButlerTechnologyCommented:
Are you looking to have the Name and number on the same row and remove the duplicates?
Would it be okay to have two columns for the final output?

Like this--
Student Name    Count
Thomas                 10
Tracy                      15
Tony                       11

Tom
0
geeta_m9Author Commented:
Yes, that's correct.
0
ButlerTechnologyCommented:
Public Sub studentCount2()

Range("A2").Activate

Do
  If IsNumeric(ActiveCell.Offset(1, 0).Value) Then
    ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 0).Value
    ActiveCell.Offset(1, 0).EntireRow.Delete
    ActiveCell.Offset(1, 0).Activate
  Else
    ActiveCell.EntireRow.Delete
  End If
Loop Until IsEmpty(ActiveCell)
End Sub

Open in new window


This should handle the second sheet.

Tom
0
geeta_m9Author Commented:
I tried it just now and it does not appear to be working correctly. I am obtaining each name on a separate row with a count of 1. For example:


Aaron Kurland                     1
Abigail Coles                     1
Abigail Green                     1
Adam Oppenheimer       1
Addie Mcelwee             1
Aditya Sapasetty             1
0
ButlerTechnologyCommented:
I see the issue -- I did not take into consideration that the number calculated fields.  I add the code that will change the calculated fields to their actual values.

Tom

Public Sub studentCount2()

Columns("A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select

Do
  If IsNumeric(ActiveCell.Offset(1, 0).Value) Then
    ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 0).Value
    ActiveCell.Offset(1, 0).EntireRow.Delete
    ActiveCell.Offset(1, 0).Activate
  Else
    ActiveCell.EntireRow.Delete
  End If
Loop Until IsEmpty(ActiveCell)
End Sub

Open in new window

0
geeta_m9Author Commented:
It works great now. Thank you for putting so much time and effort into it. I really appreciate it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.