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:

Option Explicit

Sub FilterData()
Dim LC As Long, LR As Long, dCol As Long
Dim wsData As Worksheet, wsOut As Worksheet
Application.ScreenUpdating = False

Set wsData = Sheets("Data")
Set wsOut = Sheets("Output")

'Clear existing output sheet
wsOut.Cells.Clear

'Create sorted list of headers on Output sheet using Adv Filter
With wsData
    .Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("C1"), Unique:=True
    LR = .Range("C" & .Rows.Count).End(xlUp).Row
    .Range("C2:C" & LR).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
    .Range("C2:C" & LR).Copy
    wsOut.Range("A1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
    .Range("C1:C" & LR).ClearContents
    LR = .Range("B" & .Rows.Count).End(xlUp).Row
End With

'Gather the data into each column
    LC = wsOut.Cells(1, Columns.Count).End(xlToLeft).Column
    For dCol = 1 To LC
        wsData.Range("A:A").AutoFilter Field:=1, Criteria1:=wsOut.Cells(1, dCol)
        wsData.Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).Copy wsOut.Cells(2, dCol)
    Next dCol
        
wsData.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

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.)