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

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of Sheet2 and the sheet containing the cells that you are copying/pasting. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Patriot2879]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of Sheet2 and the sheet containing the cells that you are copying/pasting. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Hi thank you for getting back to me, please find attached a link to the drop box. If you click on the button for week 1 - week 4, you will see the empty cells in sheet1, the data is in sheet2. Duplicates

 

[mumps]

Try this macro for Week 1:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, concatenatedValue As String, desWS As Worksheet
    Set desWS = Sheets("Sheet1")
    v = Range("B2", 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
                    Cells(r + 1, c + 1).ClearContents
                End If
            End If
        Next c
        concatenatedValue = Application.Trim(Join(Application.Index(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

If it works properly, change the column references in the code (in red - three occurrences) to "G" for Week 2, "L" for Week 3 and "Q" for week 4. Re-name each new macro changing the button number to match the week.

 

[Patriot2879]

Try this macro for Week 1:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, concatenatedValue As String, desWS As Worksheet
    Set desWS = Sheets("Sheet1")
    v = Range("B2", 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
                    Cells(r + 1, c + 1).ClearContents
                End If
            End If
        Next c
        concatenatedValue = Application.Trim(Join(Application.Index(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

If it works properly, change the column references in the code (in red - three occurrences) to "G" for Week 2, "L" for Week 3 and "Q" for week 4. Re-name each new macro changing the button number to match the week.

Hiya i have tried the code but nothing happened, it doesnt remove the duplicates in sheet2 or paste any date into sheet1, hope you can still help please.

 

[mumps]

It worked for me. Are you using the macro on the same file that you posted? If not, please post a link to the file that is not working for you.
You would also have to change the column reference (in blue) in the code to "B" for Week 2, "c" for week 3 and "D" for week 4.

desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1) = concatenatedValue

 

[Patriot2879]

It worked for me. Are you using the macro on the same file that you posted? If not, please post a link to the file that is not working for you.
You would also have to change the column reference (in blue) in the code to "B" for Week 2, "c" for week 3 and "D" for week 4.

desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1) = concatenatedValue

Hiya what do you mean macro? Never done one of them before. I deleted all the data in the command button1 and pasted in your code, is this not right?

 

[mumps]

That should work. The word "macro" is used to refer any code that performs an action or series of actions such as the code I suggested. Please answer my question in my previous post.

 

[Patriot2879]

Hiya what do you mean macro? Never done one of them before. I deleted all the data in the command button1 and pasted in your code, is this not right?

Hi I am using the same file to which I posted you

 

[mumps]

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