logic to find people with multiple visits and move to separate page

[okerix]

Hello,
I am trying to work through the logic of how to do this in Excel. I have a spreadsheet with about 20,000 rows in it. It's full of information about people's visits and the referrals from that visit. There are three main id columns the person_id, form_id(visit's indicated by form_id so one form_id per visit), nomenclature_id(referral issue).

So someone with only one visit will have only one form_id associated to there person_id but they may have more than one row to having multiple referrals(nomenclature_ids). These I want removed or do not need.

In the example data provided the first person_id 1171850 has only one visit.

Someone with more than one visit will have more than one form_id associated with there person_id. If that is the case I want all rows associated to there person_id on a seperate sheet or book.

In the example data provided the last person_id in the data is 1171877 and they have 2 visits.

So I am trying to figure out the logic and what functions can help me make it work. Any help or guidance is appreciated.
Thanks,
Shaun

 

[NBVC]

Try adding a couple of helper columns first to Sheet1,

so in J2 enter formula:

=A2&"_"&F2 copied down. This concatenates the PERSON_ID and FORM_ID fields to get something to compare to

and in K2 enter formula:

=IF(COUNTIF($J$2:$J$2000,J2)=COUNTIF($A$2:$A$2000,A2),"",COUNT(K$1:K1)+1)

This checks if there are equal amounts of combined data as there is PERSON_ID only. If they are equal, then it means only 1 FORM_ID number exists for the PERSON_ID otherwise, it cumulatively counts the rows where the PERSON_ID has multiple FORM_ID's

adjust ranges to suit your whole database and then copy down.

in L2 enter formula:

=MAX(K:K)

This gives us the number of records that need to transfer over.

Now in Sheet2, A2 enter formula to get the relevant records.

=IF(ROWS($A$1:$A1)>Sheet1!$L$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$K:$K)))

copied down as far as you want and across the columns needed.

 

[okerix]

Awesome stuff NBVC! Just learned a whole lot about Access with that one.

You rock!

 

[NBVC]

Awesome stuff NBVC! Just learned a whole lot about Access with that one.

You rock!

You mean you learned about EXCEL, right?

You are welcome, by the way.