# 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 900i want it as follow: 1500 1500 1500 1500 1500 1200 1200 1200 1200 1100 1100 1100 1100 1100 1000 1000 1000 900 900i 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

#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-50On the Menu Bar select:2nd - Format3rd - Conditional Formatting4th – Change “Cell Value is” to “Formula Is”5th – Enter the formula: =COUNTIF(\$J1,\$N1)Sub Menu Format :6th - Patterns7th - Choose a pretty color8th - Press OK9th - Press OKAll 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 Matchand "FALSE" for those cells that do not Match.The advantage here is that you can now sort your wholesheet 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:=xlYesColumns("n:t").Sort Key1:=Range("n1"), Order1:=xlAscending, Header:=xlYesLastRow = Range("n" & Rows.Count).End(xlUp).RowFor NxtName = 3 To LastRowIf 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:=xlDownElseIf 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:=xlDownElseIf 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 IfNextEnd 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 •