Can this be done
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Have attached a file...
it has a query sheet and data sheet... data sheet has columns labelled study id and no of cases
query sheet has the concatenated string from the concatenateifs query in the column labelled "Studies quoting".
I want to add the no of cases for each row when the "studies quoting" match the "study id"....
it has a query sheet and data sheet... data sheet has columns labelled study id and no of cases
query sheet has the concatenated string from the concatenateifs query in the column labelled "Studies quoting".
I want to add the no of cases for each row when the "studies quoting" match the "study id"....
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
I'll have a look at it a bit later - I'll be away from my computer for a short while.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
In B2 on the Query Sheet:
=SUMPRODUCT('Data Sheet'!$B$2:$B$45,--ISNUMBER(FIND(","&'Data Sheet'!$A$2:$A$45&",",","&A2&",")))
Fill down to the end of the data, for example by double-clicking the fill handle (the little black square in the lower right corner) of B2.
=SUMPRODUCT('Data Sheet'!$B$2:$B$45,--ISNUMBER(FIND(","&'Data Sheet'!$A$2:$A$45&",",","&A2&",")))
Fill down to the end of the data, for example by double-clicking the fill handle (the little black square in the lower right corner) of B2.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Thanks Hans...
It would be nice if you could explain the formula...... I understand what each of the individual function does.... but I don't understand what they are doing when you have nested them!!... especially the "--" added prior to ISNUMBER?
It would be nice if you could explain the formula...... I understand what each of the individual function does.... but I don't understand what they are doing when you have nested them!!... especially the "--" added prior to ISNUMBER?
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
ah... i see... the "--" are converting the lookup from true/false into 1 or 0....
thats nice!!
thats nice!!
Last edited by drknow1982 on 16 Aug 2014, 16:19, edited 1 time in total.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
FIND(...) either returns a number (if the Study ID is found in Studies Quoting) or an error (if the Study ID is not found).
ISNUMBER(FIND(...)) returns TRUE (if the Study ID is found in Studies Quoting) or FALSE (if the Study ID is not found).
We can't use this directly in SUMPRODUCT - it ignores TRUE/FALSE values, even though TRUE is stored inrernally as 1 and FALSE as 0.
-ISNUMBER(FIND(...)) forces Excel to treat the values as numbers: TRUE becomes -1 (because of the -) and FALSE becomes 0.
The second - in --ISNUMBER(FIND(...)) changes -1 to +1. As a result, each match counts as 1 and each non-match as 0.
This is multiplied with the values in the NO OF CASES column and summed.
ISNUMBER(FIND(...)) returns TRUE (if the Study ID is found in Studies Quoting) or FALSE (if the Study ID is not found).
We can't use this directly in SUMPRODUCT - it ignores TRUE/FALSE values, even though TRUE is stored inrernally as 1 and FALSE as 0.
-ISNUMBER(FIND(...)) forces Excel to treat the values as numbers: TRUE becomes -1 (because of the -) and FALSE becomes 0.
The second - in --ISNUMBER(FIND(...)) changes -1 to +1. As a result, each match counts as 1 and each non-match as 0.
This is multiplied with the values in the NO OF CASES column and summed.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 25 Aug 2014, 17:42
Re: Can this be done
Hello and thank you for thge Concatenateifs function. I am struggling to use the criteria to check for text within the cell. as an example I would normally use the syntax
"*" & "test" & "*"
However the concatenate ifs function does not like that syntax.
The usage is that I am trying to concatenate all notes from a column if they are entered on a specific date.
Any help is appreciated
"*" & "test" & "*"
However the concatenate ifs function does not like that syntax.
The usage is that I am trying to concatenate all notes from a column if they are entered on a specific date.
Any help is appreciated
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
Neither of the versions of ConcatenateIfs that I posted support wildcards. But if you're looking for notes posted on a specific date, I don't think you need wildcards.
Let's say the notes are in B2:B100, the dates in A2:A100, and the specific date you're looking for is in cell K1.
You can then use the ConcatenateIf function from the first reply on page 1 of this thread, and use
=ConcatenateIf(A2:A100,K1,B2:B100,", ")
Neither of the versions of ConcatenateIfs that I posted support wildcards. But if you're looking for notes posted on a specific date, I don't think you need wildcards.
Let's say the notes are in B2:B100, the dates in A2:A100, and the specific date you're looking for is in cell K1.
You can then use the ConcatenateIf function from the first reply on page 1 of this thread, and use
=ConcatenateIf(A2:A100,K1,B2:B100,", ")
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 25 Aug 2014, 17:42
Re: Can this be done
Thank you Hans, I am looking to only concatenate notes that contain particular Text. Basically all notes that contain "text" for a particular date.
Ideally I would reference a header cell to indicate the text I am looking for.
Syntax would be something like "*" & "cell" & "*"
Again thank you
Ideally I would reference a header cell to indicate the text I am looking for.
Syntax would be something like "*" & "cell" & "*"
Again thank you
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
OK, here is a new version that supports wildcards for the "=" and "<>" operators.
Let's say the notes are in B2:B100, the dates in A2:A100, the specific text you're looking for is in L1 and the specific date you're looking for is in cell K1.
The formula would be like this
=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,", ")
The first B2:B100 is the concatenate range. The second B2:B100 is the first criteria range, and "*"&L1&"*" is the first condition (with wildcards). A2:A100 is the second criteria range, and L1 is the second condition.
Code: Select all
Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
' Source: EileensLounge.com, August 2014
Dim i As Long
Dim c As Long
Dim n As Long
Dim f As Boolean
Dim Separator As String
Dim strResult As String
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 3 Then
' Too few arguments
GoTo ErrHandler
End If
If n Mod 3 = 0 Then
' Separator specified explicitly
Separator = Criteria(n)
Else
' Use default separator
Separator = ","
End If
' Loop through the cells of the concatenate range
For i = 1 To ConcatenateRange.Count
' Start by assuming that we have a match
f = True
' Loop through the conditions
For c = 0 To n - 1 Step 3
' Does cell in criteria range match the condition?
Select Case Criteria(c + 1)
Case "<="
If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
f = False
Exit For
End If
Case "<"
If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
f = False
Exit For
End If
Case ">="
If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
f = False
Exit For
End If
Case ">"
If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
f = False
Exit For
End If
Case "<>"
If Criteria(c).Cells(i).Value Like Criteria(c + 2) Then
f = False
Exit For
End If
Case Else
If Not Criteria(c).Cells(i).Value Like Criteria(c + 2) Then
f = False
Exit For
End If
End Select
Next c
' Were all criteria satisfied?
If f Then
' If so, add separator and value to result
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
' Remove first separator
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIfs = strResult
Exit Function
ErrHandler:
ConcatenateIfs = CVErr(xlErrValue)
End Function
The formula would be like this
=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,", ")
The first B2:B100 is the concatenate range. The second B2:B100 is the first criteria range, and "*"&L1&"*" is the first condition (with wildcards). A2:A100 is the second criteria range, and L1 is the second condition.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 25 Aug 2014, 17:42
Re: Can this be done
Thank you, That seems to do it.
I appreciate all t he help.
Josh
I appreciate all t he help.
Josh
-
- NewLounger
- Posts: 3
- Joined: 26 Aug 2014, 17:33
Re: Can this be done
Well, I'm surprised, I didn't know that it was possible to do. Excel always surprises me.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Can this be done
Welcome to the lounge...
I have been using Excel for more than 2 decades and I still learn new things on a regular basis. Excel is a bottomless cup of coffee, literally!! Between the two powerhouses of Excel; it's logic formulas and macros, one can tweak Excel to do everything besides wash the dishes...
I have learned to hesitate before I say, no...Excel cannot do that!
I have been using Excel for more than 2 decades and I still learn new things on a regular basis. Excel is a bottomless cup of coffee, literally!! Between the two powerhouses of Excel; it's logic formulas and macros, one can tweak Excel to do everything besides wash the dishes...
I have learned to hesitate before I say, no...Excel cannot do that!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- NewLounger
- Posts: 3
- Joined: 26 Aug 2014, 17:33
Re: Can this be done
Yes, I agree with you, but i'm feeling sad when these things happen, I think that i'm a novice user
-
- NewLounger
- Posts: 1
- Joined: 16 Dec 2014, 22:19
Re: Can this be done
I'm wondering if there is an easy adjustment to this:
Some of the cells that my ConcatenateIfs return are blank, so a few of the concatenations look like:
/ / / / / / / / #2much$W@G/ / / / a great personality and great fellow overall/ / / / / / / VERY helpful and informative/ / / / / / Not an a--hole/ / / / /
using "/ " as the separator.
I was thinking that I needed some thing that worked like an IsNotBlank
I tried adding criteria that work with countifs: [range], "*" or [range],"><"&"" where [range] was the same as [ConcatenateRange] but with each of those, the cells were just blank.
I'm not sure if there's a way to do this within the function or if it would have to be modified in VBA, in which case, I have no idea.
Thanks,
Laurel
Some of the cells that my ConcatenateIfs return are blank, so a few of the concatenations look like:
/ / / / / / / / #2much$W@G/ / / / a great personality and great fellow overall/ / / / / / / VERY helpful and informative/ / / / / / Not an a--hole/ / / / /
using "/ " as the separator.
I was thinking that I needed some thing that worked like an IsNotBlank
I tried adding criteria that work with countifs: [range], "*" or [range],"><"&"" where [range] was the same as [ConcatenateRange] but with each of those, the cells were just blank.
I'm not sure if there's a way to do this within the function or if it would have to be modified in VBA, in which case, I have no idea.
Thanks,
Laurel
Last edited by HansV on 16 Dec 2014, 23:07, edited 1 time in total.
Reason: strong language
Reason: strong language
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
You can add [range], "<>", ""
For example:
You can add [range], "<>", ""
For example:
Code: Select all
=ConcatenateIfs(A2:A50, A2:A50, "<>", "")
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 26 Jan 2015, 18:18
Re: Can this be done
Hello,
I have found your concatenateifs formula to apply towards my spreadsheet. However, I have over 5000 rows of data to search through, and the workbook freezes up every time I attempt to apply it to all rows. Do you know of a way around this?
Thanks!
I have found your concatenateifs formula to apply towards my spreadsheet. However, I have over 5000 rows of data to search through, and the workbook freezes up every time I attempt to apply it to all rows. Do you know of a way around this?
Thanks!
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
The ConcatenateIfs function has to perform a very large number of calculations, so slowness is unavoidable. If using it in cell formulas brings Excel to a halt, it's probably best not to use formulas, but to write a macro that populates cells with the result of ConcatenateIfs, and to only call this macro when you really need the values to be up-to-date.
The ConcatenateIfs function has to perform a very large number of calculations, so slowness is unavoidable. If using it in cell formulas brings Excel to a halt, it's probably best not to use formulas, but to write a macro that populates cells with the result of ConcatenateIfs, and to only call this macro when you really need the values to be up-to-date.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 26 Jan 2015, 18:18
Re: Can this be done
Thank you! I will try that!
-
- NewLounger
- Posts: 2
- Joined: 20 May 2015, 19:52
Re: Can this be done
Hi,
I'm using the ConcatenateIfs, and would like to add vbnewline separator in the cell instead of the Separator = ", "
How could I do this ?
Thanks,
Voff
I'm using the ConcatenateIfs, and would like to add vbnewline separator in the cell instead of the Separator = ", "
How could I do this ?
Thanks,
Voff