Keeping track of tests components VBA

L

Lenna

Active Member
Joined
Jun 25, 2014
Messages
269
I need help with keeping track of how many components are reported with specific tests.
Here is the logic that I would like to implement with VBA code and insert into my macro. Any help with this would be greatly appreciated.

Thanks,

Lenna

Starting in row 1:
If value in O is either “PRAMO” or “HLAS” and L is not blank
Then
Compare the current row with the row beneath at A, H, N, L and O
If all match,
Return in R (combine value in O & “both”)
Move two rows down and repeat.
If A,H,N,L and O do not match
Return in R (combine value in O & “one”)
Move to the next row

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
Patient Id
MR Number
Last Name
First Name
Category
Included In Mailing
Log Time
Sample Date
Assigned Date
Test Type
Test Id
ReportDate
Days2SignOff
Order Number
SoftTestCodes
Repeats
Reportable Comments
CombinedCodes
11
1212212
Smith
Bob
KIDNEY
FALSE
9/10/2014
9/10/2014
9/10/2014
SABI
604562
9/12/2014
2
211003156
HLAS
None
HLASBoth
11
3.3E+07
Smith
Bob
KIDNEY
FALSE
9/10/2014
9/10/2014
9/10/2014
SABII
604563
9/12/2014
2
211003156
HLAS
None
HLASBoth
22
1.3E+07
Jones
Jenn
KIDNEY
FALSE
9/11/2014
9/10/2014
9/12/2014
SABI
604623
9/12/2014
0
211007028
PRAMO
None
PRAMOone
22
1.3E+07
Jones
Jenn
KIDNEY
FALSE
9/11/2014
9/10/2014
9/12/2014
SABII
604624
9/13/2014
0
211007028
PRAMO
None
PRAMOone
33
3.6E+07
Brian
JASON
KIDNEY
TRUE
9/10/2014
9/8/2014
9/12/2014
SABI
604495
9/12/2014
0
211004776
PRAMO
None
PRAMOboth
33
3.6E+07
Brian
JASON
KIDNEY
TRUE
9/10/2014
9/8/2014
9/12/2014
SABII
604496
9/12/2014
0
211004776
PRAMO
None
PRAMOboth
44
1E+08
Ceder
PATRICK
KIDNEY
TRUE
9/11/2014
9/10/2014
9/12/2014
SABI
604621
9/12/2014
0
211004686
PRAMO
None
PRAMOboth
44
1E+08
Ceder
PATRICK
KIDNEY
TRUE
9/11/2014
9/10/2014
9/12/2014
SABII_RPT
604622
9/12/2014
0
211004686
PRAMO
None
PRAMOboth
44
1E+08
Ceder
PATRICK
KIDNEY
TRUE
9/11/2014
9/10/2014
9/12/2014
SABI_RPT
604621
0
211004686
PRAMO
None

44
1E+08
Ceder
PATRICK
KIDNEY
TRUE
9/11/2014
9/10/2014
9/12/2014
SABII
604622
0
211004686
PRAMO
None

55
1E+08
Spear
ANTHONY
KIDNEY
FALSE
9/11/2014
9/10/2014
9/12/2014
LSM__MICA
604649
9/12/2014
0
211007470
HLASM
None

55
1E+08
Spear
ANTHONY
KIDNEY
FALSE
9/11/2014
9/10/2014
9/12/2014
LSMI
604650
9/12/2014
0
211007470
HLASM
None


<tbody>
</tbody>
 
Sort by date Sort by votes
Here is what is return by your latest code: only correct orders are marked.

Patient Id
MR Number
Last Name
First Name
Category
Included In Mailing
Log Time
Sample Date
Assigned Date
Test Type
Test Id
ReportDate
Days2SignOff
Order Number
SoftTestCodes
Repeats
comments
68767
36646251
Smith
BRENDA
KIDNEY REC
FALSE
6/30/2014
6/30/2014
7/1/2014
SABI
593827
7/21/2014
183002181
HLAS
correct
HLASboth
68767
36646251
Smith
BRENDA
KIDNEY REC
FALSE
6/30/2014
6/30/2014
7/1/2014
SABII
593828
7/21/2014
183002181
HLAS
correct
HLASboth
87363
16707223
Smith
MICHAEL
KIDNEY REC
TRUE
6/27/2014
6/18/2014
7/1/2014
FL__PRAI
593542
7/9/2014
182606036
HLASM
None
87363
16707223
Smith
MICHAEL
KIDNEY REC
TRUE
6/27/2014
6/18/2014
7/1/2014
FL__PRAII
593543
7/9/2014
182606036
HLASM
None
106609
1E+08
Smith
JACOB
KIDNEY/KPD
TRUE
6/27/2014
6/16/2014
7/1/2014
SABI
593561
7/9/2014
182606219
PRAMO
mistake
108398
1E+08
Smith
ROBIN
HCT Patient
FALSE
6/30/2014
6/30/2014
7/1/2014
SABII
593829
7/9/2014
183001584
PRAMO
mistake
108569
1E+08
Smith
JACOB
KIDNEY/KPD
TRUE
6/27/2014
6/16/2014
7/1/2014
HLAS
593562
7/10/2014
182606219
HLAS
mistake
106290
1E+08
Smith
DENNIS
KIDNEY REC
TRUE
6/27/2014
6/16/2014
7/1/2014
SABI
593564
7/9/2014
182606245
HLAS
correct
HLASboth
106290
1E+08
Smith
DENNIS
KIDNEY REC
TRUE
6/27/2014
6/16/2014
7/1/2014
SABII
593565
7/9/2014
182606245
HLAS
correct
HLASboth
106290
38649383
Smith
MING
KIDNEY REC
TRUE
6/27/2014
6/26/2014
7/1/2014
FL__PRAI
593757
7/9/2014
182705747
HLASM
None

<tbody>
</tbody>
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Lenna said:
Normally, there should be always two rows for each HLAS/PRAMO. Sometimes there is only one row for each patient Id with PRAMO/HLAS in O and L is not blank. These are mistakes made by people who ordered and reported the test. We are trying to track and correct these mistakes. So, if there is only one row with PRAMO/HLAS in O and L is not blank it should return the corresponding value from O & "one" not a blank. I hope this makes sense and sorry for the confusion.

I can provide more examples/ further explanations if needed.

Thank you again,

Lenna
Click to expand...

Then the last code that I posted in post number 9 should do what you want.
 
Upvote 0
Also if there are three mistake for same patient ID(108569), only the first two are marked. Sample dates are different for all three rows with PatientID(108569).

Patient IdMR NumberLast NameFirst NameCategoryIncluded In MailingLog TimeSample DateAssigned DateTest TypeTest IdReportDateDays2SignOffOrder NumberSoftTestCodesRepeatscommentsResult
6876736646251SmithBRENDAKIDNEY RECFALSE6/30/20146/30/20147/1/2014SABI5938277/21/2014183002181HLAScorrectHLASboth
6876736646251SmithBRENDAKIDNEY RECFALSE6/30/20146/30/20147/1/2014SABII5938287/21/2014183002181HLAScorrectHLASboth
106609100230251SmithJACOBKIDNEY/KPDTRUE6/27/20146/16/20147/1/2014SABI5935617/9/2014182606219PRAMOmistake
108398100282805SmithROBINHCT PatientFALSE6/30/20146/30/20147/1/2014SABII5938297/9/2014183001584PRAMOmistake
108569100230251SmithJACOBKIDNEY/KPDTRUE6/27/20146/16/20147/1/2014HLAS5935627/10/2014182606219HLASmistakeHLASone
108569100230251SmithJACOBKIDNEY/KPDTRUE6/27/20146/20/20147/1/2014HLAS5935627/10/2014182606219HLASmistakeHLASone
108569100230251SmithJACOBKIDNEY/KPDTRUE6/27/20146/21/20147/1/2014HLAS5935627/10/2014182606219HLASmistake
106290100227680SmithDENNISKIDNEY RECTRUE6/27/20146/16/20147/1/2014SABI5935647/9/2014182606245HLAScorrectHLASboth
106290100227680SmithDENNISKIDNEY RECTRUE6/27/20146/16/20147/1/2014SABII5935657/9/2014182606245HLAScorrectHLASboth

<tbody>
</tbody>
 
Last edited:
Upvote 0
Sorry, I've missed you latest post.

Code from #9 works great!

Thank you very much.

Lenna
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
299
awoohaw
awoohaw
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
216
NoSparks
N
F
  • Question
Graph Help
Replies
2
Views
164
Fegal
F
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
149
XLfriend777
XLfriend777
Chris_010101
  • Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K

Forum statistics

Threads
1,214,516
Messages
6,119,980
Members
448,934
Latest member
audette89

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top