Problem turning list to table

[NoviceGuy]

Hi
I have a problem turning a lsit of data into a table, I would appreciate help. The list looks like.

The list is quite long (4000 obs) and 37 categories (Area)

Area Length
A 12
A 14
A 13
A 10
B 5
B 7
B 9
. .
ETC n

To this

A B C ..... Z
12 5 n ......Z
14 7 n .......Z
13 9 n .......Z
10 n n .......Z

Where n is an observation at Area

I do not want to manipulate these data in any way, just re-arrange them in a MxN table. The numbers in each area may be different. I am using Ecel 2007. Sorry if this question is silly, but for some reason, it has me stumped.

Thanks in advance NoviceGuy....
I have tried everything I can think of but still no luck. T

[JBeaucaire]

Post up a not-oversimplified example workbook.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[NoviceGuy]

Here is the spreadsheet, it also has my Novice-like approach to the problem (which doesn't work). I'm sure I'm missing something simple here.

NG

[JBeaucaire]

Name the data sheet Data.
Name an empty sheet Output.

Run this macro:

Please Login or Register  to view this content.

The simple things (?) here we are using are Advanced Filter/Sort to get the unique values, and copy/paste/transpose to put them in a row as headers.

Then we are using the AUTOFILTER on the column A to display all the rows of each header value one at a time. Then we copy the filtered results to the columns in sequence. Copying is only taking visible cells.

[NoviceGuy]

You are THE man Jerry, full qudos to you my friend. I'm glad the solution wasn't as simple as I thought it would be. I have learned a lot from your code example. It works, perfectly. I will still need to lookup the autofilter and other functions I am not familiar withm but that's my homework. Sleep well tonight your mission is accomplished.

Again, a big thankyou.
NG
ps. how do I mark this as solved?

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)