Hello all,
I have a table with multiple columns where in Column A are unique values, in column D are values they are assigned to.
Would like to consolidate all the rows by merging by values in column D while values from column A are listed in one cell
Straightforward, managed that with this code
Couple of problems I need this to be adapted to, if possible
Problem 1. want it to stop with listing at 8 listed values and after that to continue listing in a new row
(if there is 9 unique values from column A associated with value from column D it should be in one row 8A -D and then row below 1A -D
Problem 2: Would be nice that all of this is fitted in a single code which can be run when needed, at the end it should look something like:
(working on a Sheet2)
Hope it makes sense, I can post some data and examples how it should look like, until your response will continue to work hard on Google 
Thanks
I have a table with multiple columns where in Column A are unique values, in column D are values they are assigned to.
Would like to consolidate all the rows by merging by values in column D while values from column A are listed in one cell
Straightforward, managed that with this code
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 FunctionCouple of problems I need this to be adapted to, if possible
Problem 1. want it to stop with listing at 8 listed values and after that to continue listing in a new row
(if there is 9 unique values from column A associated with value from column D it should be in one row 8A -D and then row below 1A -D
Problem 2: Would be nice that all of this is fitted in a single code which can be run when needed, at the end it should look something like:
(working on a Sheet2)
Code:
Sub Concatenate()
1. messagebox to select working range of master table (sheet1)
2. find last used range on sheet2
3. in column D list values from column D in a master table, but remove duplicates
4. in column E do the concatenate function from column A in a master table
5. in column A would be filled formula which I think I can sort it out
6. in column B would be filed formula which I think I can sort it out
7. column C is irrelevant and can be used as helper column if needed
8. in column F I need another example of concatenate, this time from column F in master table with sumif in bracket
9. repeat steps 2 to 8 but this time source is column H in master tableThanks
Last edited:
)
