Excel help

InsideInsurance

I have a data table in excel and need to manipulate how it is entered but not sure how to do it as it is beyond my Excel skills and I suspect it will require VBA.

To make the example easier I currently have a table about people's hobbies. In column A I have a unique number, in column B I have an ID number for the person, in column C I have their hobby and in column D I have their proficiency level

As a single person can have more than one hobby people are entered into the table N times.

What I want to do is create a new table where each persons ID number (ie column appears once and all their hobbies (from column C) appear in the following columns (1 per column).

So in the original table each person is in N rows where as in the new table there is 1 row per person but their hobbies fill N+1 columns

Any suggestions?

rmg1

The fastest (and probably easiest) way to get what you want is to use a Pivot Table.
You would have the person ID down the side, the hobby ID across the top, and the proficiency level in the middle/

InsideInsurance

Many thanks for the suggestion, the issue is that there are a couple of hundred different hobbies (and a few thousand people) and so a pivot table isnt going to work.

Whilst the original data table has a proficiency level this can be ignored as I simply need a new table with 1 row per person an a horizontal list of their hobbies (irrespective of proficiency level) in the following columns.

m5rcc

You can do a Pivottable with 65,000 rows and 676 columns. It's built to handle large volumes of data

rmg1

If that's all you need, you can also use formulae to do what you want but you'll need a helper column.

I'll PM you a file over in a little while once I've worked out the intricacies.