Finding cells that contain a value from another set of cells

[slpscientist]

I've seen a number of posts on finding duplicates, but this is a little more complex.
I have two spreadsheets.
In Spreadsheet #1, each row represents a research subject. The cells in column A each have a subject number and there are no duplicates. The cells in the other columns each contain demographic information for that subject.
In Spreadsheet #2, each row represents data for a subject at a given testing time. So, there may be three rows for some subjects (e.g. subjects who were tested at 12, 24, and 36 months) and there may only be one row for another subject (e.g. a subject who was only tested at 36 months). There also are more subjects in that spreadsheet than in Spreadsheet #1 (all subjects in spreadsheet #1 are in Spreadsheet #2, but not vice versa). So, in Spreadsheet #2, column A also has subject numbers, but many are duplicates. I'd like to select only those rows in Spreadsheet #2 that have a value in column A that is also in column A of Spreadsheet #1. So, that would select every row representing a testing session for a subject in Spreadsheet #1.
This would be the first step. What I actually need to do is take each individual row from Spreadsheet #1 and add it to the appropriate row(s) for spreadsheet #2. This could allow every testing session for the specified set of research subjects to contain their demographic data and their data from the specific testing session.
Is that clear as mud?
Thank you in advance -

 

[stnkynts]

Just to see if I understand:

1. Worksheet 1 is a self entry data sheet. Worksheet 2 is the "Master" sheet that holds all the data (tests) on the subjects. Each row in Column A is the research subjects number (in both sheets).

2. As data is entered into Worksheet 1 you want it to find the matching subject number in Worksheet 2 Column A (by using a macro).

3. After it has found the matching number, you want it to add the data in Worksheet 1 to the last column in Worksheet 2 (same row).

Is this correct?

 

[nikik]

Perhaps the countif function would work. I use it for finding duplicates. Try going to the first blank column in sheet 2, starting at Row A, and type:

=countif(Sheet1!A:A,Sheet2!B1)

Then you can sort by that formorly blank column and all the 2's, 3's, etc are on your Sheet 1.

Not sure if I read your post right, but hope it helps!

 

[slpscientist]

Nikik - that didn't quite work, but it gave me what I needed to figure it out. Thanks! I just had to change B1 to A:A, so the formula read: =COUNTIF(Sheet1!A:A,Sheet2!A:A)

Stnkynts - You are essentially right on all three counts counts. Sheet 2 is the "Master" for the entire study and Sheet 1 is for a smaller project using that data, along with data that is manually entered. Because the studies are longitudinal, both Sheets are updated from time to time.
With Nikik's answer, I was able to identify all rows in Sheet 2 belonging to the subgroup in Sheet 1. If you have an idea on how to now to take the rows from Sheet 1 and combine them with the appropriate rows in Sheet 2, I'd love to hear it. It isn't totally straight forward (to me) given that most subjects will have multiple rows in Sheet 2.

I appreciate the help!

 

[nikik]

See, I did read it wrong - also you can do it the other way by putting a new column in sheet 1 like:

=COUNTIF(Sheet2!A:A,Sheet1!A1)

Then all the 2's, 3's, etc in sheet one will tell you how many of that unique entry are in sheet two.

Also, you could always do the good old fashioned =countif(Sheet2!A:A, "insertwordhere") and it'll tell you how many of that word are in Column A, Sheet 2.

Glad I could sorta help! Usually I'm the helpee, not the helper!