Excel-Auto Sort and Paste to new Column
 

How can I instruct Excel to create a new heading for all like data that is
currently in the same field?

For example, my data looks like this now:


1) A B C D E (Headings Row)
2) a b c x z
3) a b c x y
4) a b c z x
5) a b c y z
6) a b c d e

I need it the data to look like this without having to sort, cut and paste.
There is
too much data to sort, cut and paste. Note: In the sample below, x, y, and
z data now have their own fields (columns)

1) A B C D E X Y Z (Headings Row)
2) a b c x z
3) a b c x y
4) a b c x z
5) a b c y z
6) a b c d e

Excel-Auto Sort and Paste to new Column
 

This macro copies the data from sheet1 to sheet2. I don't like over writing
existing data. When yo are done you can always delete the original data.

Sub createcolumns()

RowCount = 2
Sh2LastCol = 2
With Sheets("Sheet1")
Do While .Range("A" & RowCount) < ""
LastCol = .Cells(RowCount, Columns.Count). _
End(xlToLeft).Column
For Sh1ColCount = 1 To LastCol
Data = .Cells(RowCount, Sh1ColCount)
With Sheets("Sheet2")

Set c = .Rows(1).Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, Sh2LastCol) = Data
.Cells(RowCount, Sh2LastCol) = Data
Sh2LastCol = Sh2LastCol + 1
Else
.Cells(RowCount, c.Column) = Data
End If
End With
Next Sh1ColCount
RowCount = RowCount + 1
Loop
End With

End Sub
"ManhattanRebel" wrote:

How can I instruct Excel to create a new heading for all like data that is
currently in the same field?

For example, my data looks like this now:


1) A B C D E (Headings Row)
2) a b c x z
3) a b c x y
4) a b c z x
5) a b c y z
6) a b c d e

I need it the data to look like this without having to sort, cut and paste.
There is
too much data to sort, cut and paste. Note: In the sample below, x, y, and
z data now have their own fields (columns)

1) A B C D E X Y Z (Headings Row)
2) a b c x z
3) a b c x y
4) a b c x z
5) a b c y z
6) a b c d e