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

 

[MrKowz]

How do you want the result to look?

 

[mostin]

Something like this:

NAME CATS DOGS ORANGES APPLES PEARS
Joe YES YES YES YES YES

So that there could be more 'animal' categories that Joe might not be into, but other people might....

Hope that makes sense

 

[mostin]

Sorry, hope you get in the last reply that the 'yes' values should be below each category

 

[MrKowz]

So I can better create a VBA macro that will accomplish this, is it possible for you to give me an exact representation of your data? How many categories are possible, what are the categories, and in what columns do these categories appear?

 

[mostin]

Yes, it's fairly complex. But here goes:

Each person has a PersonID, then there is First_Name and Last_Name columns then various address, phone email columns (let me know if you need these)

Then there are CategoryID, Project ID, FuelcompanyID, TheatrespecialistID columns

In CategoryID, numbers 1 to 29 could appear
In Project ID, numbers 1 to 38 could appear
In FuelcompanyID, numbers 1 to 21 could appear
In TheatrespecialistID, numbers 1 to 14 could appear

Let me know if you need more info. It's bamboozling me!

 

[mikerickson]

A pivot table with Names in the row headers, Fruit in the column headers might work.

 

[SaladProblems]

Which columns are the ones with different entries? The ID numbers? Are there multiple addresses, phone numbers, etc., and do you still have access to the Access database?

Also with 3500 entries are there any duplicate names that you need to worry about?

 

[MrKowz]

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.

 

[mostin]

@saladproblems The categories I listed with the numbers are the variables. All others are duplicates.