how to align all common list in excel?Solved/Closed

Question

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       


 PLEASE HELP ME SOLVE THE FORMULA FOR THIS...       
THANK YOU VERY MUCH, MORE POWER AND GOD BLESS!!!

rizvisa1 · Accepted Answer

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

How to align all common list in excel?

1 response

Similar discussions

Subscribe To Our Newsletter!