Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Simple List...Multiple Columns and Multiple Worksheets
I have 3 worksheets (different resources) which all contain 2 to 4 columns
listing the grade level that each of that households child attends. Example Columns: Name, Address, email, Grade (this may have 2-4 columns depending on how many children, one may be in pre-k, one in 2nd and so on) Name, Address, Grade (this could be 2-4 columns) Jones, 777 Oates, (column 1) -pk, (column 2)- 3rd, (column3)- 6th I need to pull from these worksheets by grade. A family may appear in more than one grade list if they have more than one child. What is the best way to pull this data? I tried to filter the columns but when I do it will only give me one column at a time in each worksheet. When I pull from the list say I want all 6th graders it will only give me those in the 3rd column but not column 1 and 2. What am I doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Simple List...Multiple Columns and Multiple Worksheets
Jane
You're not doing anything wrong. The filter is built to work in only one column. Having filtered the data in one column by grade, you can then filter another column by another grade, but the second filter will work with only the data that is visible from the first filter. So, basically, you can't get there from here by filtering. The only way I know that would work for you involves VBA (programming). This might present a problem for you because, in order for VBA to find, say all 3rd grade entries, all those entries would have to be exactly the same. If your data fits this criteria, VBA would work just fine and would do what you want in only seconds. If this sounds like what you want, post back with some more detail about the layout of your data. For instance, what is the maximum number of columns that you have? Also, when VBA finds each instance of the criteria grade, VBA will have to copy some/all of that row and place it somewhere. What do you want copied? Where do you want this data to be placed? Perhaps in another, maybe blank, sheet? Also, you say you have 3 worksheets. Do you want VBA to search all 3 sheets for the criteria grade? If so, do you want the result (the copied rows) separated by sheet or all bunched together? Also, do you want the result sorted in some manner? What manner? HTH Otto "Jane Doe" wrote in message ... I have 3 worksheets (different resources) which all contain 2 to 4 columns listing the grade level that each of that households child attends. Example Columns: Name, Address, email, Grade (this may have 2-4 columns depending on how many children, one may be in pre-k, one in 2nd and so on) Name, Address, Grade (this could be 2-4 columns) Jones, 777 Oates, (column 1) -pk, (column 2)- 3rd, (column3)- 6th I need to pull from these worksheets by grade. A family may appear in more than one grade list if they have more than one child. What is the best way to pull this data? I tried to filter the columns but when I do it will only give me one column at a time in each worksheet. When I pull from the list say I want all 6th graders it will only give me those in the 3rd column but not column 1 and 2. What am I doing wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Simple List...Multiple Columns and Multiple Worksheets
"Otto Moehrbach" wrote: Jane You're not doing anything wrong. The filter is built to work in only one column. Having filtered the data in one column by grade, you can then filter another column by another grade, but the second filter will work with only the data that is visible from the first filter. So, basically, you can't get there from here by filtering. The only way I know that would work for you involves VBA (programming). This might present a problem for you because, in order for VBA to find, say all 3rd grade entries, all those entries would have to be exactly the same. If your data fits this criteria, VBA would work just fine and would do what you want in only seconds. If this sounds like what you want, post back with some more detail about the layout of your data. For instance, what is the maximum number of columns that you have? Also, when VBA finds each instance of the criteria grade, VBA will have to copy some/all of that row and place it somewhere. What do you want copied? Where do you want this data to be placed? Perhaps in another, maybe blank, sheet? Also, you say you have 3 worksheets. Do you want VBA to search all 3 sheets for the criteria grade? If so, do you want the result (the copied rows) separated by sheet or all bunched together? Also, do you want the result sorted in some manner? What manner? HTH Otto "Jane Doe" wrote in message ... I have 3 worksheets (different resources) which all contain 2 to 4 columns listing the grade level that each of that households child attends. Example Columns: Name, Address, email, Grade (this may have 2-4 columns depending on how many children, one may be in pre-k, one in 2nd and so on) Name, Address, Grade (this could be 2-4 columns) Jones, 777 Oates, (column 1) -pk, (column 2)- 3rd, (column3)- 6th I need to pull from these worksheets by grade. A family may appear in more than one grade list if they have more than one child. What is the best way to pull this data? I tried to filter the columns but when I do it will only give me one column at a time in each worksheet. When I pull from the list say I want all 6th graders it will only give me those in the 3rd column but not column 1 and 2. What am I doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Simple List...Multiple Columns and Multiple Worksheets
"Jane Doe" wrote: "Otto Moehrbach" wrote: Jane You're not doing anything wrong. The filter is built to work in only one column. Having filtered the data in one column by grade, you can then filter another column by another grade, but the second filter will work with only the data that is visible from the first filter. So, basically, you can't get there from here by filtering. The only way I know that would work for you involves VBA (programming). This might present a problem for you because, in order for VBA to find, say all 3rd grade entries, all those entries would have to be exactly the same. If your data fits this criteria, VBA would work just fine and would do what you want in only seconds. If this sounds like what you want, post back with some more detail about the layout of your data. For instance, what is the maximum number of columns that you have? Also, when VBA finds each instance of the criteria grade, VBA will have to copy some/all of that row and place it somewhere. What do you want copied? Where do you want this data to be placed? Perhaps in another, maybe blank, sheet? Also, you say you have 3 worksheets. Do you want VBA to search all 3 sheets for the criteria grade? If so, do you want the result (the copied rows) separated by sheet or all bunched together? Also, do you want the result sorted in some manner? What manner? HTH Otto "Jane Doe" wrote in message ... I have 3 worksheets (different resources) which all contain 2 to 4 columns listing the grade level that each of that households child attends. Example Columns: Name, Address, email, Grade (this may have 2-4 columns depending on how many children, one may be in pre-k, one in 2nd and so on) Name, Address, Grade (this could be 2-4 columns) Jones, 777 Oates, (column 1) -pk, (column 2)- 3rd, (column3)- 6th I need to pull from these worksheets by grade. A family may appear in more than one grade list if they have more than one child. What is the best way to pull this data? I tried to filter the columns but when I do it will only give me one column at a time in each worksheet. When I pull from the list say I want all 6th graders it will only give me those in the 3rd column but not column 1 and 2. What am I doing wrong? The maximum number of column in any one spreadsheet is 11. The columns are labled : Parents First Name, Parents Last Name, sStreet Address, City, State, Zip, columns 7-10 list grade for each household child "Child1" = k, Child2=pre-k, Child3=6, Child 4=5 (only goes up to 4 children and no grade greater than 8), column 11 is email address. Listing all information in another worksheet pulling from all sources (3 worksheets in same workbook) is fine. I would want to group by grade of course. Could do sheet for each grade? Will probably begin sorting alphabetical, but may need to change that later. First priority is to list row information by grade. I'm aware that some households will appear in more than one grade goup. Thank you for your help...greatly appreciated! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Simple List...Multiple Columns and Multiple Worksheets
Sorry Otto,
Not a veteran at posting in forums. Expected that when I replied it would seperate our text. Answer to your response questions... The maximum number of column in any one spreadsheet is 11. The columns are labled : Parents First Name, Parents Last Name, sStreet Address, City, State, Zip, columns 7-10 list grade for each household child "Child1" = k, Child2=pre-k, Child3=6, Child 4=5 (only goes up to 4 children and no grade greater than 8), column 11 is email address. Listing all information in another worksheet pulling from all sources (3 worksheets in same workbook) is fine. I would want to group by grade of course. Could do sheet for each grade? Will probably begin sorting alphabetical, but may need to change that later. First priority is to list row information by grade. I'm aware that some households will appear in more than one grade goup. Thank you for your help...greatly appreciated! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Simple List...Multiple Columns and Multiple Worksheets
Otto, Thought I might mention that if there are only 2 children, there will be 2 columns blank...child1=pk, child2=7, child3=blank, child4=blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Same Formula, multiple columns, worksheets | Excel Worksheet Functions | |||
replicate columns across multiple worksheets | Excel Worksheet Functions | |||
Create multiple worksheets from list | Excel Discussion (Misc queries) | |||
linking multiple columns onto multiple worksheets | Excel Discussion (Misc queries) | |||
Simple Way to Count the Number of Duplicate Dates on Multiple Worksheets | Excel Discussion (Misc queries) |