Removing duplicates from sheet2 and paste data in sheet1, got issues

[Patriot2879]

This code works great with removing the duplicates in Sheet2, and putting what is left into a cell in sheet1, but it does not work if i copy and paste cells from another worksheet into sheet2, different cells are left blank in sheet1. Hope you can help me please? I have this code duplicated alot for different cells to paste into.

 Define variables for each cell value
    Dim valueB As String
    Dim valueC As String
    Dim valueD As String
    Dim valueE As String
    Dim valueF As String
   
    ' Store values from Sheet2, cells A3 to E3 into variables
    With Worksheets("Sheet2")
        valueB = .Range("B2").Value
        valueC = .Range("C2").Value
        valueD = .Range("D2").Value
        valueE = .Range("E2").Value
        valueF = .Range("F2").Value

   
   ' Initialize concatenatedValue as empty string
    Dim concatenatedValue As String
    concatenatedValue = ""
   
    ' Check each value and concatenate with "/" if not empty
    If valueB <> "" Then
        concatenatedValue = valueB
    End If
   
    If valueC <> "" Then
        If concatenatedValue <> "" Then
            concatenatedValue = concatenatedValue & " / " & valueC
        Else
            concatenatedValue = valueC
        End If
    End If
   
    If valueD <> "" Then
        If concatenatedValue <> "" Then
            concatenatedValue = concatenatedValue & " / " & valueD
        Else
            concatenatedValue = valueD
        End If
    End If
   
    If valueE <> "" Then
        If concatenatedValue <> "" Then
            concatenatedValue = concatenatedValue & " / " & valueE
        Else
            concatenatedValue = valueE
        End If
    End If
   
    If valueF <> "" Then
        If concatenatedValue <> "" Then
            concatenatedValue = concatenatedValue & " / " & valueF
        Else
            concatenatedValue = valueF
        End If
    End If
    ' Put the concatenated value into cell A4 on Sheet1
    Worksheets("Sheet1").Range("A2").Value = concatenatedValue

 

[Patriot2879]

Click here to download your file. Give it a try. This is what the Week1 macro looks like:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, concatenatedValue As String, srcWS As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Sheet1")
    Set srcWS = Sheets("Sheet2")
    v = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
    For r = LBound(v) To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) <> "" Then
                If c = UBound(v, 2) Then Exit For
                If v(r, c + 1) = v(r, c) Then
                    srcWS.Cells(r + 1, c + 1).ClearContents
                End If
            End If
        Next c
        concatenatedValue = Application.Trim(Join(Application.Index(srcWS.Range("B" & r + 1).Resize(, 5).Value, 1, 0), "/"))
        Do While InStr(concatenatedValue, "/" & "/")
          concatenatedValue = Replace(concatenatedValue, "/" & "/", "/")
        Loop
        If Left(concatenatedValue, 1) = "/" Then concatenatedValue = Mid(concatenatedValue, 2, 999)
        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1) = concatenatedValue
    Next r
    Application.ScreenUpdating = True
End Sub

Hi this works great thank you, only thing is it is only looking at 4 days in sheet 2, not 5 days for the week, It is going from B to E, not B to F for example in sheet2, thank you for your help

 

[Patriot2879]

Click here to download your file. Give it a try. This is what the Week1 macro looks like:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, concatenatedValue As String, srcWS As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Sheet1")
    Set srcWS = Sheets("Sheet2")
    v = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
    For r = LBound(v) To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) <> "" Then
                If c = UBound(v, 2) Then Exit For
                If v(r, c + 1) = v(r, c) Then
                    srcWS.Cells(r + 1, c + 1).ClearContents
                End If
            End If
        Next c
        concatenatedValue = Application.Trim(Join(Application.Index(srcWS.Range("B" & r + 1).Resize(, 5).Value, 1, 0), "/"))
        Do While InStr(concatenatedValue, "/" & "/")
          concatenatedValue = Replace(concatenatedValue, "/" & "/", "/")
        Loop
        If Left(concatenatedValue, 1) = "/" Then concatenatedValue = Mid(concatenatedValue, 2, 999)
        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1) = concatenatedValue
    Next r
    Application.ScreenUpdating = True
End Sub

This is amazing thank you , is it doing 4 days though instead of 5 working days?

 

[mumps]

you are very welcome. It is actually looking at the fifth day in this line of code:

If v(r, c + 1) = v(r, c) Then

c + 1 = 5 because c=4

 

[Patriot2879]

you are very welcome. It is actually looking at the fifth day in this line of code:

If v(r, c + 1) = v(r, c) Then

c + 1 = 5 because c=4

Oww wow that is amazing how is c=4 please? And if I need to add more rows in sheet2 is that easy to do? Will I need to amend the code? Thank you again and the code is so much smaller now thank you. It took me ages all that copy and pasting then changing each line lol

 

[mumps]

The code will automatically adjust if you add more rows to Sheet2 so amendments to the code are not necessary. In this line of code:

If c = UBound(v, 2) Then Exit For

the value of "Ubound(v,2)" is 5 because it represents the last column in the week. The variable "c" represents the column number that is being examined. So if c=5, the "For" loop is exited making 4 the last column actually examined. I hope that makes sense.

 

[Patriot2879]

The code will automatically adjust if you add more rows to Sheet2 so amendments to the code are not necessary. In this line of code:

If c = UBound(v, 2) Then Exit For

the value of "Ubound(v,2)" is 5 because it represents the last column in the week. The variable "c" represents the column number that is being examined. So if c=5, the "For" loop is exited making 4 the last column actually examined. I hope that makes sense.

Wow that is so clever thank you again for your help it is fantastic thank you

 

[mumps]

You are very welcome.