# Excel - How to align all common list?

April 2018

## Issue

I have 4 columns. the first two (A & B) columns are list of cheques i issued. then the other two (C & D) columns are list of cheques encashed from Bank. that looks like this:

```A ---------- B ---------- C ---------- D
ISSUED----Amount----En-cashed----amount

001 ------ 112.00 ------ 001 ------ 112.00
002 ------ 200.25 ------ 003 ------ 350.00
003 ------ 350.00 ------ 004 ------ 512.00
004 ------ 512.05 ------ 005 ------ 200.10
005 ------ 200.05 ------ 007 ------ 821.30
006 ------ 300.25 ------ 009 ------ 100.10
007 ------ 821.30 ------ 010 ------ 500.00
008 ------ 254.23
009 ------ 100.00
010 ------ 500.00 ```

Now, what i want to do is align all common cheque numbers in column A and C. And letting me know on column E if the amount has the same value. which will look like this:

```A ---------- B ---------- C ---------- D ---------- E
ISSUED----Amount----En-cashed----amount-----Value

001 ------ 112.00 ------ 001 ------ 112.00 ----- TRUE
002 ------ 200.25
003 ------ 350.00 ------ 003 ------ 350.00 ----- TRUE
004 ------ 512.05 ------ 004 ------ 512.00 ----- FALSE
005 ------ 200.05 ------ 005 ------ 200.10 ----- FALSE
006 ------ 300.25
007 ------ 821.30 ------ 007 ------ 821.30 ----- TRUE
008 ------ 254.23
009 ------ 100.00 ------ 009 ------ 100.80 ----- FALSE
010 ------ 500.00 ------ 010 ------ 500.00 ----- TRUE```

## Solution

Assumptions
• 1. The max rows of data is found in column A
• 2. At the time of execution of macro, the active sheet is the sheet with data

```Sub AlignAndAccount()
Dim lMaxRows As Long
Dim lRowBeanCounter As Long

Columns("A:B").Select
Selection.Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Columns("C:D").Select
Selection.Sort _
Key1:=Range("C2"), Order1:=xlAscending, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row

Cells(1, "E") = "Value"

For lRowBeanCounter = 2 To lMaxRows

Select Case Cells(lRowBeanCounter, "A")

Case Is = Cells(lRowBeanCounter, "C")

If (Cells(lRowBeanCounter, "B") = Cells(lRowBeanCounter, "D")) Then
Cells(lRowBeanCounter, "E") = "TRUE"
Else
Cells(lRowBeanCounter, "E") = "FALSE"
End If

Case Is < Cells(lRowBeanCounter, "C")

Range("C" & lRowBeanCounter & ":D" & lRowBeanCounter).Select
Selection.Insert Shift:=xlDown
'Cells(lRowBeanCounter, "E") = "FALSE"

Case Else

Range("A" & lRowBeanCounter & ":B" & lRowBeanCounter).Select
Selection.Insert Shift:=xlDown
lMaxRows = lMaxRows + 1
End Select

Next lRowBeanCounter

End Sub```

## Note

Thanks to rizvisa1 for this tip on the forum.
Published by aakai1056. Latest update on April 14, 2010 at 06:43 AM by aakai1056.