need to add heading if data is less then 30

[sksanjeev786]

Hi Team

Below is the data B-D ,E-G,H-I, K-M, N-P will have in the same set with 3 columns and if any between 3 columns have less than 30 score
I need to add a note like Colunm B9..

Currently, B5 , F5, M5 have data less than so I have added those names Fro Row 6 to B9

book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5	22	270	270	57	12	260	81	442	442	147	780	2	26	117	117
6	DMA: Atlanta			DMA: Dallas - Ft. Worth			DMA: Los Angeles			DMA: New York City			DMA: Washington, DC (Hagrstwn)
7
8
9	DMA: Atlanta, DMA: Dallas - Ft. Worth, DMA: New York City
Sheet1

 

[Sam_D_Ben]

May be something like this,

Sub Addheadingifdataislessthen30()
    Dim result      As String
    Dim startColumn As Integer
    Dim endColumn   As Integer
    Dim lastColumn  As Integer
    Dim i           As Integer
    Dim mergedCell  As Range
    Dim cell        As Range
    
    result = ""
    
    lastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
    
    For startColumn = 2 To lastColumn Step 3
        endColumn = startColumn + 2
        
        If endColumn > lastColumn Then
            endColumn = lastColumn
        End If
        
        For i = startColumn To endColumn
            Set cell = Cells(5, i)
            
            If cell.Value < 30 Then
                Set mergedCell = cell.Offset(1, 0).MergeArea
                
                If result = "" Then
                    result = mergedCell.Cells(1, 1).Value
                Else
                    result = result & ", " & mergedCell.Cells(1, 1).Value
                End If
                
                Exit For
            End If
        Next i
    Next startColumn
    
    If result <> "" Then
        Range("B9").Value = result
    Else
        Range("B9").Value = "No values below 30"
    End If
End Sub

 

[Peter_SSs]

Currently, B5 , F5, M5 have data less than so I have added those names Fro Row 6 to B9

What about N5 (26)?

Here is a formula approach assuming you are using your 365 version.

25 02 08.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5	22	270	270	57	12	260	81	442	442	147	780	2	26	117	117
6	DMA: Atlanta			DMA: Dallas - Ft. Worth			DMA: Los Angeles			DMA: New York City			DMA: Washington, DC (Hagrstwn)
7
8
9	DMA: Atlanta, DMA: Dallas - Ft. Worth, DMA: New York City, DMA: Washington, DC (Hagrstwn)
<30

Cell Formulas
Range	Formula
B9	B9	=TEXTJOIN(", ",1,BYCOL(B6:P6,LAMBDA(c,IF(AND(c<>"",MIN(OFFSET(c,-1,0,1,3))<30),c,""))))

 

[Peter_SSs]

If you did want a vba approach, you could try this

Sub LessThan30()
  Dim c As Range
  Dim s As String
  
  For Each c In Range("B6", Cells(6, Columns.Count).End(xlToLeft))
    If Len(c.Value) > 0 Then
      If Application.Min(c.Offset(-1).Resize(, 3)) < 30 Then s = s & ", " & c.Value
    End If
  Next c
  Range("B9").Value = Mid(s, 3)
End Sub

 

[HongRu]

Try it.
Inspired by Peter_SSs's code.

Sub Less30()
    Do
        x = Application.Min(Range("B5:D5").Offset(0, i))
        If x = 0 Then Exit Do
        If x < 30 Then y = y & "," & Chr(10) & Range("B5").Offset(1, i)
        i = i + 3
    Loop
    Range("B9") = Mid(y, 3)
End Sub

 

[Peter_SSs]

Try it.

I tried it but I don't like your method of finishing the loop. When I tried it I had 0 in cell C5 & got nothing as my result.
I also like to declare all my variables, so I would use something more like this

Sub Less30_v2()
  Dim i As Long, lc As Long
  Dim x As Double
  Dim y As String
  
  lc = Cells(5, Columns.Count).End(xlToLeft).Column
  i = 2
  Do While i < lc
    x = Application.Min(Cells(5, i).Resize(, 3))
    If x < 30 Then y = y & ", " & Cells(6, i).Value
    i = i + 3
  Loop
  Range("B9") = Mid(y, 3)
End Sub

 

[DanteAmor]

Hi @sksanjeev786

It is advisable not to use volatile functions such as offset, indirect, etc.
More about volatile functions:

Volatile function​

Volatile Excel Functions​

Try the following formula:

=TEXTJOIN(", ",1,BYCOL(B6:P6,LAMBDA(a,IF(MIN(TAKE(INDEX(a,1):P5,,3))<30,a,""))))

I also propose the following macro:

Sub AddHeadingLess30()
  Dim j As Long, s As String
  For j = 2 To Cells(5, Columns.Count).End(1).Column Step 3
    If WorksheetFunction.Min(Cells(5, j).Resize(, 3)) < 30 Then s = s & Cells(6, j).Text & ", "
  Next
  If s <> "" Then [B9] = Left(s, Len(s) - 2)
End Sub

 

[sksanjeev786]

Hi @sksanjeev786

It is advisable not to use volatile functions such as offset, indirect, etc.
More about volatile functions:

Volatile function​

Volatile Excel Functions​

Try the following formula:

=TEXTJOIN(", ",1,BYCOL(B6:P6,LAMBDA(a,IF(MIN(TAKE(INDEX(a,1):P5,,3))<30,a,""))))

I also propose the following macro:

Sub AddHeadingLess30()
  Dim j As Long, s As String
  For j = 2 To Cells(5, Columns.Count).End(1).Column Step 3
    If WorksheetFunction.Min(Cells(5, j).Resize(, 3)) < 30 Then s = s & Cells(6, j).Text & ", "
  Next
  If s <> "" Then [B9] = Left(s, Len(s) - 2)
End Sub

Hi Sir,

its is working very well

 

[sksanjeev786]

May be something like this,

Sub Addheadingifdataislessthen30()
    Dim result      As String
    Dim startColumn As Integer
    Dim endColumn   As Integer
    Dim lastColumn  As Integer
    Dim i           As Integer
    Dim mergedCell  As Range
    Dim cell        As Range
  
    result = ""
  
    lastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
  
    For startColumn = 2 To lastColumn Step 3
        endColumn = startColumn + 2
      
        If endColumn > lastColumn Then
            endColumn = lastColumn
        End If
      
        For i = startColumn To endColumn
            Set cell = Cells(5, i)
          
            If cell.Value < 30 Then
                Set mergedCell = cell.Offset(1, 0).MergeArea
              
                If result = "" Then
                    result = mergedCell.Cells(1, 1).Value
                Else
                    result = result & ", " & mergedCell.Cells(1, 1).Value
                End If
              
                Exit For
            End If
        Next i
    Next startColumn
  
    If result <> "" Then
        Range("B9").Value = result
    Else
        Range("B9").Value = "No values below 30"
    End If
End Sub

Thank you so much Sir it is working

 

[DanteAmor]

Currently, B5 , F5, M5 have data less than so I have added those names Fro Row 6 to B9

In your example, you must also include N5