|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Ignore my last post as it worked perfectly. I am now doing a bit of research on the code. I don't want to just take your code and run it - I would like to understand how it works. Thank you for providing this for me and starting me on the macro path
|
#8
|
|||
|
|||
Good luck and make sure if you need anything for excel to come back here.
|
#9
|
|||
|
|||
Cathy
The following line Code:
Cells(j, 4).Formula = "=COUNTIF(RC[-2]:R[" & lr & "]C[-2],RC[-1] & ""*"")" '<----- Column D has formula Code:
Cells(j, 4).Formula = "=COUNTIF(R1C[-2]:R[" & lr & "]C[-2],RC[-1] & ""*"")" '<----- Column D has formula The 1 (one) needs to be there. Good luck |
#10
|
|||
|
|||
Here is another possibility without the need for a Help Column.
In this code it assumes that Column C (3) is free. Change as required. Code:
Sub One_More_Way() Dim j As Long, lr As Long lr = Cells(Rows.Count, 1).End(xlUp).Row '<---- Column A (1) is being checked. Change as required For j = 1 To 26 'The number 3 in Cells(j, 3) in the next line is Column C (3rd Column).Change as required. Cells(j, 3).Value = Application.WorksheetFunction.CountIf(Range("B1:B" & lr), _ Chr(64 + j) & "*") & " x " & Chr(64 + j) & "'s" Next j End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Right Click menu list names | jimbassett | Word | 0 | 02-25-2013 02:39 PM |
How to import a list of names into PowerPoint from Excel? | CarpetRemnant | PowerPoint | 4 | 12-07-2012 11:08 AM |
auto insert names from list for printing | andreipopa2k | Word | 1 | 12-09-2011 01:51 PM |
Using a list of names in credits effect | Pemberton | PowerPoint | 4 | 08-17-2010 02:10 AM |
Random names from a given list | professor snape | Excel | 1 | 06-06-2009 09:39 AM |