Concatenate with conditions

[dado6612]

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

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

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)

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 table

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

 

[dado6612]

I have 2 photos here which might get picture clear, if not and someone wish to help but has any question, please go ahead
https://ibb.co/yFCYLRq - master
https://ibb.co/VLTD7Qk - after code

 

[dado6612]

Can't be done? Too much to ask? Anyone to give a try on any step? Thanks

 

[dado6612]

Maybe just concatenate part then.. in column b are numbers, in column D fruit names. put on a new sheet in column B fruit name and in column C in one row all the numbers assigned to them, but max 8 per cell, then in moves next row
ex.
1 apple
2 apple
3 banana
4 apple
5 apple
6 apple
7 apple
8 apple
9 apple
10 apple
11 banana
turns to
apple 1 2 4 5 6 7 8 9
apple 10
banana 3

Thanks
(any feedback (even negative one) is better than seen)