Home |
Search |
Today's Posts |
#1
|
|||
|
|||
concatenate like sumif
Is there a function like SUMIF for text values that concatenates rather than
sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#2
|
|||
|
|||
Simon,
Would a custom function do? This is a simplified example that assumes that the data is in columns A:C starting in Row 1. Functions cannot change the environment so you would have to select to wrap text and expand the row height yourself. Public Function Test(TruckNo) As String Dim EndRow As Long Dim NN As String Dim x as Long EndRow = Cells(Rows.Count, 1).End(xlUp) For x = 1 To EndRow If Cells(x, 1).Value = TruckNo Then NN = NN & Cells(x, 2).Value _ & " : " & Cells(x, 3).Value & Chr(10) Next x NN = Left(NN, Len(NN) - 1) Test = NN End Function HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Simon Shaw" wrote in message ... Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#3
|
|||
|
|||
Nice code Sandy;
Using the example I Inserted 5 rows at top of sheet before showing the table (with 3 header columns on Row 6, data begins on Row7 (A:C) Where your code shows: For x = 1 To EndRow I would have thought that I would need to substitute "7" for your 1 but it doesn't seem to matter... I'm sure I'm missing something here, but what? TIA, "Sandy Mann" wrote in message ... Simon, Would a custom function do? This is a simplified example that assumes that the data is in columns A:C starting in Row 1. Functions cannot change the environment so you would have to select to wrap text and expand the row height yourself. Public Function Test(TruckNo) As String Dim EndRow As Long Dim NN As String Dim x as Long EndRow = Cells(Rows.Count, 1).End(xlUp) For x = 1 To EndRow If Cells(x, 1).Value = TruckNo Then NN = NN & Cells(x, 2).Value _ & " : " & Cells(x, 3).Value & Chr(10) Next x NN = Left(NN, Len(NN) - 1) Test = NN End Function HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Simon Shaw" wrote in message ... Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#4
|
|||
|
|||
Looking further at the code:
For x = 1 To EndRow obviously it is starting with Row1 everytime and tests for TruckNo, retuning nothing if there is nothing, so in my case it doesn't pick up anything until it gets to Row7, right? hummmm beginner coder here.... Thanks, Jim "Jim May" wrote in message news:IwZUd.4489$2s.525@lakeread06... Nice code Sandy; Using the example I Inserted 5 rows at top of sheet before showing the table (with 3 header columns on Row 6, data begins on Row7 (A:C) Where your code shows: For x = 1 To EndRow I would have thought that I would need to substitute "7" for your 1 but it doesn't seem to matter... I'm sure I'm missing something here, but what? TIA, "Sandy Mann" wrote in message ... Simon, Would a custom function do? This is a simplified example that assumes that the data is in columns A:C starting in Row 1. Functions cannot change the environment so you would have to select to wrap text and expand the row height yourself. Public Function Test(TruckNo) As String Dim EndRow As Long Dim NN As String Dim x as Long EndRow = Cells(Rows.Count, 1).End(xlUp) For x = 1 To EndRow If Cells(x, 1).Value = TruckNo Then NN = NN & Cells(x, 2).Value _ & " : " & Cells(x, 3).Value & Chr(10) Next x NN = Left(NN, Len(NN) - 1) Test = NN End Function HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Simon Shaw" wrote in message ... Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#5
|
|||
|
|||
"Jim May" wrote in message
news:NIZUd.4492$2s.723@lakeread06... Looking further at the code: For x = 1 To EndRow obviously it is starting with Row1 everytime and tests for TruckNo, retuning nothing if there is nothing, so in my case it doesn't pick up anything until it gets to Row7, right? Jim, Yes that is right. So it is bad coding really because it *could* find a TruckNo outside of the table of data. It would be much better therefore, providing that you have at least one blank cell at the end of the table before any other data, to start at your start of table and count down thus: Public Function Test(TruckNo) As String Dim EndRow As Long Dim NN As String Dim x As Long EndRow = Range("A7").End(xlDown).Row For x = 7 To EndRow If Cells(x, 1).Value = TruckNo Then NN = NN & Cells(x, 2).Value _ & " : " & Cells(x, 3).Value & Chr(10) Next x NN = Left(NN, Len(NN) - 1) Test = NN End Function That will limit the code to looking at the table of data only - PROVIDED that you do not add any rows above the start of the table. If that is liable to happen then you would have to code in a way of finding the start of the table. (VBA is not part of Excel so it does not alter references the way that true functions do when rows or columns are changed.) Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk |
#6
|
|||
|
|||
Sandy:
Thanks for taking the extra time to clear my question(s). Much appreciated, Jim "Sandy Mann" wrote in message ... "Jim May" wrote in message news:NIZUd.4492$2s.723@lakeread06... Looking further at the code: For x = 1 To EndRow obviously it is starting with Row1 everytime and tests for TruckNo, retuning nothing if there is nothing, so in my case it doesn't pick up anything until it gets to Row7, right? Jim, Yes that is right. So it is bad coding really because it *could* find a TruckNo outside of the table of data. It would be much better therefore, providing that you have at least one blank cell at the end of the table before any other data, to start at your start of table and count down thus: Public Function Test(TruckNo) As String Dim EndRow As Long Dim NN As String Dim x As Long EndRow = Range("A7").End(xlDown).Row For x = 7 To EndRow If Cells(x, 1).Value = TruckNo Then NN = NN & Cells(x, 2).Value _ & " : " & Cells(x, 3).Value & Chr(10) Next x NN = Left(NN, Len(NN) - 1) Test = NN End Function That will limit the code to looking at the table of data only - PROVIDED that you do not add any rows above the start of the table. If that is liable to happen then you would have to code in a way of finding the start of the table. (VBA is not part of Excel so it does not alter references the way that true functions do when rows or columns are changed.) Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk |
#7
|
|||
|
|||
The brilliance of the people on this site never ceases to amaze me... thanks
again "Sandy Mann" wrote: "Jim May" wrote in message news:NIZUd.4492$2s.723@lakeread06... Looking further at the code: For x = 1 To EndRow obviously it is starting with Row1 everytime and tests for TruckNo, retuning nothing if there is nothing, so in my case it doesn't pick up anything until it gets to Row7, right? Jim, Yes that is right. So it is bad coding really because it *could* find a TruckNo outside of the table of data. It would be much better therefore, providing that you have at least one blank cell at the end of the table before any other data, to start at your start of table and count down thus: Public Function Test(TruckNo) As String Dim EndRow As Long Dim NN As String Dim x As Long EndRow = Range("A7").End(xlDown).Row For x = 7 To EndRow If Cells(x, 1).Value = TruckNo Then NN = NN & Cells(x, 2).Value _ & " : " & Cells(x, 3).Value & Chr(10) Next x NN = Left(NN, Len(NN) - 1) Test = NN End Function That will limit the code to looking at the table of data only - PROVIDED that you do not add any rows above the start of the table. If that is liable to happen then you would have to code in a way of finding the start of the table. (VBA is not part of Excel so it does not alter references the way that true functions do when rows or columns are changed.) Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk |
#8
|
|||
|
|||
My solution:
Public Function ConcatenateIF(Lookup_Value_Range As Range, _ Match_Range As Range, _ Concatenate_Range As Range) As String Dim x As Long Dim Lookup_Value As String Dim Source_Cell As Range Dim Lookup_Row_Count As Long Set Source_Cell = Application.Caller Lookup_Value = Lookup_Value_Range _ .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value ConcatenateIF = "" If Lookup_Value < 0 Then Lookup_Row_Count = Match_Range.Rows.Count For x = 1 To Lookup_Row_Count If Lookup_Value = Match_Range.Cells(x, 1).Value _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Value Else ConcatenateIF = ConcatenateIF & Chr(10) & Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function "Simon Shaw" wrote: Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#9
|
|||
|
|||
another option:
--------------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'created by Gdall - with acknowledgement to Harold Staff 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'NOTE the above two ranges must be indentically sized. 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function -------- Cheers JulieD "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... My solution: Public Function ConcatenateIF(Lookup_Value_Range As Range, _ Match_Range As Range, _ Concatenate_Range As Range) As String Dim x As Long Dim Lookup_Value As String Dim Source_Cell As Range Dim Lookup_Row_Count As Long Set Source_Cell = Application.Caller Lookup_Value = Lookup_Value_Range _ .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value ConcatenateIF = "" If Lookup_Value < 0 Then Lookup_Row_Count = Match_Range.Rows.Count For x = 1 To Lookup_Row_Count If Lookup_Value = Match_Range.Cells(x, 1).Value _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Value Else ConcatenateIF = ConcatenateIF & Chr(10) & Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function "Simon Shaw" wrote: Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#10
|
|||
|
|||
thanks, I should have tested my code better first... I will be more careful
next time I post... Simon "JulieD" wrote: another option: --------------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'created by Gdall - with acknowledgement to Harold Staff 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'NOTE the above two ranges must be indentically sized. 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function -------- Cheers JulieD "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... My solution: Public Function ConcatenateIF(Lookup_Value_Range As Range, _ Match_Range As Range, _ Concatenate_Range As Range) As String Dim x As Long Dim Lookup_Value As String Dim Source_Cell As Range Dim Lookup_Row_Count As Long Set Source_Cell = Application.Caller Lookup_Value = Lookup_Value_Range _ .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value ConcatenateIF = "" If Lookup_Value < 0 Then Lookup_Row_Count = Match_Range.Rows.Count For x = 1 To Lookup_Row_Count If Lookup_Value = Match_Range.Cells(x, 1).Value _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Value Else ConcatenateIF = ConcatenateIF & Chr(10) & Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function "Simon Shaw" wrote: Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#11
|
|||
|
|||
Hi Simon
don't apologise .. my comments weren't meant as a criticism ... Cheers JulieD "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... thanks, I should have tested my code better first... I will be more careful next time I post... Simon "JulieD" wrote: another option: --------------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'created by Gdall - with acknowledgement to Harold Staff 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'NOTE the above two ranges must be indentically sized. 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function -------- Cheers JulieD "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... My solution: Public Function ConcatenateIF(Lookup_Value_Range As Range, _ Match_Range As Range, _ Concatenate_Range As Range) As String Dim x As Long Dim Lookup_Value As String Dim Source_Cell As Range Dim Lookup_Row_Count As Long Set Source_Cell = Application.Caller Lookup_Value = Lookup_Value_Range _ .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value ConcatenateIF = "" If Lookup_Value < 0 Then Lookup_Row_Count = Match_Range.Rows.Count For x = 1 To Lookup_Row_Count If Lookup_Value = Match_Range.Cells(x, 1).Value _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Value Else ConcatenateIF = ConcatenateIF & Chr(10) & Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function "Simon Shaw" wrote: Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#12
|
|||
|
|||
Question:
Does loading the Ranges into arrays and using: For each cell in the myRange speed up the calculation time? Is this better than using the for x = 1 to myRange.Rows.Count check_something = myRange.Cells(x, 1).Value next x style of coding "JulieD" wrote: another option: --------------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'created by Gdall - with acknowledgement to Harold Staff 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'NOTE the above two ranges must be indentically sized. 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function -------- Cheers JulieD "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... My solution: Public Function ConcatenateIF(Lookup_Value_Range As Range, _ Match_Range As Range, _ Concatenate_Range As Range) As String Dim x As Long Dim Lookup_Value As String Dim Source_Cell As Range Dim Lookup_Row_Count As Long Set Source_Cell = Application.Caller Lookup_Value = Lookup_Value_Range _ .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value ConcatenateIF = "" If Lookup_Value < 0 Then Lookup_Row_Count = Match_Range.Rows.Count For x = 1 To Lookup_Row_Count If Lookup_Value = Match_Range.Cells(x, 1).Value _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Value Else ConcatenateIF = ConcatenateIF & Chr(10) & Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function "Simon Shaw" wrote: Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#13
|
|||
|
|||
not to worry... I appreciate your comments and testing.
Thanks Simon "JulieD" wrote: Hi Simon don't apologise .. my comments weren't meant as a criticism ... Cheers JulieD "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... thanks, I should have tested my code better first... I will be more careful next time I post... Simon "JulieD" wrote: another option: --------------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'created by Gdall - with acknowledgement to Harold Staff 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'NOTE the above two ranges must be indentically sized. 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function -------- Cheers JulieD "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... My solution: Public Function ConcatenateIF(Lookup_Value_Range As Range, _ Match_Range As Range, _ Concatenate_Range As Range) As String Dim x As Long Dim Lookup_Value As String Dim Source_Cell As Range Dim Lookup_Row_Count As Long Set Source_Cell = Application.Caller Lookup_Value = Lookup_Value_Range _ .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value ConcatenateIF = "" If Lookup_Value < 0 Then Lookup_Row_Count = Match_Range.Rows.Count For x = 1 To Lookup_Row_Count If Lookup_Value = Match_Range.Cells(x, 1).Value _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Value Else ConcatenateIF = ConcatenateIF & Chr(10) & Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function "Simon Shaw" wrote: Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
#14
|
|||
|
|||
Improved Solution: (thanks for your help JulieD)
----------------------------------------------------------------- Public Function ConcatenateIF(Match_Range As Range, _ Criteria_Range As Range, _ Concatenate_Range As Range) As String ' created by Simon Shaw ' Match_Range - Range to match the criteria against ' Criteria_Range - Range to get the criteria to match ' against the Match_Range. ' if range is more than one cell it will pull the value ' from the same row as the application.caller ' Concatenate_Range - Range to concatenate text from ' Match_Range and Concatenate_Range must be the same size Dim x As Long Dim Criteria_Value As String Dim Source_Cell As Range Dim Match_Row_Count As Long If Match_Range.Rows.Count < Concatenate_Range.Rows.Count Then Exit Function End If Set Source_Cell = Application.Caller If Criteria_Range.Rows.Count 1 Then Criteria_Value = Criteria_Range _ .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text Else Criteria_Value = Criteria_Range.Text End If ConcatenateIF = "" If Criteria_Value < "" Then Match_Row_Count = Match_Range.Rows.Count For x = 1 To Match_Row_Count If Criteria_Value = Match_Range.Cells(x, 1).Text _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Text Else ConcatenateIF = ConcatenateIF & Chr(10) & _ Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function ----------------------------------------------------------- "Simon Shaw" wrote: My solution: Public Function ConcatenateIF(Lookup_Value_Range As Range, _ Match_Range As Range, _ Concatenate_Range As Range) As String Dim x As Long Dim Lookup_Value As String Dim Source_Cell As Range Dim Lookup_Row_Count As Long Set Source_Cell = Application.Caller Lookup_Value = Lookup_Value_Range _ .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value ConcatenateIF = "" If Lookup_Value < 0 Then Lookup_Row_Count = Match_Range.Rows.Count For x = 1 To Lookup_Row_Count If Lookup_Value = Match_Range.Cells(x, 1).Value _ And Concatenate_Range.Cells(x, 1).Value < 0 Then If ConcatenateIF = "" Then ConcatenateIF = Concatenate_Range.Cells(x, 1).Value Else ConcatenateIF = ConcatenateIF & Chr(10) & Concatenate_Range.Cells(x, 1).Value End If End If Next x End If End Function "Simon Shaw" wrote: Is there a function like SUMIF for text values that concatenates rather than sums? I have a set of data as follows: Truck No. Product Comments ----------- --------- ------------------ 123 R-Gas Fully Reconciled 456 R-Gas Tank Dried 123 G-Gas Refilled There are other columns of numbers. I then have a summary row for each truck number using sumif for the number columns, but I need to concatenate the comments in the summary row for say truck 123 to be: "R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled" with the results in one text wrapped cell displayed as follows: R-Gas: Fully Reconciled R-Gas: Refilled Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
help with "criterea" in the sumif function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |