Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare 2 columns and align duplicates into same row | Excel Worksheet Functions | |||
Align & compare data | Excel Discussion (Misc queries) | |||
Filtering Columns to Align Matching Data | Excel Worksheet Functions | |||
Macro to align & compare multiple columns with several rows | Excel Programming | |||
Macro to align and compare multiple rows and columns | New Users to Excel |