macro to extraxt information

[Bradly]

I have a file entitled "Cases-2010.xls". Within that file are all cases that
I have to work with for the year. There are headings for columns A:M that
read the following: Last Name, First Name, Middle Initial, Social Security #,
Case Status, Case #, Program Code, Program Type, Class of Assistance, STR
Level, Previous Load, Next Review Date, and Next Review Type. I was able to
adapt a macro I had previous assistance with to extract all cases with
Program Code "AF" and paste them one after the other in a file entitled "AF
Cases-2010.xls". The column headings in this file are the same as in the
"Cases-2010" file. The macro works fine.

Here is my problem. I am trying to figure out a way to find and extract all
of the related cases for each AF case client and paste them on a sheet in the
"AF Cases" file entitled "Related Cases". For example, one AF client might
have an AF case and an MA case--I would like to extract the related MA case
from the "Cases-2010" file and paste it (together with the AF case
information, if possible) on the "Related Cases" sheet; another client might
have an AF case with 2 MA cases and an FS case--I would like to extract these
and paste them on the "Related Cases" sheet, etc. The number of related
cases varies with each AF client, and the program codes are not in
alphabetical order for each client. I could resign myself to sorting by
name, copying, and pasting manually, but there are 500 clients with AF cases
and this would take me a while. I am seeking help on how I can do this more
efficiently.

Here is a simplified example of how the "Cases-2010" list might look:

Blow Joe SS#100000000 Case# 123456789 Program Code AF...
Blow Joe SS#100000000 Case# 234567890 Program Code FS...
Blue John SS#200000000 Case# 345678901 Program Code MA...
Blue John SS#200000000 Case# 456789012 Program Code FS...
Blue John SS#200000000 Case# 567890123 Program Code AF...
Blue John SS#200000000 Case# 678901234 Program Code MA...etc.

I am not experienced or advanced enough to figure this out on my own, but I
am learning. Any help would be appreciated.

Let me know if further explanation is needed.

Thanks.

 

[JLGWhiz]

Use the social security number to identify the individual. You could filter
the Cases file and get all related cases by social security number, then
copy to the related file.

 

[Bernie Deitrick]

Bradly,

Personally, I would use another column of formulas to return a known value for those rows that I
want to extract. For example, entered into a cell in row 2

=IF(G2<>"AF",IF(SUMPRODUCT(($D$2:$D$10000=D2)*($G$2:$G$10000="AF"))<>0,"Copy ME!!!",""),"")

Where col G has your Program codes, and col D has the SSNs (I'm assuming those are unique to the
client). Then copy down to match your data set - increase or decrease the 10000 to match your row
usage...

Then use code similar to your current code to extract the lines where the formula returns "Copy
ME!!!" to another worksheet.

HTH,
Bernie
MS Excel MVP