Concatenate all the values in cell after each time it is updated

[Jstrom81]

Hi everyone,

I don't think this is possible but thought I would ask here just in case. Here's my problem:

I'm building a repair management system in excel right now where every row represents one repair or one clients repair. One thing we need to constantly update is when and how many times that client is contacted upon completion of the repair. My issue is that right now record each and every time that client is contacted I have separate cells for each time. For example, A1 = March 2, A2 = March 4, A3 = March 10, etc. Is there a way maybe through concatenate function or any other function where I can condense all this info into one cell automatically after I update the cell. For example, if A1 = March 2, then A2 = March 2. as well. If I contact the client again on March 4, then the A1 = March 4, and the A2 now = March2, March 4.

Is this possible? Is there another way to manage this in a more space efficient way?

Please don't hesitate to ask any questions.

Thanks! - Julian

 

[Beyond_avarice]

You're going to have to use VBA for this. I honestly think it easier just to append the new contact date yourself.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Range("A2").Value = Range("A2").Value & "," & Range("A1").Value
    End If

End Sub

 

[Jstrom81]

Yeah I looked into it but we have to build this for multiple locations and VBA isn't an option unfortunately. Thank you anyways

 

[sandy666]

something like this?
with Power Query/Get&Transform

Client	Date		Client	Date
client1	04/03/2020		client1	04/03/2020, 06/03/2020
client2	05/03/2020		client2	05/03/2020, 12/03/2020
client1	06/03/2020		client3	10/03/2020
client3	10/03/2020
client2	12/03/2020

 

[Jstrom81]

Not entirely but close. I want to append every update to another cell basically. The example above, there is one client "Client 1" but it's listed multiple times. I can only have it listed once where every time I update the date cell for "Client 1" it appends that update to another cell and adds to whatever's currently there already. As well I can't use any 3rd add in or software, only standard excel. Are those functions standard?

 

[sandy666]

post representative example of source data and expected result which reflect structure of the tables (with generic data if you want)
you've 365 / 2019 so Power Query is built-in

 

[Jstrom81]

I hope this helps. Every time I update the date in the left table for each cell it adds the date to the date cell in the right table. If the client has already been contacted and there's date already in the cell, it adds afterwards. The goal is to keep record of every time the client is contacted and which day, within one cell. Not sure if that is possible.

 

[sandy666]

date from left table is deleted then new date is inserted.
so I think vba is an option

 

[Jstrom81]

Yes expect I can't use VBA unfortunately.

 

[Akuini]

Try this:
Say the date will be entered in range B2:B100.
This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.
Change 'Range("B2:B100")' to suit.
The Sub Worksheet_Change is triggered whenever you exit a cell after you change its content.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B2:B100")) Is Nothing Then
        
        Application.EnableEvents = False
        If Target.Offset(, 1) = "" Then
            Target.Offset(, 1) = Target.Text
        Else
            Target.Offset(, 1) = Target.Offset(, 1).Text & ", " & Target.Text
        End If
        Application.EnableEvents = True
        
    End If

End Sub

Example:

Book1
	A	B	C
1	CLIENT	DATE	DATE (history)
2	client1	04-Mei	02-Mei, 04-Mei
3	client2	03-Agu	01-Jun, 02-Jul, 03-Agu
4	client3	04-Jun	02-Jun, 04-Jun
5	client5	02-Jun	02-Jun
Sheet1

Note: if your data is in an actual Table (not just a range) then you can change the range to refer to date column of the table.