Hello All,
I work at a test lab and i have over 500,000 test records. I need to remove the duplicate record rows. The file has this layout. A patient can have multiple test and on rare cases have a different case number.
<tbody>
</tbody>
I want to create a macro that will give me this result.
I have the following.
This code only gives me the following records.
<tbody>
</tbody>
<tbody>
</tbody>
Thanks in advance.
I work at a test lab and i have over 500,000 test records. I need to remove the duplicate record rows. The file has this layout. A patient can have multiple test and on rare cases have a different case number.
Case Number | Patient Name | Test | Patient ID |
1011 | John Smith | ABC123 | 5555 |
1011 | John Smith | ABC123 | 5555 |
1014 | Jane Doe | YHF162 | 5512 |
1014 | Jane Doe | BRAC21 | 5512 |
1032 | Jane Doe | JFEH12 | 5512 |
1014 | Jane Doe | YHF162 | 5512 |
1014 | Jane Doe | BRAC21 | 5512 |
Case Number | Patient Name | Test | Patient ID |
1011 | John Smith | ABC123 | 5555 |
1014 | Jane Doe | YHF162 | 5512 |
1014 | Jane Doe | BRAC32 | 5512 |
1032 | Jane Doe | JFEH12 | 5512 |
<tbody>
</tbody>
I want to create a macro that will give me this result.
I have the following.
Code:
Sub SameLine ()
Dim x as long
Dim y as long
Dim Test as long
test = cells.find (what:="Test", LookIn =:xlformulas, lookAt:=xlPart, searchOrder=:xLByRow, SearchDirection:=XlNext, MatchCase:=false_, SearchFormat=:false).column
Dim VarStart as Long
varstart = cells.find (what:="Case Number", LookIn =:xlformulas, lookAt:=xlPart, searchOrder=:xLByRow, SearchDirection:=XlNext, MatchCase:=false_, SearchFormat=:false).column
dim varend as long
varend = 0
do while cells (x,1).value <> ""
y=y +varend +2
rows(x+1).delete
x=x+1
loop
end sub
This code only gives me the following records.
1011 | John Smith | ABC123 | 5555 |
1011 | John Smith | ABC123 | 5555 |
<tbody>
</tbody>
1014 | Jane Doe | YHF162 | 5512 |
<tbody>
</tbody>
Thanks in advance.