Joining data from two worksheets into one or displaying data from one in the other

I have Excel Mac 2011 and also Excel Win 2003. I need to produce a worksheet for others to use in Win 2003. My problem is:

I have workbook with two worksheets:
Worksheet 1 has id numbers corresponding to people names and other details. Each person and id is listed only once as the id identifies that person.

Worksheet 2 has more information about these people but doesn't have their names, just their id numbers. In some cases, however, there are multiple records for the same person.

I need to combine the two worksheets so that it shows all the events for each person on the one sheet. I don't want to have to go through and make new lines for multiple entries and cut and paste as I have about 13000 id's.

Is there a way to easily combine the two so that all entries for each person are listed together OR alternatively that if you click on the id or name on the first worksheet it displays just those entries that match the id on the second worksheet?

I could do this by transferring the data to MS Access and using a subform but most of the intended users don't have MS Access so I really need to stick with Excel. That's also why I need it to work in Excel 2003 - regardless of what I build it in.

I'd be grateful for any help.

thanks,

roger

Thank you for the tip - I though my explanation had been clear enough - obviously a newby mistake. I've attached 30 records from each of the two worksheets though, as I said before, there are some thousands of records however all basically contain the same information as in the sample it's just that some patients have 8 or 9 episodes of care and some only one.

roger

Dear Ben,

Thank you so much - you're a whizz - don't know what you did but this is exactly what I need. Can you explain how you make this happen either on the thread, or if permissible, directly to me at roger@hawcroft.net. I don't want to break any thread rules.

Thanks again

This will save me hours of work and provide the users with exactly what they need.

My sincere thanks for your time, patience and expertise.

roger

Thanks again, Ben - I've never done any coding so this is all new to me but your explanation is very clear and inspires me to do some learning in the area. How would I go about putting headers for 'Admission Date', 'Discharge Date', 'Condition' on that form, for instance?

roger

Originally Posted by protonLeah

The button "View Summary" on the Patients worksheet calls a macro "Button1_Click()"

Please Login or Register  to view this content.

This macro checks to see if the active cell in within the data table on Patients sheet. If so, it retrieves the patient id from column A and uses that id number toloop through the records on the "Episodes of Care" sheet. Each time the id number is found, the macro adds the line to the listbox. When all the records have been tested, it displays the form with a "Close" button.

To see the form and the code, right click either of the sheet tabs and select View Code.
In the VBAProject Window, click PatientSummary under the Forms folder.
Click Module1 to view or edit the button1_click macro.