PDA

View Full Version : Copying Column A to Column E and removing the 4th cell of each value



goldbeje
09-18-2012, 09:52 AM
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
09-18-2012, 11:27 AM
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

goldbeje
09-18-2012, 11:44 AM
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
09-18-2012, 12:43 PM
post a sample workbook?

goldbeje
09-18-2012, 12:51 PM
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
09-18-2012, 12:55 PM
offset(1,0) not (0,1) sorry!!!

goldbeje
09-18-2012, 01:11 PM
Works perfectly!! Thanks a bunch CatDaddy!

J.

CatDaddy
09-18-2012, 01:20 PM
no worries bud!