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

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

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

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