Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-31-2014, 07:08 AM
cadonn cadonn is offline Have an alphbetical list need to count how many names start with A, B, C etc. Windows XP Have an alphbetical list need to count how many names start with A, B, C etc. Office 2007
Novice
Have an alphbetical list need to count how many names start with A, B, C etc.
 
Join Date: Jan 2013
Posts: 6
cadonn is on a distinguished road
Default Have an alphbetical list need to count how many names start with A, B, C etc.


Have an alphbetical list need to count how many names start with A, B, C etc. I know I can use the =COUNTIF(A1:A50, "*A") but I do not want to have to do this for each letter of the alphabet. Is there another option. I am using Excel 2010. Thank you.

Cat
Reply With Quote
  #2  
Old 08-31-2014, 10:57 PM
jolivanes jolivanes is offline Have an alphbetical list need to count how many names start with A, B, C etc. Windows XP Have an alphbetical list need to count how many names start with A, B, C etc. Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Posts: 91
jolivanes will become famous soon enough
Default

Would this work?
Names are in Column B and Columns C and D are free to use.
Change references as required.
Code:
Sub Try_This()
    Dim lr As Long, j As Long
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, 2).End(xlUp).Row    '<----- Column B with names
    For j = 1 To 26
        Cells(j, 3).Value = Chr(64 + j)    '<----- Column C is temporary used
        Cells(j, 4).Formula = "=COUNTIF(RC[-2]:R[" & lr & "]C[-2],RC[-1] & ""*"")"    '<----- Column D has formula
        Cells(j, 4).Value = Cells(j, 4).Value
    Next j
    'Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row).ClearContents
    Application.ScreenUpdating = True
End Sub
Reply With Quote
  #3  
Old 08-31-2014, 11:58 PM
jolivanes jolivanes is offline Have an alphbetical list need to count how many names start with A, B, C etc. Windows XP Have an alphbetical list need to count how many names start with A, B, C etc. Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Posts: 91
jolivanes will become famous soon enough
Default

Or this. Same goes for the Columns as in the previous post.
For some reason, the previous post misses one word with a C when testing and I have not been able to find out why it does that.


Code:
Sub Another_Way()
    Dim j As Long, Counter As Long, c As Range
    Application.ScreenUpdating = False
    For j = 1 To 26
        Counter = 0
        Cells(j, 3).Value = Chr(64 + j)
        For Each c In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
            If Left(c, 1) = Cells(j, 3).Value Then Counter = Counter + 1
        Next c
        Cells(j, 5) = Counter
    Next j
    Application.ScreenUpdating = True
End Sub
Reply With Quote
  #4  
Old 09-01-2014, 10:24 AM
cadonn cadonn is offline Have an alphbetical list need to count how many names start with A, B, C etc. Windows XP Have an alphbetical list need to count how many names start with A, B, C etc. Office 2007
Novice
Have an alphbetical list need to count how many names start with A, B, C etc.
 
Join Date: Jan 2013
Posts: 6
cadonn is on a distinguished road
Default

Hi. Since I am a newbie at formulas and code I am not sure what to do with the information you have provided is this a macro. I certainly appreciate the effort.

Cat
Reply With Quote
  #5  
Old 09-01-2014, 11:39 AM
jolivanes jolivanes is offline Have an alphbetical list need to count how many names start with A, B, C etc. Windows XP Have an alphbetical list need to count how many names start with A, B, C etc. Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Posts: 91
jolivanes will become famous soon enough
Default

See if you can make any sense out of the attached workbook.
Save it and open it (allow macros to be used).
If something is not clear, come back and ask.
Good luck.
Attached Files
File Type: xlsm cadonn.xlsm (20.4 KB, 17 views)
Reply With Quote
  #6  
Old 09-01-2014, 12:03 PM
cadonn cadonn is offline Have an alphbetical list need to count how many names start with A, B, C etc. Windows XP Have an alphbetical list need to count how many names start with A, B, C etc. Office 2007
Novice
Have an alphbetical list need to count how many names start with A, B, C etc.
 
Join Date: Jan 2013
Posts: 6
cadonn is on a distinguished road
Default

That works great - when you say copy the macro do you mean from your post above. I can't believe how more of an explanation you gave me - awesome and thank you.

Cathy
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Right Click menu list names jimbassett Word 0 02-25-2013 02:39 PM
Have an alphbetical list need to count how many names start with A, B, C etc. How to import a list of names into PowerPoint from Excel? CarpetRemnant PowerPoint 4 12-07-2012 11:08 AM
Have an alphbetical list need to count how many names start with A, B, C etc. auto insert names from list for printing andreipopa2k Word 1 12-09-2011 01:51 PM
Have an alphbetical list need to count how many names start with A, B, C etc. Using a list of names in credits effect Pemberton PowerPoint 4 08-17-2010 02:10 AM
Have an alphbetical list need to count how many names start with A, B, C etc. Random names from a given list professor snape Excel 1 06-06-2009 09:39 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft