Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing sets of rows
I have a spreadsheet with the following structure
primary row 1 related row 1.1 related row 1.2 related row 1.3 ... and so on primary row 2 related row 2.1 related row 2.2 primary row 3 ... that is, some set of "primary rows" that are each followed by related rows that have information related to the primary rows. I'm not sure if that's the optimal structure, but it generally works for me. (I'm open to suggestions for how to improve it.) My problem is when I need to filter on a column. I'd like to be able to filter on a column that has information from one of the rows but instead of limiting the view to only those rows that have that identifier, I want to show all of the rows in the "set" whose primary row satisfied the filter criteria. e.g., if there is an "x" in column G on primary row 2, when I "filter" (or whatever) on column G and look for all x's, I want to show not only primary row 2 but also all of the related rows 2.1 and 2.2. I'm capable of relatively simple macros, but I can't find a good way to do it. All of the related rows have some features that distinguish them from a primary row (column A of the primary rows is not empty - column A of the related rows is empty). So the concept of a "set" of rows is visually obvious, but I don't know how to make "excel" understand the concept. Any suggestions? Using Excel 2004 for MAC, Version 11.3.3. Have not upgraded to newer versions so I don't lose the functionality in my existing macros. Julia Bell |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing sets of rows
If your data is as shown, try custom filter, end with 1.*
Peter "julia" wrote: I have a spreadsheet with the following structure primary row 1 related row 1.1 related row 1.2 related row 1.3 ... and so on primary row 2 related row 2.1 related row 2.2 primary row 3 ... that is, some set of "primary rows" that are each followed by related rows that have information related to the primary rows. I'm not sure if that's the optimal structure, but it generally works for me. (I'm open to suggestions for how to improve it.) My problem is when I need to filter on a column. I'd like to be able to filter on a column that has information from one of the rows but instead of limiting the view to only those rows that have that identifier, I want to show all of the rows in the "set" whose primary row satisfied the filter criteria. e.g., if there is an "x" in column G on primary row 2, when I "filter" (or whatever) on column G and look for all x's, I want to show not only primary row 2 but also all of the related rows 2.1 and 2.2. I'm capable of relatively simple macros, but I can't find a good way to do it. All of the related rows have some features that distinguish them from a primary row (column A of the primary rows is not empty - column A of the related rows is empty). So the concept of a "set" of rows is visually obvious, but I don't know how to make "excel" understand the concept. Any suggestions? Using Excel 2004 for MAC, Version 11.3.3. Have not upgraded to newer versions so I don't lose the functionality in my existing macros. Julia Bell |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing sets of rows
the labels I used in the description were just to facilitate the
description. There is nothing in the actual cell entries that has that structure. On Apr 1, 1:23 am, Billy Liddel wrote: If your data is as shown, try custom filter, end with 1.* Peter "julia" wrote: I have a spreadsheet with the following structure primary row 1 related row 1.1 related row 1.2 related row 1.3 ... and so on primary row 2 related row 2.1 related row 2.2 primary row 3 ... that is, some set of "primary rows" that are each followed by related rows that have information related to the primary rows. I'm not sure if that's the optimal structure, but it generally works for me. (I'm open to suggestions for how to improve it.) My problem is when I need to filter on a column. I'd like to be able to filter on a column that has information from one of the rows but instead of limiting the view to only those rows that have that identifier, I want to show all of the rows in the "set" whose primary row satisfied the filter criteria. e.g., if there is an "x" in column G on primary row 2, when I "filter" (or whatever) on column G and look for all x's, I want to show not only primary row 2 but also all of the related rows 2.1 and 2.2. I'm capable of relatively simple macros, but I can't find a good way to do it. All of the related rows have some features that distinguish them from a primary row (column A of the primary rows is not empty - column A of the related rows is empty). So the concept of a "set" of rows is visually obvious, but I don't know how to make "excel" understand the concept. Any suggestions? Using Excel 2004 for MAC, Version 11.3.3. Have not upgraded to newer versions so I don't lose the functionality in my existing macros. Julia Bell |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing sets of rows
Julia
Perhaps you could insert a column and have index numbers based on that idea 1.0,1.1.. 2.0,2.1..n.0..n.n and use that column to filter. Peter "julia" wrote: the labels I used in the description were just to facilitate the description. There is nothing in the actual cell entries that has that structure. On Apr 1, 1:23 am, Billy Liddel wrote: If your data is as shown, try custom filter, end with 1.* Peter "julia" wrote: I have a spreadsheet with the following structure primary row 1 related row 1.1 related row 1.2 related row 1.3 ... and so on primary row 2 related row 2.1 related row 2.2 primary row 3 ... that is, some set of "primary rows" that are each followed by related rows that have information related to the primary rows. I'm not sure if that's the optimal structure, but it generally works for me. (I'm open to suggestions for how to improve it.) My problem is when I need to filter on a column. I'd like to be able to filter on a column that has information from one of the rows but instead of limiting the view to only those rows that have that identifier, I want to show all of the rows in the "set" whose primary row satisfied the filter criteria. e.g., if there is an "x" in column G on primary row 2, when I "filter" (or whatever) on column G and look for all x's, I want to show not only primary row 2 but also all of the related rows 2.1 and 2.2. I'm capable of relatively simple macros, but I can't find a good way to do it. All of the related rows have some features that distinguish them from a primary row (column A of the primary rows is not empty - column A of the related rows is empty). So the concept of a "set" of rows is visually obvious, but I don't know how to make "excel" understand the concept. Any suggestions? Using Excel 2004 for MAC, Version 11.3.3. Have not upgraded to newer versions so I don't lose the functionality in my existing macros. Julia Bell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping According to Sets of Rows | Excel Worksheet Functions | |||
VLOOKUP in large Data sets of more than 16384 rows | Excel Worksheet Functions | |||
Count rows that match 3 sets of criteria? | Excel Worksheet Functions | |||
Why does the Sort function stop recognizing Header Rows? | Excel Worksheet Functions | |||
How do I set up different column widths for sets or rows? | Excel Discussion (Misc queries) |