Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to extraxt information
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to extraxt information
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. "Bradly" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to extraxt information
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 "Bradly" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to change information | Excel Discussion (Misc queries) | |||
Macro to retrieve information | Excel Discussion (Misc queries) | |||
Macro using Combo box information | Excel Discussion (Misc queries) | |||
INFORMATION MESSAGE BY MACRO | Excel Programming | |||
Help with a macro to gather information | Excel Programming |