Compare and align columns of data
 

I have two large files that I've combined into one sheet. They both list
account numbers and amounts charged. The account numbers could be the same
in both lists or could be in one list but not the other. I want to compare
the account numbers which would be in columns A & C. If they are the same I
want to move down and compare the next row. If the account number is in
column A but not column C I want to insert cells into C & D, then drop down
and compare the next row. If the account number is in C but not A I want to
insert cells into A&B and drop down to the next row and continue.

Before
A B C D
a/n
1 1.00 1 10.00
3 2.00 2 11.00
5 3.00 3 12.00
7 4.00 6 13.00

After
A B C D
1 1.00 1 10.00
2 11.00
3 2.00 3 12.00
5 3.00
6 13.00
7 4.00


I have never mastered the macro functions in Excel. I used to be able to do
this in Lotus. Any help would be appreaciated

Compare and align columns of data
 

Hi,

HTH. Assumes data starts in row 1; change initial value of i and j
as required.

Sub Transform()

Dim i As Long, j As Long

i = 1
j = 1

Do While (Cells(i, 1) < "") And (Cells(j, 3) < "")
If Cells(i, 1) Cells(j, 3) Then
Range(Cells(i, 1), Cells(i, 2)).Insert shift:=xlDown
Else
If Cells(i, 1) < Cells(j, 3) Then
Range(Cells(i, 3), Cells(i, 4)).Insert shift:=xlDown
End If
End If

i = i + 1
j = j + 1

Loop

End Sub



"JGouger" wrote:

I have two large files that I've combined into one sheet. They both list
account numbers and amounts charged. The account numbers could be the same
in both lists or could be in one list but not the other. I want to compare
the account numbers which would be in columns A & C. If they are the same I
want to move down and compare the next row. If the account number is in
column A but not column C I want to insert cells into C & D, then drop down
and compare the next row. If the account number is in C but not A I want to
insert cells into A&B and drop down to the next row and continue.

Before
A B C D
a/n
1 1.00 1 10.00
3 2.00 2 11.00
5 3.00 3 12.00
7 4.00 6 13.00

After
A B C D
1 1.00 1 10.00
2 11.00
3 2.00 3 12.00
5 3.00
6 13.00
7 4.00


I have never mastered the macro functions in Excel. I used to be able to do
this in Lotus. Any help would be appreaciated

Compare and align columns of data
 

Try this against a copy of your worksheet--just in case!

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColC As Range
Dim iRow As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
Set ColA = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set ColC = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))

With ColA.Resize(, 2)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

With ColC.Resize(, 2)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 1
Do
If Application.CountA(.Cells(iRow, "A"), .Cells(iRow, "C")) = 0 _
Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "c").Value _
Or Application.CountA(.Cells(iRow, "A"), .Cells(iRow, "C")) = 1 _
Then
'do nothing
Else
If .Cells(iRow, "A").Value .Cells(iRow, "c").Value Then
.Cells(iRow, "A").Resize(1, 2).Insert shift:=xlDown
Else
.Cells(iRow, "c").Resize(1, 2).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

JGouger wrote:

I have two large files that I've combined into one sheet. They both list
account numbers and amounts charged. The account numbers could be the same
in both lists or could be in one list but not the other. I want to compare
the account numbers which would be in columns A & C. If they are the same I
want to move down and compare the next row. If the account number is in
column A but not column C I want to insert cells into C & D, then drop down
and compare the next row. If the account number is in C but not A I want to
insert cells into A&B and drop down to the next row and continue.

Before
A B C D
a/n
1 1.00 1 10.00
3 2.00 2 11.00
5 3.00 3 12.00
7 4.00 6 13.00

After
A B C D
1 1.00 1 10.00
2 11.00
3 2.00 3 12.00
5 3.00
6 13.00
7 4.00

I have never mastered the macro functions in Excel. I used to be able to do
this in Lotus. Any help would be appreaciated

--

Dave Peterson