put content of adjacent cell (comma-separated) into cell if value is higher than x

vreni

New Member
Joined
Feb 25, 2015
Messages
6
Hi there,

I am an absolute VBA newbie and need help:

I have a list of values in cell A and in B a list of verbs.
I want to put all verbs which have a values higher than 3 in a separate cell and make excel separate them with a comma, semi-colon or the sorts.

Any pointers would be very much appreciated!
Thanks a lot
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
vreni,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture) try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
[TABLE="width: 822"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]attracted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]79.0086[/TD]
[TD="colspan: 2"]UNDERSTAND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62.8487[/TD]
[TD]BELIEVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]57.6241[/TD]
[TD]GET[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53.1274[/TD]
[TD]HURRY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40.1141[/TD]
[TD]WATCH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31.1562[/TD]
[TD]PLACE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22.891[/TD]
[TD]START[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20.3585[/TD]
[TD]PROTECT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14.8321[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11.7753[/TD]
[TD="colspan: 2"]COORDINATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10.2592[/TD]
[TD]INFORM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9.7213[/TD]
[TD]WAKE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.8179[/TD]
[TD]LISTEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.1533[/TD]
[TD]ENJOY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.1232[/TD]
[TD]STOP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7.5305[/TD]
[TD]DUCK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.9355[/TD]
[TD]LEARN[/TD]
[TD][/TD]
[TD="colspan: 2"]>|3 |=> p<0.001[/TD]
[TD]understand, believe, get, hurry, watch, place, start, protect, check, coordinate, inform, wake, listen, enjoy, stop, duck, learn, hope, prepare, beware, shape, heed, assess, shut, batten, sit, behave, hold, rethink, handle, figure, manage, secure, educate[/TD]
[/TR]
[TR]
[TD="align: right"]6.7379[/TD]
[TD]HOPE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.7376[/TD]
[TD]PREPARE[/TD]
[TD][/TD]
[TD="colspan: 2"]>|2 |=> p<0.01[/TD]
[TD]define, organize, keep, …[/TD]
[/TR]
[TR]
[TD="align: right"]6.678[/TD]
[TD]BEWARE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.3196[/TD]
[TD]SHAPE[/TD]
[TD][/TD]
[TD="colspan: 3"]>|1.30103 |=> p<0.05.[/TD]
[/TR]
[TR]
[TD="align: right"]5.7873[/TD]
[TD]HEED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.6307[/TD]
[TD]ASSESS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.2787[/TD]
[TD]SHUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.0203[/TD]
[TD]BATTEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.8226[/TD]
[TD]SIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.3143[/TD]
[TD]BEHAVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.0751[/TD]
[TD]HOLD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.9441[/TD]
[TD]RETHINK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.9277[/TD]
[TD]HANDLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.8351[/TD]
[TD]FIGURE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.744[/TD]
[TD]MANAGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.7252[/TD]
[TD]SECURE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.1965[/TD]
[TD]EDUCATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 822"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]attracted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]79.0086[/TD]
[TD="colspan: 2"]UNDERSTAND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62.8487[/TD]
[TD]BELIEVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]57.6241[/TD]
[TD]GET[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53.1274[/TD]
[TD]HURRY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40.1141[/TD]
[TD]WATCH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31.1562[/TD]
[TD]PLACE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22.891[/TD]
[TD]START[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20.3585[/TD]
[TD]PROTECT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14.8321[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11.7753[/TD]
[TD="colspan: 2"]COORDINATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10.2592[/TD]
[TD]INFORM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9.7213[/TD]
[TD]WAKE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.8179[/TD]
[TD]LISTEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.1533[/TD]
[TD]ENJOY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.1232[/TD]
[TD]STOP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7.5305[/TD]
[TD]DUCK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.9355[/TD]
[TD]LEARN[/TD]
[TD][/TD]
[TD="colspan: 2"]>|3 |=> p<0.001[/TD]
[TD]understand, believe, get, hurry, watch, place, start, protect, check, coordinate, inform, wake, listen, enjoy, stop, duck, learn, hope, prepare, beware, shape, heed, assess, shut, batten, sit, behave, hold, rethink, handle, figure, manage, secure, educate[/TD]
[/TR]
[TR]
[TD="align: right"]6.7379[/TD]
[TD]HOPE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.7376[/TD]
[TD]PREPARE[/TD]
[TD][/TD]
[TD="colspan: 2"]>|2 |=> p<0.01[/TD]
[TD]define, organize, keep, …[/TD]
[/TR]
[TR]
[TD="align: right"]6.678[/TD]
[TD]BEWARE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.3196[/TD]
[TD]SHAPE[/TD]
[TD][/TD]
[TD="colspan: 3"]>|1.30103 |=> p<0.05.[/TD]
[/TR]
[TR]
[TD="align: right"]5.7873[/TD]
[TD]HEED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.6307[/TD]
[TD]ASSESS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.2787[/TD]
[TD]SHUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.0203[/TD]
[TD]BATTEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.8226[/TD]
[TD]SIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.3143[/TD]
[TD]BEHAVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.0751[/TD]
[TD]HOLD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.9441[/TD]
[TD]RETHINK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.9277[/TD]
[TD]HANDLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.8351[/TD]
[TD]FIGURE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.744[/TD]
[TD]MANAGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.7252[/TD]
[TD]SECURE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.1965[/TD]
[TD]EDUCATE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
vreni,

You have posted flat text which I would have to separate, and, that makes no sense.


To post a small screen shot (NOT a graphic, or, picture) try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
The OP could use this UDF. The arguments of ConcatIf mirror those of SUMIF, with the addition of an optional delimiter argument.

A formula like =ConcatIf(A:A, ">3", B:B, ",") will put a comma delimited string composed of strings from column B where column A has a value greater than 3.

Code:
Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function
 
Upvote 0
wow, that's great, thanks a lot!
Is there any way to tell ConcatIf to select only those values within a certain range, i.e. <3 and >1.30103?

I tried =ConcatIf(E:E, ">1.30103",D:D, ",")-ConcatIf(E:E, "<2",D:D, ","),
but that does not work...
 
Upvote 0
That would need ConcatIfs. The (optional) Delimiter is the last argument passed to ConcatIfs.


Code:
Function ConcatIfs(StringsArray As Variant, ParamArray Criterias() As Variant) As String
    Dim Delimiter As String
    Dim Size As Long
    Dim RowCount As Long, ColumnCount As Long
    Dim RowOfInterest As Long, ColumnOfInterest As Long
    Dim i As Long, j As Long, k As Long
    Dim flag As Boolean
    
    Delimiter = " ": Rem default delimiter
    Size = UBound(Criterias)
    If UBound(Criterias) Mod 2 = 0 Then
        Delimiter = Criterias(UBound(Criterias))
        Size = Size - 1
    End If
    If TypeName(StringsArray) = "Range" Then
        If StringsArray.Cells.Count = 1 Then
            StringsArray = Array(StringsArray.Value)
        Else
            StringsArray = StringsArray.Value
        End If
    End If
    
    RowCount = UBound(StringsArray, 1)
    ColumnCount = UBound(StringsArray, 2)
    For k = 0 To Size Step 2
        With Criterias(k)
            Set Criterias(k) = Application.Intersect(.Cells, .Parent.UsedRange)
        End With
        
        With Criterias(k)
            If RowCount < .Rows.Count Then RowCount = .Rows.Count
            If ColumnCount < .Columns.Count Then ColumnCount = .Columns.Count
        End With
    Next k
    
    For i = 1 To RowCount
        For j = 1 To ColumnCount
            flag = True
            For k = 0 To Size Step 2
                RowOfInterest = Application.Min(i, Criterias(k).Rows.Count)
                ColumnOfInterest = Application.Min(j, Criterias(k).Columns.Count)
                flag = flag And (WorksheetFunction.CountIf(Criterias(k).Cells(RowOfInterest, ColumnOfInterest), Criterias(k + 1)) = 1)
            Next k
            If flag Then
                RowOfInterest = Application.Min(i, UBound(StringsArray, 1))
                ColumnOfInterest = Application.Min(j, UBound(StringsArray, 2))
                ConcatIfs = ConcatIfs & Delimiter & CStr(StringsArray(RowOfInterest, ColumnOfInterest))
            End If
        Next j
    Next i
    ConcatIfs = Mid(ConcatIfs, Len(Delimiter) + 1)
End Function
 
Upvote 0
Great, thanks a lot!!!

Only one dumb question left.... How do I phrase this in the function?
I've tried this, but none of it worked...

=ConcatIfs($H8:$H1504,"<3",">2",$B8:$B1504, ",")
=ConcatIfs($H8:$H1504,"<3"&">2",$B8:$B1504, ",")
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top