Excel - How to align all common list?

February 2017




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


Published by aakai1056. Latest update on April 14, 2010 at 06:43 AM by aakai1056.
This document, titled "Excel - How to align all common list?," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).