Converting an excel spreadsheet

[Guest]

I have been asked to handle the office football bowl pool. I was given an
excel spreadsheet that I imported and is formatted as follows:

John Ed Paul.......
Ark S Miss Ark
Navy C St C St
Ks Ks KS

If I could convert this to something like:

Name Team
John Ark
John Navy
John Ks.
Ed S Miss
Ed C St

I could link to a winners table and do a totals query, group on name and
count the teams, but I can't figure out how to get the spreadsheet into this
format. There are over 80 people on the table so I was looking for an easy
way to do this.

 

[Tom Ellison]

Dear Gordon:

1. Rotate the spreadsheet so it reads:

John Ark Navy Ks
Ed SMiss C St Ks
Paul Ark C St KS

For details, talk to the guys in the Excell newsgroups.

2. Import this into a database.

3. Use a Normalizing UNION query:

SELECT Name, 1, School1
FROM YourTable
WHERE School1 IS NOT NULL
UNION ALL
SELECT Name, 2, School2
FROM YourTable
WHERE School2 IS NOT NULL
SELECT Name, 3, School3
FROM YourTable
WHERE School3 IS NOT NULL

4. Append or past this into the table you mentioned.

I added a number to the output to track whether each name/school come from
the first, second, or third selection. This could be important. It also
gives you a unique natural key on School/Number which would be a good unique
index, even the primary key. Under the theory that the position and/or
order of the information in the non-normalized form may be of value, I
usually include this.

Tom Ellison