How to align all common list in excel?

Solved/Closed
Report
Posts
12
Registration date
Monday April 5, 2010
Status
Member
Last seen
November 2, 2018
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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!!!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
12
Registration date
Monday April 5, 2010
Status
Member
Last seen
November 2, 2018

is there any other way of solving it without using Macro?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
There might be. Beside being out of my limited capabilities, it would be slow