PDA

View Full Version : Need formula to arrange column data into different rows



loveguy1977
03-28-2012, 12:27 AM
Hello,

I'm looking for a formula that arrange column data into different rows based on the 1st letter.

Example:
Col (XX): content companies' name and I always insert new names.

Google
Boston Consulting Group
SAS Institute
Edward Jones
NetApp
Camden Property Trust
CHG Healthcare Services
DPR Construction
DreamWorks Animation
Chesapeake Energy
Robert W. Baird
Alston & Bird
Burns & McDonnell
Devon Energy
Bingham McCutchen


I need to list all company using formula to be listed and arranaged based on the 1st letter (A, B, C and so on)

In Col (A):
Alston & Bird

In Col (B):
Burns & McDonnell
Bingham McCutchen

In Col (C):
Camden Property Trust
CHG Healthcare Services
Chesapeake Energy

And so on.

In case I insert new company name in Col (XX) then it should be appear in related Col.


Thank you very much

Bob Phillips
03-28-2012, 01:13 AM
Sub ProcessData()
Dim lastrow As Long
Dim targetcol As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "XX").End(xlUp).Row
For i = 1 To lastrow

targetcol = Asc(Left$(.Cells(i, "XX").Value, 1)) - 64
If .Cells(1, targetcol).Value = "" Then

.Cells(1, targetcol).Value = .Cells(i, "XX").Value
ElseIf .Cells(2, targetcol).Value = "" Then

.Cells(2, targetcol).Value = .Cells(i, "XX").Value
Else

.Cells(1, targetcol).End(xlDown).Value = .Cells(i, "XX").Value
End If
Next i
End With
End Sub