compare two culomns

hb February 4, 2009 at 07:33:14
Specs: Windows XP
hi all, I have a problem and deeply need your help.
I have a date from my acc. system and another date in same sheet form a suppler, i want to compare this date according to the amount as follow: you will understand me from this example as i guess.

i will put my date in the rage
A:j and put the other date in the rage N:T ( my amount will be in column J and other amount from other date in column N ). here i will put an example for only columns J and N.

1500 1500
1500 1500
1500 1200
1200 1200
1200 1100
1100 1100
1000 1100
1000 1100
900 1000
900

i want it as follow:

1500 1500
1500 1500
1500

1200 1200
1200 1200

1100 1100
1100
1100
1100

1000 1000
1000

900 900

i will be thankful if any body can help me to solve this problem.
i can do a sort according to the amount so no need to do that throw the macro. thanks a lot in advance.



See More: compare two culomns

Report •


#1
February 6, 2009 at 10:21:21
Not sure this is what your looking for, but

To Find Duplicate data in two columns:

1st - Highlight Column Data Cells
I.E. Column J Cells 1-50 and Column N Cells 1-50

On the Menu Bar select:

2nd - Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:

=COUNTIF($J1,$N1)

Sub Menu Format :
6th - Patterns
7th - Choose a pretty color
8th - Press OK
9th - Press OK

All your duplicate records should now be highlighted in your selected pretty color.

There are other ways of doing this.

One other way is to use the EXACT function.

In the top cell of a BLANK column enter the formula:

=EXACT($J1,$N1)

Then copy the forumula down
You will see the word "TRUE" for those cells that Match
and "FALSE" for those cells that do not Match.

The advantage here is that you can now sort your whole
sheet on the "TRUE or FALE" column, which might be handy.

MIKE


Report •

#2
February 7, 2009 at 04:01:55
first of all, thanks a lot Mike, acutely i need a macro to insert empty rows whenever he finds a change in the amount in my date or in in compared date.

i have tried to write a macro but when i try it i have an error message.

my macro is:

Sub SortMatch()

Columns("a:j").Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlYes
Columns("n:t").Sort Key1:=Range("n1"), Order1:=xlAscending, Header:=xlYes

LastRow = Range("n" & Rows.Count).End(xlUp).Row

For NxtName = 3 To LastRow
If Range("n" & NxtName) = Range("j" & NxtName) And Range("j" & NxtName) <> Range("j" & NxtName - 1) And Range("n" & NxtName) <> Range("n" & NxtName - 1) Then
Range("a" & NxtName & " :t " & NxtName).Insert shift:=xlDown
ElseIf Range("n" & NxtName) <> Range("j" & NxtName) And Range("j" & NxtName) = Range("j" & NxtName - 1) And Range("n" & NxtName) <> Range("n" & NxtName - 1) Then
Range("n" & NxtName & " :t " & NxtName).Insert shift:=xlDown
ElseIf Range("n" & NxtName) <> Range("j" & NxtName) And Range("j" & NxtName) > Range("j" & NxtName - 1) And Range("n" & NxtName) = Range("n" & NxtName - 1) Then
Range("a" & NxtName & ":j" & NxtName).Insert shift:=xlDown
Else: End If
End If
Next
End Sub

the error message is

end if without blocking if.


Report •

#3
February 7, 2009 at 07:28:55
Sorry, my macro ability is just a shade above null.


Hopefully, someone else can help.

MIKE


Report •

Related Solutions

#4
February 7, 2009 at 09:11:59
I'm not sure how your code looks in your VBA editor, but I like to use indenting and the Line Continuation character (underscore) to make my code easier to read. That makes it easier to find errors because you can follow blocks of code in a logical manner.

After I laid out your code the way I like, it was easy to see that you end your Block If twice - once with a Else: End If, then with a "standard" End If. Only one of those is needed.

I would use the standard End If since I can line it up with the starting If statement and close off that indented block, as follows:

Sub SortMatch()

Columns("a:j").Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlYes
Columns("n:t").Sort Key1:=Range("n1"), Order1:=xlAscending, Header:=xlYes

LastRow = Range("n" & Rows.Count).End(xlUp).Row

 For NxtName = 3 To LastRow
  If Range("n" & NxtName) = Range("j" & NxtName) And _
     Range("j" & NxtName) <> Range("j" & NxtName - 1) And _
     Range("n" & NxtName) <> Range("n" & NxtName - 1) Then
       Range("a" & NxtName & " :t " & NxtName).Insert shift:=xlDown
    ElseIf Range("n" & NxtName) <> Range("j" & NxtName) And _
           Range("j" & NxtName) = Range("j" & NxtName - 1) And _
           Range("n" & NxtName) <> Range("n" & NxtName - 1) Then
             Range("n" & NxtName & " :t " & NxtName).Insert shift:=xlDown
        ElseIf Range("n" & NxtName) <> Range("j" & NxtName) And _
               Range("j" & NxtName) > Range("j" & NxtName - 1) And _
               Range("n" & NxtName) = Range("n" & NxtName - 1) Then
                 Range("a" & NxtName & ":j" & NxtName).Insert shift:=xlDown
            'Delete This Line - Else: End If
  End If
 Next
End Sub


Report •


Ask Question