Copying Column A to Column E and removing the 4th cell of each value

[goldbeje]

I have a macro that currently is formatting data from Sheet(1) to Sheet(2). I am running into an issue with getting data to copy in triplicates from column A (saved in quadruplicates) to Column E (copied in triplicates). Does anyone know how to remove/truncate that fourth cell of each value when copying to column E?

xlUp would be needed as the rows are never the same count.
Each column's row starts in the 2nd cell as there is a header row.

i.e.
"A"
PCR1
PCR1
PCR1
PCR1
PCR2
PCR2
PCR2
PCR2
PCR3
PCR3
PCR3
PCR3

"E"
PCR1
PCR1
PCR1
PCR2
PCR2
PCR2
PCR3
PCR3
PCR3

Thanks for looking!

J.

[CatDaddy]

[VBA]Dim cell as Range
Dim r as Long
r=0
For each cell in Range("A2:A" & Range("A" & Rows.count).End(xlup).row)
If cell.Value <> cell.offset(0,1).Value Then
Range("E" & 2+r & ":E" & 5 + r).Value = cell.value
r = r+3
End if
Next cell[/VBA]

[goldbeje]

Thanks for your response CatDaddy.. When I included this into my code it did the opposite of what I wanted. It tripled each set into Column E, instead of removing the fourth value. I am looking to get that fourth value truncated.

Basically taking that fourth value for each group of the same value and removing it and then saving the group of three into column E, as listed in the original post.

Below is the result of your code inclusion:

A
PCR1
PCR1
PCR1
PCR1
PCR2
PCR2
PCR2
PCR2

E
DNA1
DNA1
DNA1
DNA1
DNA1
DNA1
DNA1
DNA1
DNA1
DNA1
DNA1
DNA1
DNA2
DNA2
DNA2
DNA2
DNA2
DNA2
DNA2
DNA2
DNA2
DNA2
DNA2
DNA2

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value <> cell.Offset(0, 1).Value Then
        Range("E" & 2 + m & ":E" & 5 + m).Value = cell.Value
        m = m + 3
    End If
Next cell
 
 
 
    'Parse the first 3 characters off column E cells
    For Each Whole In Range(Sheets("Sheet2").Range("E2"), Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp))
    Whole = Right(Whole, Len(Whole) - 3)
        Next
 
    'Align column E to the Right
    Sheets("Sheet2").Range("E1:E999").HorizontalAlignment = xlRight
    'Insert "DNA" to the front of Column E cells
    For Each DNACopy In Range(Sheets("Sheet2").Range("E1"), Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp))
    If DNACopy.Value <> "" Then DNACopy.Value = "DNA" & DNACopy.Value
        Next

[CatDaddy]

post a sample workbook?

[goldbeje]

I have attached the workbook. Macro "CopyPaste" needs to be ran and it will generate Sheet(2) and format. When this happens, column E needs to show each value (DNA1, DNA2, DNA3, and so on until last "Source_ID" listed) in triplicates. I figured that using the first column would be the easiest method and truncating the first 3 letters and adding DNA.

You will be able to see what I am talking about when you run the macro.

If there is an easier way of accomplishing this, please help!

Thanks.

[CatDaddy]

offset(1,0) not (0,1) sorry!!!

[goldbeje]

Works perfectly!! Thanks a bunch CatDaddy!

J.

[CatDaddy]

no worries bud!