Display data horizontally

[cultgag]

Here is what I need, I saw something similar to this before but I can't find it now.

A 1
A 2
A 3
A 4

B 1
B 2

C 3
C 5

D 2
D 5
D 9

Instead of data growing vertically I need

A 1 2 3 4
B 1 2
C 3 5
D 2 5 9


All data in A will be in one row, B in one row etc..

(A, B are just generic names I came up with

i.e : I want to group all rows with name A(inspect A) to have just 1 row
instead of having one row for each data)

Any help will be greatly appreciated.

Thanks

[bhofsetz]

What format is your data currently in?
Are you pulling it from another worksheet or another workbook or importing from text or *.csv?

[cultgag]

It's a .csv file, I'm pulling the data from another report.

[bhofsetz]

You could simply do a pivot table operation but this involves some manual manipulation on the spreadsheet. However if there are any duplicate names in Column 1 (i.e. if A is repeated below D), or if there are any empty cells then pivot tables have trouble.

A macro that loads your data into an array then outputs the data onto a new worksheet will probably work better for you and is more powerful if you want to do more automation (i.e. select and automatically open the *.csv, save the result *.xls file with a specified name, formatting, etc.).
I'll post some simple code for the transformation you are after for a place you can start.

[cultgag]

thanx for the help!

[bhofsetz]

Give this a try and let me know how it works

Option Explicit
Dim ResultAry(10, 100) As String 'assumes you have only 10 different groupings and
'only 100 values for each grouping
'(adjust each array parameter as required)
Dim x As Integer, y As Integer, V As Integer

Sub Master()
Load_ResultAry
UnLoad_ResultAry
End Sub

Sub Load_ResultAry()
x = 1: V = 0
Do Until Range("A" & x) = ""
y = 1
ResultAry(V, 0) = Range("A" & x)
Do Until ResultAry(V, 0) <> Range("A" & x)
ResultAry(V, y) = Range("B" & x)
y = y + 1
x = x + 1
Loop
V = V + 1
Loop
End Sub

Sub UnLoad_ResultAry()
Sheets("Sheet2").Select 'designate where you want the transformed data output
x = 1: V = 0
Do Until ResultAry(V, 0) = ""
y = 1
Cells(x, 1) = ResultAry(V, 0)
Do Until ResultAry(V, y) = ""
Cells(x, y + 1) = ResultAry(V, y)
y = y + 1
Loop
x = x + 1
V = V + 1
Loop
End Sub

HTH

B