Multiple vertical data entries need to be horizontal...

[mostin]

Hi all,

Here's my conundrum.

I have some data that I've exported from MS Access. It contains multiple categories. So, for example, someone could be into apples, oranges and pears, and also into dogs and cats, and therefore the data looks like this:

NAME ANIMALS FRUIT
Joe Bloggs Cats Apples
Joe Bloggs Cats Oranges
Joe Bloggs Cats Pears
Joe Bloggs Dogs Apples
Joe Bloggs Dogs Oranges
Joe Bloggs Dogs Pears

How do I transfer the data so there is only one entry for Joe Bloggs without having to do it manually. We have over 5000 entries and an intern working on it manually at the moment. She's calculated it's going to take her well over 100 hours to do the whole thing.

Any help much appreciated.
Matthew

 

[mostin]

Again, trying to get the specifics here, what columns does each field appear in?

And as for the output, in your initial example, you had the output columns as "CAT", "DOG", etc... do you want these output columns to be like "CatID 1", "CatID 2", ..., "ProID 1", "ProID 2", etc? Or how do you want the output to be laid out.

Not sure what your first question means - but I'll try and explain again:

For each project they can have more than one CategoryID number, meaning that if they are in three categories, they appear in three rows:

Here's a screenshot of what it looks like now: http://www.flickr.com/photos/matthewaustin/5240369431/

I would like the output to be so that there are columns titled "CatID 1" to CatID 29", "ProID1" to "ProID38" and so on and that each contact appears once with either 'yes' or 'true' in each relevant column.

Hope that makes sense...!