Excel - How to align all common list?

December 2016




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, _ 
            Header:=xlYes, OrderCustom:=1, _ 
            MatchCase:=False, Orientation:=xlTopToBottom, _ 
            DataOption1:=xlSortNormal 
             
    Columns("C:D").Select 
    Selection.Sort _ 
            Key1:=Range("C2"), Order1:=xlAscending, _ 
            Header:=xlYes, OrderCustom:=1, _ 
            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.

Related :

This document entitled « Excel - How to align all common list? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.