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

V

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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

W
  • Solved
Return values in comma separated format when specific text is found in a string
2
Replies
10
Views
718
Cubist
Cubist
J
  • Question Question
Extract list of cells from one column in to one cell and separate by a comma
Replies
8
Views
134
Peter_SSs
Peter_SSs
K
  • Solved
combobox values with validation from adjacent cell
Replies
4
Views
92
kampog
K
R
  • Solved
Vlookup help for comma separated values
2
Replies
13
Views
1K
Peter_SSs
Peter_SSs
E
  • Question Question
How to: Multiply only numbers that contain values higher than 0
Replies
2
Views
409
eDarren
E

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

Share this page

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