My data contains discharges from a hospital. I need to extract records with the same Medical Record Number, i.e. count > 1, but these records have different discharge dates.
Tried to use the Advanced Filter wizard, but I couldn't figure it out.
Full Version: Extracting records with more than one id number
Hello,
what do you mean by 'extract'? On a first glance, this looks as a task for Autofilter...
If you want to use Advanced Filter to copy the filtered rows, try this:
(1) Select the data including column Headers (e.g. A1:C10, A1:C1 are the headers, "Header1", "Header2", etc)
(2) Somewhere in your sheet, e.g. in E1 put "Header1" (or whatever the header is, e.g. "Medical Record Number")
(3) In E2 enter the criterion, e.g. 1111 (the filtered number)
(4) Check "Copy to another location", select e.g. G1 in the same sheet, OK
HTH,
Martin
what do you mean by 'extract'? On a first glance, this looks as a task for Autofilter...
If you want to use Advanced Filter to copy the filtered rows, try this:
(1) Select the data including column Headers (e.g. A1:C10, A1:C1 are the headers, "Header1", "Header2", etc)
(2) Somewhere in your sheet, e.g. in E1 put "Header1" (or whatever the header is, e.g. "Medical Record Number")
(3) In E2 enter the criterion, e.g. 1111 (the filtered number)
(4) Check "Copy to another location", select e.g. G1 in the same sheet, OK
HTH,
Martin
Let me make sure I understand what what you are trying to do. You have a listing of XX,XXX encounters but only want to work with those encounters where the patient has multiple visits within your given time frame. Is that correct?
If so, sort your encounters by MR #, ascending. Place this formula in the right most empty column, row 2 (assumes row 1 is column headings, row 2 begins data, and that the MR# is in column A):
=IF(A2=A3,1,IF(A2=A1,1,""))
This formula compares the MR# on a given row to the MR#'s on the row above and the row below. If it is a match in either case, it marks the encounter with a 1. If there is no match, it doesn't mark the encounter with anything.
Copy this formula down to all rows containing data.
Copy, paste special, values on that entire column.
Sort on this new column (ascending) and secondarily on the MR#, and 3rd on the encounter # or admit date, or discharge date, or, well, you get the picture.
Now all of the patients who were seen multiple times are listed together, first. Then, all of the patients who were only admitted once are listed.
I hope this helps.
Scott
If so, sort your encounters by MR #, ascending. Place this formula in the right most empty column, row 2 (assumes row 1 is column headings, row 2 begins data, and that the MR# is in column A):
=IF(A2=A3,1,IF(A2=A1,1,""))
This formula compares the MR# on a given row to the MR#'s on the row above and the row below. If it is a match in either case, it marks the encounter with a 1. If there is no match, it doesn't mark the encounter with anything.
Copy this formula down to all rows containing data.
Copy, paste special, values on that entire column.
Sort on this new column (ascending) and secondarily on the MR#, and 3rd on the encounter # or admit date, or discharge date, or, well, you get the picture.
Now all of the patients who were seen multiple times are listed together, first. Then, all of the patients who were only admitted once are listed.
I hope this helps.
Scott
Martin, I see you selecting data!
Scott,
This is Scott. That's exactly what I want. I'll let you know how it turns out.
King,
Sorry, I probably didn't explain myself very well.
This is Scott. That's exactly what I want. I'll let you know how it turns out.
King,
Sorry, I probably didn't explain myself very well.
Hey Frank,
you gotta be kiddin'? 
OK, first of all, I've mocked up my instructions. Here is a correct order:
(1) Somewhere in your sheet, e.g. in E1 put "Header1" (or whatever the header is, e.g. "Medical Record Number")
(2) In E2 enter the criterion, e.g. 1111 (the filtered number)
(3) GoTo Data=>Advanced Filter
(4) Click into 'List Range', Select A1:C10
(5) Click into 'Criteria range', Select E1:E2
(6) Check "Copy to another location", select 'Copy To' range, e.g. G1 in the same sheet
(7) Hammer down on Enter
Now,
of course I select cells as the user interface requires you to do so. (In fact, you could type in the addresses, but why not use the offered refEdit control?)
In VBA, I would never-ever-never select any cells:
Best regards,
Martin
P.S. Seeing the OPs response, I was off target anyway...
you gotta be kiddin'?
OK, first of all, I've mocked up my instructions. Here is a correct order:
(1) Somewhere in your sheet, e.g. in E1 put "Header1" (or whatever the header is, e.g. "Medical Record Number")
(2) In E2 enter the criterion, e.g. 1111 (the filtered number)
(3) GoTo Data=>Advanced Filter
(4) Click into 'List Range', Select A1:C10
(5) Click into 'Criteria range', Select E1:E2
(6) Check "Copy to another location", select 'Copy To' range, e.g. G1 in the same sheet
(7) Hammer down on Enter
Now,
of course I select cells as the user interface requires you to do so. (In fact, you could type in the addresses, but why not use the offered refEdit control?)
In VBA, I would never-ever-never select any cells:
CODE
Sub Filter1111()
Dim rg As Range
Dim lNumber As Long
'
Let lNumber = 1111
With Sheets("Sheet1")
Set rg = .Range("A1", .[c65536].End(3))
.[E1] = "Header1"
.[E2] = lNumber
rg.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.[E1:E2], _
CopyToRange:=.[G1], _
Unique:=False
End With
End Sub
Dim rg As Range
Dim lNumber As Long
'
Let lNumber = 1111
With Sheets("Sheet1")
Set rg = .Range("A1", .[c65536].End(3))
.[E1] = "Header1"
.[E2] = lNumber
rg.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.[E1:E2], _
CopyToRange:=.[G1], _
Unique:=False
End With
End Sub
Best regards,
Martin
P.S. Seeing the OPs response, I was off target anyway...
I couldn't resist, Martin, of course, I know you select cells in the spreadsheet. I suspect your code does too, once it is translated into object code.
QUOTE
I suspect your code does too, once it is translated into object code.
No, it doesn't Frank, believe me 
I attached a sample file. Select any cell you want, run the code.
You'll notice that the selection didn't change => nothing was selected by the code.
regards,
Martin
Scott, that is a very interesting technique. I think I'll steal it.
Frank,
Not a problem. Enjoy.
By the way, do you know of a different way to accomplish this?
Scott
Not a problem. Enjoy.
By the way, do you know of a different way to accomplish this?
Scott
I'll take a look at it. Thanks.
You're welcome.
Have a nice weekend
Martin
Have a nice weekend
Martin
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.