Full Version: Extracting records with more than one id number
NineIron
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.
KingMartin
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:

(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
sdosborne
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
fkegley
Martin, I see you selecting data!
NineIron
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.
KingMartin
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)

(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))
.[E2] = lNumber
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...
fkegley
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.
KingMartin
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
fkegley
Scott, that is a very interesting technique. I think I'll steal it.
sdosborne
Frank,

Not a problem. Enjoy.

By the way, do you know of a different way to accomplish this?

Scott
fkegley
I'll take a look at it. Thanks.
KingMartin
You're welcome.

Have a nice weekend

Martin