Count and output letters in order

Will there always only be 1 set of each letter if it's there in each row?

In other words, would you ever have this: X X Y Z X
for example.

No they will always be in X,Y,Z order

Then a simple COUNTIF should do it:

In A2:
=COUNTIF($D2:$Z2,A$1)

Copy across and down.

No it should output X, Y, Z in the order not count them

I'm looking for D2, E2,F2,G2, D3,E3,F3,G3

I'm fairly new to VBA and I'm sure someone else has a better solution.

Not sure exactly what you are asking, but if you merely want to count the number of occurrences of x,y and z in the whole table then the following works: just paste your data in cell A1 on the sheet. Instead of the message box you can put the results back in the sheet.

Private Sub CommandButton1_Click()
Dim x As Integer, y As Integer, z As Integer


x = 0
y = 0
z = 0


For Each cell In Range("A1:G3")
If cell = "x" Or cell = "X" Then
x = x + 1
ElseIf cell = "y" Or cell = "Y" Then
y = y + 1
ElseIf cell = "z" Or cell = "Z" Then
z = z + 1
Else
End If
Next cell
MsgBox ("x = " & x & vbNewLine & "y = " & y & vbNewLine & "z = " & z)



End Sub

bestnagi,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


How about a macro solution?

The following macro will adjust for a varying number of rows, and, columns.

Sample raw data:




After the macro:




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the OutputLetters macro.

If you only have 3 inputs, try this formula copied across and down.

That works prefect!