Basic sort question - maintain relationship across columns
 

Excel 2003

The worksheet has two columns only, all text.
I'd like to sort the entire worksheet by column A, alphabetically.
Many of my column A entries have multiple related entries in col. B;
that is, cell A2 has related items in B2, B3, B4. A2, A3, A4 are
blank.
A5 has related items in B5, B6, B7, B8, B9 (the A's are blank)
How do I sort by col. A and retain the association of all of the
related col. B items? So that, if the sort moves A5 up to A1, it also
moves B5, B6, B7, B8, B9 up to A1, A2, etc.?

Thanks,

Mike

Basic sort question - maintain relationship across columns
 

Select all the columns you want included in the sort.
Then Data|Sort and sort by the key that you want.

Personally, I never let excel guess at the range to sort--sometimes it guesses
wrong.

mkemp73 wrote:

Excel 2003

The worksheet has two columns only, all text.
I'd like to sort the entire worksheet by column A, alphabetically.
Many of my column A entries have multiple related entries in col. B;
that is, cell A2 has related items in B2, B3, B4. A2, A3, A4 are
blank.
A5 has related items in B5, B6, B7, B8, B9 (the A's are blank)
How do I sort by col. A and retain the association of all of the
related col. B items? So that, if the sort moves A5 up to A1, it also
moves B5, B6, B7, B8, B9 up to A1, A2, etc.?

Thanks,

Mike

--

Dave Peterson

Basic sort question - maintain relationship across columns
 

when doing sort hight both column A and column B. The select column A as
key. You can also select column b as 2nd key so when there are multiple
items in Column A they will sort so column B is also sorted.

"mkemp73" wrote:

Excel 2003

The worksheet has two columns only, all text.
I'd like to sort the entire worksheet by column A, alphabetically.
Many of my column A entries have multiple related entries in col. B;
that is, cell A2 has related items in B2, B3, B4. A2, A3, A4 are
blank.
A5 has related items in B5, B6, B7, B8, B9 (the A's are blank)
How do I sort by col. A and retain the association of all of the
related col. B items? So that, if the sort moves A5 up to A1, it also
moves B5, B6, B7, B8, B9 up to A1, A2, etc.?

Thanks,

Mike

Basic sort question - maintain relationship across columns
 

On Mar 15, 11:38 am, Dave Peterson wrote:
Select all the columns you want included in the sort.
Then Data|Sort and sort by the key that you want.

Personally, I never let excel guess at the range to sort--sometimes it guesses
wrong.





mkemp73 wrote:

Excel 2003

The worksheet has two columns only, all text.
I'd like to sort the entire worksheet by column A, alphabetically.
Many of my column A entries have multiple related entries in col. B;
that is, cell A2 has related items in B2, B3, B4. A2, A3, A4 are
blank.
A5 has related items in B5, B6, B7, B8, B9 (the A's are blank)
How do I sort by col. A and retain the association of all of the
related col. B items? So that, if the sort moves A5 up to A1, it also
moves B5, B6, B7, B8, B9 up to A1, A2, etc.?

Thanks,

Mike

--

Dave Peterson- Hide quoted text -

- Show quoted text -

When I do that, all of the col B items that have a blank col A next to
them get dropped to the bottom of col. B. For example, I'd like B3 to
remain with A2, but after the sort, A2 moves to A14, but B3 drops to
B48.

Basic sort question - maintain relationship across columns
 

I assume your data looks like this

A 1

2

3

B 1

2

For excel to do the sorting you need both column A and column B to have data
in all rows. I know its easy to read when column A doesn't have all the
entries. You may want to add a new column that have data in all the rows so
the sort works properly. The other choice is to write a Macro.




1) have a column A with all entries

"mkemp73" wrote:

On Mar 15, 11:38 am, Dave Peterson wrote:
Select all the columns you want included in the sort.
Then Data|Sort and sort by the key that you want.

Personally, I never let excel guess at the range to sort--sometimes it guesses
wrong.





mkemp73 wrote:

Excel 2003

The worksheet has two columns only, all text.
I'd like to sort the entire worksheet by column A, alphabetically.
Many of my column A entries have multiple related entries in col. B;
that is, cell A2 has related items in B2, B3, B4. A2, A3, A4 are
blank.
A5 has related items in B5, B6, B7, B8, B9 (the A's are blank)
How do I sort by col. A and retain the association of all of the
related col. B items? So that, if the sort moves A5 up to A1, it also
moves B5, B6, B7, B8, B9 up to A1, A2, etc.?

Thanks,

Mike

--

Dave Peterson- Hide quoted text -

- Show quoted text -

When I do that, all of the col B items that have a blank col A next to
them get dropped to the bottom of col. B. For example, I'd like B3 to
remain with A2, but after the sort, A2 moves to A14, but B3 drops to
B48.

Basic sort question - maintain relationship across columns
 

On Mar 15, 12:16 pm, Joel wrote:
I assume your data looks like this

A 1

2

3

B 1

2

For excel to do the sorting you need both column A and column B to have data
in all rows. I know its easy to read when column A doesn't have all the
entries. You may want to add a new column that have data in all the rows so
the sort works properly. The other choice is to write a Macro.

1) have a column A with all entries



"mkemp73" wrote:
On Mar 15, 11:38 am, Dave Peterson wrote:
Select all the columns you want included in the sort.
Then Data|Sort and sort by the key that you want.

Personally, I never let excel guess at the range to sort--sometimes it guesses
wrong.

mkemp73 wrote:

Excel 2003

The worksheet has two columns only, all text.
I'd like to sort the entire worksheet by column A, alphabetically.
Many of my column A entries have multiple related entries in col. B;
that is, cell A2 has related items in B2, B3, B4. A2, A3, A4 are
blank.
A5 has related items in B5, B6, B7, B8, B9 (the A's are blank)
How do I sort by col. A and retain the association of all of the
related col. B items? So that, if the sort moves A5 up to A1, it also
moves B5, B6, B7, B8, B9 up to A1, A2, etc.?

Thanks,

Mike

--

Dave Peterson- Hide quoted text -

- Show quoted text -

When I do that, all of the col B items that have a blank col A next to
them get dropped to the bottom of col. B. For example, I'd like B3 to
remain with A2, but after the sort, A2 moves to A14, but B3 drops to
B48.- Hide quoted text -

- Show quoted text -

Thanks. I guess I'll have to fill in the blanks. Wouldn't know how to
create a macro like that.

Basic sort question - maintain relationship across columns
 

Thanks. I guess I'll have to fill in the blanks. Wouldn't know how to
create a macro like that.

When you fill in the blanks, you could include conditional formatting
that would hide the display (make the print color the same color as the
background, eg white on white), depending on the relationship of the
"blank" cell to the cell above. So the "blanks" would still appear
blank.

How big is this list (how onerous will it be to "fill in the blanks")?

What do the actual contents of the first few groups look like (to give
an idea of what you could use to "fill in the blanks" and what the
conditional formatting would have to look like)?

If a group relating to A1 is B1,B2,B3,B4, do the cells in B1:B4 have to
stay in order after the sort, or do they simply have to stay with the
contents of A1:A4?