Feb 01 2023 03:47 PM
Hi!
We have a spreadsheet that is quite a bit of a disaster. It had to be built by someone who didn't know how to build a spreadsheet. There are 3 main titles that exist in cells A1-D1, E1-H1, and I1-M1. The 1st main title has 4 subtitles, as does the 2nd main title. The 3rd main title has 5 subtitles. Each column represents a different stage in the process. Each cell contains 6 labels and their corresponding information: Acct #, Business, Notes, Address, Name, and Request. Below is an example of what the cells look like:
Acct #: 12345 Business: ABC Company Notes: Generic note goes here. Address: 123 N East Street Name: Gold/Sterling/Silver Request: Employees start tomorrow. |
We can't use a COUNTIF formula because there are some crazy rules attached to this program. Please see below:
Here is an example of names:
In the above example, Gold would be counted 6 times--5 times for 1 time for when Gold is bold.
Does anyone have any idea how we can even go about making this better for our employees? I am working on converting the spreadsheet over to a new format, but I would appreciate any assistance we can get for this particular situation. Thank you in advance! Have a fantastic day!
Bray
Feb 02 2023 04:06 AM
Here is a custom VBA function.
Function CountName(MyRange As Range, MyName As String) As Long
Dim MyCell As Range
Dim MyLines() As String
Dim MyLine As String
Dim MyStart As Long
Dim i As Long
Dim f As Boolean
For Each MyCell In MyRange
MyLines = Split(MyCell.Value, vbLf)
If UBound(MyLines) >= 4 Then
MyLine = MyLines(4)
If MyLine = MyName Then
CountName = CountName + 1
Else
MyStart = 0
For i = 0 To 3
MyStart = MyStart + Len(MyLines(i)) + 1
Next i
MyStart = MyStart + InStr(1, MyLine, MyName, vbTextCompare)
f = True
For i = 0 To Len(MyName) - 1
If Not MyCell.Characters(MyStart + i, 1).Font.Bold Then
f = False
Exit For
End If
Next i
If f Then
CountName = CountName + 1
End If
End If
End If
Next MyCell
End Function
Use like this:
=CountName(A3:M3, "Gold")
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.