How to count merged cells containing text

[TimetablingABC]

Hi,

I need to count cells both merged and unmerged (mainly merged vertically in columns) on a text string in the cell (text string may be long but I just need to search for a name out of the text). The cells are also conditionally formatted different colours, don’t know if this caused a problem? Does anyone know if this is possible please?

Also, the workbook is a timetable and each cell represents 15 minute slots (6 merged cells represent 1hour 30 mins) so I will have to do some math after that, which I’m presuming will be easier separately using the count to work out how much time a name has allocated, 15mins x count result?

Please help, my brain has gone to spaghetti trying to figure out how to count merged cells, I’m abit of a novice!?

PS. The worksheet is inherited from someone else so I am not responsible for the extensive use of merged cells ?

 

[jasonb75]

Sometimes when you inherit someone else's disaster, it is less time consuming to start again that to try and fix what you've been given to work with.

Staring off with the simplest method then it can be expanded if needed. Assuming that your merged cells are in column A, you could use something like

=COUNTIF(A:A,"*cat*")

to count the cells that contain the word "cat" anywhere in the string. Note that this will not differentiate between the word 'cat' or longer words that contain 'cat'.

When you count merged cells (or use the content of a merged cell as a reference to something else) it is important to remember than it only counts as 1, regardless of how many rows / columns are merged. The content defaults to the top left cell of the merged selection, with all of the other cells being empty (this default can not be changed).

 

[TimetablingABC]

Sometimes when you inherit someone else's disaster, it is less time consuming to start again that to try and fix what you've been given to work with.

Staring off with the simplest method then it can be expanded if needed. Assuming that your merged cells are in column A, you could use something like

=COUNTIF(A:A,"*cat*")

to count the cells that contain the word "cat" anywhere in the string. Note that this will not differentiate between the word 'cat' or longer words that contain 'cat'.

When you count merged cells (or use the content of a merged cell as a reference to something else) it is important to remember than it only counts as 1, regardless of how many rows / columns are merged. The content defaults to the top left cell of the merged selection, with all of the other cells being empty (this default can not be changed).

Hi, thanks for your reply. Unfortunately I need a way to count the merged cells as their multiple of unmerged (eg. If 6 unmerged cells were merged into 1 I need a return count of 6, as they represent 6 occurrences of a time slot I need to add up, but were merged so visually a lot of info could go into that block).

When I have more time I will look at an alternative to using merged cells, but for now that’s not practical time wise for me. I may just have to resort to counting them manually? I’ve looked into VBA but am struggling as I’m somewhat of a novice n it’s not working.

Thank you for your suggestion ??

 

[Tupe77]

What strings are calculated from the cells?
I mean: How do you know what text you are looking for?

 

[jasonb75]

eg. If 6 unmerged cells were merged into 1 I need a return count of 6, as they represent 6 occurrences of a time slot I need to add up

There is no formula that would do that, the only way it might be possible is by comparison to adjacent data if there is any way to identify the end of the merged cells from that.

With vba it would be easier, unlike a formula, vba does appear to count each cell that is merged rather than just the first one. See if this udf does as needed.
Enter the code into the editor, then use the formula like =countmerge(A2:A20,"cat") in your sheet as normal (no wildcard characters with this one).

Public Function countmerge(rng As Range, criteria As Variant) As Long
Dim tmp As Long, c As Range
For Each c In rng
    If InStr(criteria, c.Value) Then tmp = tmp + 1
Next
countmerge = tmp
End Function

 

[Tupe77]

How about this?

'Can be used directly from workbook: =CountCells(Range:Range,"String")
Public Function CountCells(SrcRNG As Range, SrcSTR As String) As Long ' Count ALL cells that contain String(SrcSTR) , including All cells that is Merged together
Dim Cell As Range, Tmp As Long
SrcSTR = "*" & SrcSTR & "*" ' Add * for search string
    For Each Cell In SrcRNG
        If Cell.MergeArea(1).Value Like SrcSTR Then Tmp = Tmp + 1
    Next
CountCells = Tmp
End Function

Sub test_CountCells() ' example of using with VBA
Dim SrcRNG As Range, SrcSTR As String, t As Long
Set SrcRNG = Selection ' Range to search (now it's activesheet selected range
SrcSTR = "Here string to search" ' this is the string to search
t = CountCells(SrcRNG, SrcSTR)
Debug.Print t & "/" & SrcRNG.Cells.Count ' print (cells with match)/(cells in range) -just for check
End Sub

 

[TimetablingABC]

What strings are calculated from the cells?
I mean: How do you know what text you are looking for?

I was going to have a table/list of names n then I was hoping for a solution to count how many cells/15 minute blocks are attributed to that name eventually ending up with a list of names and how many hours each name is attributed throughout a timetable. However, the problem I have is most of the blocks of cells have been merged (eg. 3 cells for 45mins, 8 cells for 2 hours) and there are many names across a fortnightly time period, and slots can often change hence my desire to automate the count so I can see at any point how many hours a name has

 

[TimetablingABC]

There is no formula that would do that, the only way it might be possible is by comparison to adjacent data if there is any way to identify the end of the merged cells from that.

With vba it would be easier, unlike a formula, vba does appear to count each cell that is merged rather than just the first one. See if this udf does as needed.
Enter the code into the editor, then use the formula like =countmerge(A2:A20,"cat") in your sheet as normal (no wildcard characters with this one).

Public Function countmerge(rng As Range, criteria As Variant) As Long
Dim tmp As Long, c As Range
For Each c In rng
    If InStr(criteria, c.Value) Then tmp = tmp + 1
Next
countmerge = tmp
End Function

Great, I’ll have a look n give it a try cheers, really appreciate it!

 

[TimetablingABC]

How about this?

'Can be used directly from workbook: =CountCells(Range:Range,"String")
Public Function CountCells(SrcRNG As Range, SrcSTR As String) As Long ' Count ALL cells that contain String(SrcSTR) , including All cells that is Merged together
Dim Cell As Range, Tmp As Long
SrcSTR = "*" & SrcSTR & "*" ' Add * for search string
    For Each Cell In SrcRNG
        If Cell.MergeArea(1).Value Like SrcSTR Then Tmp = Tmp + 1
    Next
CountCells = Tmp
End Function

Sub test_CountCells() ' example of using with VBA
Dim SrcRNG As Range, SrcSTR As String, t As Long
Set SrcRNG = Selection ' Range to search (now it's activesheet selected range
SrcSTR = "Here string to search" ' this is the string to search
t = CountCells(SrcRNG, SrcSTR)
Debug.Print t & "/" & SrcRNG.Cells.Count ' print (cells with match)/(cells in range) -just for check
End Sub

That's great, I’ll give it a go and see, thank you really appreciate it!

 

[Jamie6325]

That's great, I’ll give it a go and see, thank you really appreciate it!

Did any of the solutions work? I have to do the same thing - but each cell represents 30 minutes.