macro for Excel 2003 to combine and sort data range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

recorder - 24 Mar 2008 05:23 GMT

I need to convert data for the three columns below by repeating these steps.

Insert blank row at A1
Move contents of C2 to A1
Make A1 bold and underlined.
Move to next row in column C where data is different from date that was moved
in step above. Repeat loop until end of data in column is reached.

A B C
1 Bk 2662, Pg 153    1509056    Abstract of Inventory
2 Bk 2125, Pg 749    1509059    Affidavit of Deimmobilization
3 Bk 2661, Pg 780    1508959    Affidavit Of Lost Notes
4 Bk 661, Pg 875    1508995    Affidavit Of Lost Notes
5 Bk 2661, Pg 878    1508996    Affidavit Of Lost Notes
6 Bk 2661, Pg 883    1508998    Affidavit Of Lost Notes
7 Bk 2662, Pg 11    1509019    Affidavit Of Lost Notes
8 Bk 2662, Pg 14    1509020    Affidavit Of Lost Notes
9 Bk 2662, Pg 18    1509021    Affidavit Of Lost Notes
10 Bk 2662, Pg 103    1509046    Affidavit Of Lost Notes
11 Bk 2662, Pg 64    1509037    Affidavit of Substantial Complete
12 Bk 2125, Pg 676    1508988    Articles of Organization
13 Bk 159, Pg 581    1508988    Articles of Organization
14 Bk 2125, Pg 647    1508973    Assignment & Bill of Sale
15 Bk 2661, Pg 902    1509001    Assignment of Mortgage Note
16 Bk 2661, Pg 835    1508974    Certificate of Substantial Complete
17 Bk 2125, Pg 739    1509057    Convey Interest

This is what finished sheet should look like.

A B
1 Abstract of Inventory
2 Bk 2662, Pg 153    1509056
3 Affidavit of Deimmobilization
4 Bk 2125, Pg 749    1509059
5 Affidavit Of Lost Notes
6 Bk 2661, Pg 780    1508959
7 Bk 2661, Pg 875    1508995
8 Bk 2661, Pg 878    1508996
9 Bk 2661, Pg 883    1508998
10 Bk 2662, Pg 11    1509019
11 Bk 2662, Pg 14    1509020
12 Bk 2662, Pg 18    1509021
13 Bk 2662, Pg 103    1509046
14 Affidavit of Substantial Complete
15 Bk 2662, Pg 64    1509037
16 Articles of Organization
17 Bk 2125, Pg 676    1508988
18 Bk 159, Pg 581    1508988
19 Assignment & Bill of Sale
20 Bk 2125, Pg 647    1508973
21 Assignment of Mortgage Note
22 Bk 2661, Pg 902    1509001
23 Certificate of Substantial Complete
24 Bk 2661, Pg 835    1508974
25 Convey Interest
Bk 2125, Pg 739    1509057

Your help would be greatly appreciated. I have tried several time but can't
make it work.

Thanks

Reply to this Message

Mark Ivey - 24 Mar 2008 15:46 GMT

You might get a better response for this issue if you post this question to
the Excel Programming newsgroup...

Mark

> I need to convert data for the three columns below by repeating these
> steps.
[quoted text clipped - 60 lines]
>
> Thanks

Reply to this Message

Bernie Deitrick - 24 Mar 2008 16:40 GMT

Sub NewHeaders()
Dim myR As Long

For myR = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
If Cells(myR, 3).Value <> Cells(myR - 1, 3).Value Then
Cells(myR, 3).EntireRow.Insert
Cells(myR, 1).Value = Cells(myR + 1, 3).Value
End If
Next myR
Cells(1, 3).EntireRow.Insert
Cells(1, 1).Value = Cells(2, 3).Value
Cells(1, 3).EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP

> I need to convert data for the three columns below by repeating these steps.
>
[quoted text clipped - 57 lines]
>
> Thanks

Reply to this Message

recorder - 26 Mar 2008 02:47 GMT

THANKS. Worked great. I forgot to mention in my post that I would like for
each heading type (ie Abstract of Inventory) to be underlined and bold. Is
this any easy fix?

Again Thanks

> Sub NewHeaders()
> Dim myR As Long
[quoted text clipped - 75 lines]
> >
> > Thanks

Reply to this Message

Bernie Deitrick - 26 Mar 2008 17:33 GMT

Sub NewHeaders2()
Dim myR As Long

For myR = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
If Cells(myR, 3).Value <> Cells(myR - 1, 3).Value Then
Cells(myR, 3).EntireRow.Insert
With Cells(myR, 1)
.Value = Cells(myR + 1, 3).Value
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
End If
Next myR
Cells(1, 3).EntireRow.Insert
With Cells(1, 1)
.Value = Cells(2, 3).Value
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
Cells(1, 3).EntireColumn.Delete
End Sub

Signature

HTH,
Bernie
MS Excel MVP

> THANKS. Worked great. I forgot to mention in my post that I would like for
> each heading type (ie Abstract of Inventory) to be underlined and bold. Is
[quoted text clipped - 81 lines]
>> >
>> > Thanks

Reply to this Message

recorder - 27 Mar 2008 02:20 GMT

Thanks again. Your script will save me a good deal of time.

Best Regards

> Sub NewHeaders2()
> Dim myR As Long
[quoted text clipped - 103 lines]
> >> >
> >> > Thanks

Reply to this Message

Rate this thread: