Thread: EXCEL 07 - Subordinate cells

Fill in the gaps in column 1...

HTH

Originally Posted by Cheeky Charlie

Fill in the gaps in column 1...

HTH

Fill them with what?

I thought of that, but I wasn't sure what to put in, nor did I want to clutter the HEADWORD column.

But I decided to add another column before the headword column, and copy all the headwords into that column, including the blanks. And then to fill in the blanks by adding the headword plus a number for each extra definition.

So I'd have:

1 | COLUMN A......|.....COLUMN B........|.......COLUMN C
2 | HIDDEN............|.....HEADWORD......|.......DEFINITION
3 | PICCIOTTU.......|.....PICCIOTTU.........|.......BOY
4 | PICCIOTTU1.....|...............................|.......YOUNG MAN
5 | PICCIOTTU2.....|...............................|.......YOUNG
6 | CHIANU...........|......CHIANU.............|.......FLAT
7 | CHIANU1.........|...............................|........SLOW
8 | CHIANU2.........|...............................|........OUTSIDE

So then all I have to do is sort by COLUMN A, and it will put PICCIOTTU first, then PICCIOTTU1 (second definition), and PICCIOTTU2 (third definition) last. Then I can hide the column. This works, but it's definitely not the ideal way of doing things, because I have to duplicate columns (B to A) and then fill in the blanks with the headword and add numbers to each of these. A bit much.

So I was hoping there was some way to just group these second and third blank rows to the headword, so that when sorted they act subordinately.

But maybe there's not a way to do this? I'll follow up with the next comment as well.

THANKS,

MICHELI SCALISI

Originally Posted by DonkeyOte

I concur with CC... note you can do this quite quickly...

Highlight Column containing Headwords --> Edit -> GoTo -> Special -> Blanks -> OK

Now type in:
=
up arrow

Hold CTRL and press ENTER

I'd really like to try this, as it sounds like it might save time. However, I don't understand the directions.

I'm using Microsoft Excel 2007, so at the top it doesn't have FILE, EDIT, etc. like the older versions. So I can't find EDIT > GO TO and so forth, as per your instructions.

Are you familiar with the 2007 version, and is it possible to get these instructions for that version, and possibly in a little more detail, because I didn't understand

Now type in:
=
up arrow

I would greatly appreciate it!!

MICHELI SCALISI

Also, please see my reply to HTH. Perhaps you can add something to that, or it will provide further understanding of what I'm trying to do.

Just fill them in with the same headword instead of assigning a number to it. It should only sort by the headword itself & leave the rest in whatever order you put it in. Then you can show all definitions for the headword by using the Auto Filter function. Doing this, you would just select PICCIOTTU in the auto filter & see all corresponding definitions.

Originally Posted by TheLostBoy

Just fill them in with the same headword instead of assigning a number to it. It should only sort by the headword itself & leave the rest in whatever order you put it in. Then you can show all definitions for the headword by using the Auto Filter function. Doing this, you would just select PICCIOTTU in the auto filter & see all corresponding definitions.

How would Excel know in what order to sort the definitions? Keep in mind, they won't necessarily be in the correct order as I enter them. So sorting three headword cells by PICCIOTTU would not necessarily have the definitions in the correct order, right?

I also don't know what the auto filter is, nor where to find it. Can you help?

By the way, I had another idea and wonder what you think of it. I thought of adding another small column before the definition, titled #. Here I would number the definitions. So COLUMN A (headword) rows 1 - 3 would be PICCIOTTU, blank, blank. Then COLUMN G (#) rows 1 - 3 would be 1, 2, 3. And COLUMN H (DEFINITIONS) would be the first definition, the second and then the third.

So sorting, I would choose COLUMN A, A-Z, and COLUMN G, Smallest to Largest. So it would put the row with Picciottu and (1) the first definition automatically, followed by the two blank rows with (2) and (3) in the # column.

Hopefully that makes sense. It works now, but this is because everything is in order anyway. So it's hard to say whether this will work in the future. The problem is some of these headwords will be taken from different sources, so their definitions, parts of speech, etymologies will all differ depending on the sources. So there will eventually be duplicates and additions to each headword, and I'm not sure if it will sort them correctly.

Let me know!

Micheli Scalisi

Take a look at the attachment. You'll see the arrows up top - try selecting a headword in the drop-down. Is that what you're after?

Originally Posted by TheLostBoy

Take a look at the attachment. You'll see the arrows up top - try selecting a headword in the drop-down. Is that what you're after?

Check out the attachment. Thanks!!

Originally Posted by shg

1. You can use conditional formatting to make repeated headwords appear invisible

2. You can use a custom sort order (Tools > Options > Custom Lists) to sort parts of speech however you wish.

Can you give me any more detail about this? I have no idea what conditional formatting is, how to do it, nor what it does exactly. I would like to though!

Thanks!

Micheli Scalisi