Sorting, Grouping, Organizing

  • Thread starter Thread starter Zucchera
  • Start date Start date
Z

Zucchera

Hey,

Thanks for any and all help on this.

I have a database file as follows with words and a set of numbers
following each word:

Apparition 08 23 65
Dream 08 65 68

and so on.....

The numbers refer to definition groups for each word in the left most
column. Definitions groups are numbered 01 to 70, for example.

The user I'm helping would like to be able to have Excel sift through
this database and sort the information as follows.

Find all rows with a number 65 in it, for example, and copy the word in
the left most column of the row into a new column under heading 65.
Do this for all rows until all words matching criteria 65 are copied
into the column for Definition Group 65.

Do this for all words and all of the definition groups to which the
words belong.

The users would like to be able to do this for all 70 definition
groups, ending up with 70 columns consisting of all possible words that
belong to that particular category group.

At the end, a spreasheet of definition groups would like something like
this.

Help Group Happy Group
08 = Category 65 = Catetory
Appartion Apparition
Dream Dream
Words Words
More Words More Words....


and so on through the entire database dump.


Thanks muchly for any and all ideas.
 
1. With your words and numbers in cells A2:D500, add numbers as headings
in cells F1:BW1 (type 1 in F1, type 2 in G1, select both cells, and
drag the fill handle to column BW)

2. Enter the following formula in cell F2, and copy across to cell BW2:
=IF(COUNTIF($B2:$D2,F$1),$A2,FALSE)

3. Select cells F2:BW2, and copy down to row 500

4. Select columns F:BW, and choose Edit>Copy
5. Choose Edit>Paste Special, Values, click OK

6. With the columns still selected, choose Edit>Go To, click Special
7. Select Constants, and remove check marks from Numbers, Text and
Errors (leave Logicals checked).
8. Click OK
9. Choose Edit>Delete, Shift Cells Up, click OK
10. Delete columns A:E
 
Hi Deborah,

Thanks so much for your quick and dirty sort statement. It works great
except for the first row! The first row of the sort fills in the first
word across all column headings regardless of whether or not the word
belongs to that group. After the first row, the sort works perfectly.

Hmmmm and hmmmmmm. Once I get that little 'issue' straightened out,
I'll set it up in VB.

If you have any ideas as to why row 1 of the sort result is not
correctly parsing, please let me know.

Thanks muchly......Z.
 
Hi Deborah,

Got it fixed. User (moi) error. Duh - fat fingers! LOL!!!!

Now that my tired brain has wrapped around this, the user gets a macro
and will be sooooo happy.

Best......Z.
 
Back
Top