How do I alphabetize a column but also have the corresponding cells move with the sort?

[Guest]

I am new to this program and am having difficulty figuring out how to do this. I am using Excel to prepare a vocabulary list. (I already did one and typed the info in alphabetical order to start with. Now I want to type as I go and alphabetize when I'm done.) Column A-B is the word. Column C is the page number. Column D begins the definition

I figured out how to sort/alphabetize columns A-B. But How do I get the definitions of the words that appear in the corresponding cells to move with the sort result? (I obviously don't want the definitions alphabetized ;

Also, I need to keep a space (a horizontal row) between each definition and the sort is not keeping the spaces

Would appreciate any help.

 

[Bob Phillips]

BtC,

Select all 4 columns and sort by A-B, the rest will stay aligned.

The second part is harder. Without some value in there the blank rows will
get sorted away. You could put the same values in A-B instead of blanks and
hide them by changing the font colour to white. When you then sort, the
blanks should remain.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I am new to this program and am having difficulty figuring out how to do

this. I am using Excel to prepare a vocabulary list. (I already did one
and typed the info in alphabetical order to start with. Now I want to type
as I go and alphabetize when I'm done.) Column A-B is the word. Column C
is the page number. Column D begins the definition.

I figured out how to sort/alphabetize columns A-B. But How do I get the

definitions of the words that appear in the corresponding cells to move with
the sort result? (I obviously don't want the definitions alphabetized

Also, I need to keep a space (a horizontal row) between each definition

and the sort is not keeping the spaces.

 

[Guest]

Thank you

I will try what you suggest for the first part

For the second part maybe I could put lines/borders instead of leaving blank?

 

[Bob Phillips]

You'll have a similar sort problem as the blanks, because it will be sorted
by value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thank you.

I will try what you suggest for the first part.

For the second part maybe I could put lines/borders instead of leaving

blank?

 

[Guest]

Sorry, it isn't working. A/B gets sorted correctly but the definitions are not moving with the sort. (Corresponding word.)

 

[Dave Peterson]

Are you sure you have all the columns selected before you do Data|Sort?

(and I'd dump those extra rows between the rows. I just change the row heights
to double what they need to be. The look double spaced and I can do all my
sorts with no problems.)

 

[Guest]

Thank you for your suggestions. I tried increasing the height of the rows and at first it seemed as this would work out. However when I went to print each definition was still close to the next one. There just was not enough space between each one

As for the sort, There just does not seem to be an option for sorting just column A AND moving the data in adjacent cells with it. As for the condensing of the extra spaces between rows I can always go back later and "un-condense them.

Would I have ben better of creating this vocab. list in another program? I do see a LIST feature but I tried this and it is too complicated for me to fool with at the moment

Here is example of what I am trying to alphabetize

COLUMN A COLUMN C COLUMN D-(whatever it takes to get it all in.

Consciousness pg 141 Awareness of ones self & the environement.

Biological rhythm pg 142 A periodic, more or less regular fluctuation in a biological system;....


Does this help?

 

[Ragdyer]

Always, always, always, always ... select the cells to be sorted *before*
you click <Data> <Sort>.
Never, never, never, never ... allow XL to select the range for you - and
*don't* use the sort icons on the tool bar.

That's the only way you'll be sure your sort includes the entire range that
you intend.

XL may be right 95% of the time ... BUT, who can live with 5% bad guesses ?

AND DAVE ... Remember this one ?

http://tinyurl.com/3gkbq

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Are you sure you have all the columns selected before you do Data|Sort?

(and I'd dump those extra rows between the rows. I just change the row heights
to double what they need to be. The look double spaced and I can do all my
sorts with no problems.)

are not moving with the sort. (Corresponding word.)

 

[Bob Phillips]

No, I don't think you understand what we are saying, you don't need an
option to move adjacent columns. Sorting over multiple columns does that for
you..

Select columns A-D, all of them. Then go Data>Sort, and the dialog box
allows you to pick which column you sort by. You can even have a secondary,
and a tertiary sort key (if required, not mandatory).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thank you for your suggestions. I tried increasing the height of the rows

and at first it seemed as this would work out. However when I went to print
each definition was still close to the next one. There just was not enough
space between each one.

As for the sort, There just does not seem to be an option for sorting just

column A AND moving the data in adjacent cells with it. As for the
condensing of the extra spaces between rows I can always go back later and
"un-condense them."

Would I have ben better of creating this vocab. list in another program?

I do see a LIST feature but I tried this and it is too complicated for me to
fool with at the moment.

Here is example of what I am trying to alphabetize :

COLUMN A COLUMN C COLUMN D-(whatever it takes to get it all in.)

Consciousness pg 141 Awareness of ones self & the environement.

Biological rhythm pg 142 A periodic, more or less regular

fluctuation in a biological system;.....

 

[Ragdyer]

As simple as possible:

Select A1,
Hold down <Shift> and
Navigate to and click in the last data containing cell in your list in
Column D.

This Selects your *entire* list, and it should be displayed black.

While the list is still selected,
<Data> <Sort>,

Column A should be the default sort key,
Click <OK>.

You should now find everything sorted as you wish.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Thank you for your suggestions. I tried increasing the height of the rows

and at first it seemed as this would work out. However when I went to print
each definition was still close to the next one. There just was not enough
space between each one.

As for the sort, There just does not seem to be an option for sorting just

column A AND moving the data in adjacent cells with it. As for the
condensing of the extra spaces between rows I can always go back later and
"un-condense them."

Would I have ben better of creating this vocab. list in another program?

I do see a LIST feature but I tried this and it is too complicated for me to
fool with at the moment.

Here is example of what I am trying to alphabetize :

COLUMN A COLUMN C COLUMN D-(whatever it takes to get it all in.)

Consciousness pg 141 Awareness of ones self & the environement.

Biological rhythm pg 142 A periodic, more or less regular

fluctuation in a biological system;.....

 

[Guest]

Dear Ragdyer

So far that was the best help yet. I selected the range before just like you said and this time the definitions and page numbers appeared "somewhat" correctly

However, some of the definitions are longer than one row in length and I had to continue to the next row for the remainder of the definition. Whe I did the sort all the lines of the definitions that were longer than one row appeard at the end of the sort result

Is there a way to post what my sheet looks like for you to be able to see what is happening

 

[Ragdyer]

You can send it to me.
Cut out cutout from my address.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Dear Ragdyer,

So far that was the best help yet. I selected the range before just like

you said and this time the definitions and page numbers appeared "somewhat"
correctly.

However, some of the definitions are longer than one row in length and I

had to continue to the next row for the remainder of the definition. Whe I
did the sort all the lines of the definitions that were longer than one row
appeard at the end of the sort result.

 

[Debra Dalgleish]

Since nothing is entered in column B, delete it, and widen column A
to show the entire word.
Delete the blank rows and increase the row height,
as others have suggested.
Add headings in the first row, and format the headings differently
than the other rows (e.g. Bold font)
Select a cell in the list
To select all the cells in the list, hold the Ctrl key, and press
the * key on the number keypad
(if you don't have a number keypad, hold Ctrl+Shift, press the 8 key)
Choose Data>Sort
From the first dropdown, select the column A heading
Under 'My list has', choose 'Header row', click OK

 

[Guest]

Alpha waves pg 153 Waves your brain emits in bursts when you first climb into bed & relax.
Biological rhythm pg 142 A periodic, more or less regular fluctuation in a biological system;
Circadian rhythm pg 142 A biological rhythm with a period (from peak to peak or trough to
Consciousness pg 141 Awareness of ones self & the environement.
Delta waves pg 154 Occurs in stage 3 sleep. In addition to the waves in stage 2, your
desynchronization pg 144 A state in which biological rhythms are not in phase (synchronized)
disorder pg 146 (SAD) A controversial disorder in which a person experiences
Endogenous pg 142 Generated from within rather than by external cues.
Entrainment pg 142 The synchronization of biological rhytms with external cues, such as
Infradian rhythm pg 142 A biological rhythm that occurs less frequently than once a day. Say
Internal
Lucid dreams pg 156 Although most of us are unaware of our bodies or where we are when
Melatonin pg 144 A hormone secreted by the pineal gland; it is involved in the regulation
Narcolepsy pg 152 A sleep disorder involving sudden & unpredictable daytime attacks
Non-REM sleep pg 153 A period of fewer eye movements. Periods divided into shorter
nucleus pg 144 (SCN) Circadian rhythms are controlled by a biological clock or
Premenstrual
Psychoanalytic
Rapid Eye Movement pg 153 (REM) Sleep periods charecterized by eye movement, loss of muscle
Seasonal affective
Sleep apnea pg 152 A disorder in which breathing briefly stops during sleep, causing
Sleep spindles pg 154 Occurs in stage 2 sleep. Your brain emits ocasional short bursts
Suprachiasmatic
Syndrome pg 147 A vague cluster of physical & emotional symptoms in the days
theory of dreams pg 156/178 The psychoanlytic explanations of dreams is that they allow us to
Ultradian rhythm pg 143 Occurs more often than once a day. Frequently or roughly a 90 min.

(a biological clock in our brains governs the waxing & waning of
hormone levels, urine volume, blood pressure & even the
responsiveness of brain cells to stimulation.) may or may not have
psychological implications. But many of these rhythms continue to
occur even in the absence of external time cues. (See endogenous)

fluctuations of daylight, clock time & temperature.


trough) of about 24 hrs. Best known circadian rhythm is the sleep
wake cycle, but there are hundreds of others that affect physiology &
performance such as body temp. Not always perfectly regular & can
be affected by illness, stress, fatigue, etc.)

once a month or once a season. (For example the female menstrual
cycle.)

cycle. The best studied ultradian rhythm occurs during sleep. But
many other physiological responses & behaviors follow an ultradian
pattern when social customs do not intervene. These include
contractions, hormone levels, susceptibility to to visual illusions,
verbal & spatial performance, brain wave responses during cognitive
tasks, alertness & daydreaming.
overall coordinator, located in a tiny tear-drop shaped cluster of cells
in the hypothalamus called the SCN for short. Neural pathways
from special receptors in the back of the eye transmit info. to the
SCN & allow it to respond to changes in light & dark. The SCN then
sends out messages that caus the brain & body to adapt to these

 

[Guest]

Thank you Debra I will give this a try. Also, Ragdyer I copied the item here for everyone to see befor I read your kind offer

I will get back to you all with results soon.

 

[Tom Ogilvy]

You should have your definitions all in one cell. Putting them on separate
rows without repeating the name in the same row in column A would cause them
to sort incorrectly. If you want to have multiple rows for a definition,
you need to have a matching value in the key field. As long as you only
sort on one column as the key, the definition's multiple rows should stay
adjacent (given you have repeated the key value in the key field for each
row).

--
Regards,
Tom Ogilvy

Dear Ragdyer,

So far that was the best help yet. I selected the range before just like

you said and this time the definitions and page numbers appeared "somewhat"
correctly.

However, some of the definitions are longer than one row in length and I

had to continue to the next row for the remainder of the definition. Whe I
did the sort all the lines of the definitions that were longer than one row
appeard at the end of the sort result.

 

[Guest]

Oh my this is complicated. Hopefully I'll get this figured out soon and can continue with learning this chapter

Anyway, I did managed to complete some of what Debra suggested (and others previously.

I deleted column B. I deleted the blank rows and increased the row height

Now a little question. When I am told to enter headings in the first row and format them differently than other rows have I not already done this? Column A, Row 1 through whatever down is the word I want to define, wouldn't these be the headings? I already have these in bold. Or do you mean name the Column? Something like "WORDS TO DEFINE." then list the words? I am confused here.

 

[Gord Dibben]

Place the definitions in one row only.

You can format the cells to "Wrap Text" and rows to "Autofit" to keep all data
across columns to one row only.

Gord Dibben Excel MVP

 

[Guest]

Could the problem now be that the definitions do not appear all in one cell or something? The definitions are long in many cases and I typed them in multiple columns and rows. Should the entire definition appear in only one cell?

 

[Tom Ogilvy]

Headers are like this

WORD PAGE DEFINITION
house page 16 A wooden structure for habitation
dog page 27 An animal; member of the genus Canus
dog Domesticated by man since
dog prehistoric times

The above would sort correctly if you selected all the data and used column
A as the key.

--
Regards,
Tom Ogilvy

Oh my this is complicated. Hopefully I'll get this figured out soon and

can continue with learning this chapter.

Anyway, I did managed to complete some of what Debra suggested (and others previously.)

I deleted column B. I deleted the blank rows and increased the row height.

Now a little question. When I am told to enter headings in the first row

and format them differently than other rows have I not already done this?
Column A, Row 1 through whatever down is the word I want to define,
wouldn't these be the headings? I already have these in bold. Or do you mean
name the Column? Something like "WORDS TO DEFINE." then list the words? I
am confused here.