I have data in 3 columns. I would like to convert data from Column A to be sub headings and Column B and C to stay as is. Also, if Column A has the same data in Row 1 thru 5, it should be just one heading.
So, For example, raw data is
X 1 2
X 3 4
X 5 6
Y 7 8
Y 9 10
the results should be
X
1 2
3 4
5 6
Y
7 8
9 10
Thanks for any help
Try this macro
Sub Test() Columns("E:F").Clear CurrentSubHeading = Cells(1, 1) Cells(1, 5) = CurrentSubHeading For N = 1 To Cells(65536, 1).End(xlUp).Row If Cells(N, 1) <> CurrentSubHeading Then Cells(65536, 5).End(xlUp).Offset(2, 0) = Cells(N, 1) CurrentSubHeading = Cells(N, 1) End If Cells(65536, 5).End(xlUp).Offset(1, 0) = Cells(N, 2) Cells(65536, 5).End(xlUp).Offset(0, 1) = Cells(N, 3) Next N End Sub
Martin
Martin
Thank you very much. May I ask for one more modification help.
Currently, this new data falls into these columns
Original
A B C
Column D is blank
The formatted data is in E F
The heading is in E (what was originally in A)
the text is in E and F (what was in B and C originally)
Is it possible to move the data into F and G such that it looks like it is indented.
Thanks
Ginny
No problem
Try the following
Sub Test() Columns("E:G").Clear CurrentSubHeading = Cells(1, 1) Cells(1, 5) = CurrentSubHeading Cells(65536, 5).End(xlUp).Offset(1, 1) = Cells(1, 2) Cells(65536, 6).End(xlUp).Offset(0, 1) = Cells(1, 3) For N = 2 To Cells(65536, 1).End(xlUp).Row If Cells(N, 1) <> CurrentSubHeading Then Cells(65536, 6).End(xlUp).Offset(2, -1) = Cells(N, 1) CurrentSubHeading = Cells(N, 1) Cells(65536, 5).End(xlUp).Offset(1, 1) = Cells(N, 2) Cells(65536, 6).End(xlUp).Offset(0, 1) = Cells(N, 3) Else Cells(65536, 6).End(xlUp).Offset(1, 0) = Cells(N, 2) Cells(65536, 6).End(xlUp).Offset(0, 1) = Cells(N, 3) End If Next N End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks