Multiple vertical data entries need to be horizontal...

mostin

New Member
Joined
Dec 6, 2010
Messages
6
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. :confused:
Matthew
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How do you want the result to look?
 

mostin

New Member
Joined
Dec 6, 2010
Messages
6
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

New Member
Joined
Dec 6, 2010
Messages
6
Sorry, hope you get in the last reply that the 'yes' values should be below each category
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 6, 2010
Messages
6
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,071

ADVERTISEMENT

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

SaladProblems

Active Member
Joined
Apr 9, 2010
Messages
279
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 6, 2010
Messages
6
@saladproblems The categories I listed with the numbers are the variables. All others are duplicates.
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top