TextJoin VBA macro from one blank cell to another

[AECJohn]

Hi,

I am trying to create a macro that does a text join for the data between two blank cells (see example table below). I would like it to do a text join for the invoice numbers for each company, in the company total column. Is this possible?

Name	Euro	USD	Rate	Invoice	Requested By	Vendor Contact	Due Date	Paid Date	Status	Tab
Company A	€ 7,236.92	$ 7,633.32		99-123456			20/05/2022​	01/11/2022	Exported	01.11.22
Company A	€ 5,262.19	$ 5,525.30	1.050000095​	99-123457			21/05/2022​	01/11/2022	Exported	01.11.22
Company A	€ 1,974.73	$ 2,108.02	1.067497835​	99-123458			22/05/2022​	01/11/2022	Exported	01.11.22
Company A	€ 7,236.92	$ 7,633.32		99-123459			30/05/2022​	01/11/2022	Exported	01.11.22
Company A	€ 7,236.92	$ 7,633.32		99-123468			29/06/2022​	01/11/2022	Exported	01.11.22
Company A	€ 5,262.19	$ 5,525.30	1.050000095​	99-123469			30/06/2022​	01/11/2022	Exported	01.11.22
Company A	€ 1,974.73	$ 2,108.02	1.067497835​	99-123470			01/07/2022​	01/11/2022	Exported	01.11.22
Company A Total	€ 36,184.60	$ 38,166.60
Company B	€ 427.50	$ 456.36	1.067508772​	87-654321			29/05/2022​	01/11/2022	Exported	01.11.22
Company B	€ 427.50	$ 456.36	1.067508772​	87-654322			08/06/2022​	01/11/2022	Exported	01.11.22
Company B	€ 427.50	$ 456.36	1.067508772​	87-654325			08/07/2022​	01/11/2022	Exported	01.11.22
Company B Total	€ 1,282.50	$ 1,369.08
Company C	€ 7,212.70	$ 7,573.33		167-85643			24/05/2022​	01/11/2022	Exported	01.11.22
Company C	€ 8,351.45	$ 8,769.02	1.049999701​	167-85644			25/05/2022​	01/11/2022	Exported	01.11.22
Company C	€ 7,212.70	$ 7,573.33		167-85645			03/06/2022​	01/11/2022	Exported	01.11.22
Company C	€ 8,351.45	$ 8,769.02	1.049999701​	167-85646			04/06/2022​	01/11/2022	Exported	01.11.22
Company C	€ 7,212.70	$ 7,573.33		167-85647			13/06/2022​	01/11/2022	Exported	01.11.22
Company C	€ 8,351.45	$ 8,769.02	1.049999701​	167-85648			14/06/2022​	01/11/2022	Exported	01.11.22
Company C	€ 7,212.70	$ 7,573.33		167-85649			23/06/2022​	01/11/2022	Exported	01.11.22
Company C	€ 8,351.45	$ 8,769.02	1.049999701​	167-85650			24/06/2022​	01/11/2022	Exported	01.11.22
Company C	€ 7,212.70	$ 7,573.33		167-85651			03/07/2022​	01/11/2022	Exported	01.11.22
Company C	€ 8,351.45	$ 8,769.02	1.049999701​	167-85652			04/07/2022​	01/11/2022	Exported	01.11.22
Company C Total	€ 77,820.75	$ 81,711.75

 

[Anthony47]

For example:

Sub AllByCompany()
Dim I As Long, AllInv As String
'
Sheets("Sheet1").Select
For I = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Right(Cells(I, 1), 6) = " Total" Then
        Cells(I, "E").Value = Mid(AllInv, 3)
    Else
        AllInv = AllInv & ", " & Cells(I, "E")
    End If
Next I
Range("E1:E" & I).WrapText = False
End Sub

 

[Peter_SSs]

Possibly this?
For the moment I have assumed at least two rows for each company. If it is possible there is only row for a company a small adjustment would be needed.

Sub ListInvoices()
  Dim rA As Range
 
  For Each rA In Range("E2", Range("E" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    rA.Cells(rA.Count + 1).Value = Join(Application.Transpose(rA.Value), ", ")
  Next rA
End Sub

Here is part of my sheet after the code.

AECJohn.xlsm
	A				E
1	Name				Invoice
2	Company A				99-123456
3	Company A				99-123457
4	Company A				99-123458
5	Company A				99-123459
6	Company A				99-123468
7	Company A				99-123469
8	Company A				99-123470
9	Company A Total				99-123456, 99-123457, 99-123458, 99-123459, 99-123468, 99-123469, 99-123470
10	Company B				87-654321
11	Company B				87-654322
12	Company B				87-654325
13	Company B Total				87-654321, 87-654322, 87-654325
14	Company C				167-85643
15	Company C				167-85644
16	Company C				167-85645
17	Company C				167-85646
18	Company C				167-85647
19	Company C				167-85648
20	Company C				167-85649
21	Company C				167-85650
22	Company C				167-85651
23	Company C				167-85652
24	Company C Total				167-85643, 167-85644, 167-85645, 167-85646, 167-85647, 167-85648, 167-85649, 167-85650, 167-85651, 167-85652
Sheet1 (2)