Insert blank cell and labelling if difference in comparsion

Dear all,

I have face a problem stated below:

A B C D
CN1 555555 CN1 444666
CN2 777888 CN3 888999
CNP1000 456456 CNP1001 123456
G1001 1234567 G1003 8888888
G1002 1234567 G1004 4564564
G1003 9999999 G1005 1222222
G1004 4564564 G1006 2525252
ZJ8000 894894 ZJ8000 894897
ZJ8500 123456 ZJ8501 123457

I will be comparing the 4 columns at the same time
Will it be possible to indicate in column E (DIFF!!!) if there is a different between Col B and Col D? And indicate in column F (BLANK!!!) when either one of the Col A/ B or Col C/D is not present ( CN2 & CN3 )and at the same time insert an empty cell ? Here are the lists staed below:


A..............B............. C..............D............. E............... F
CN1..........555555..... CN1......... 444666......DIFF!!!
CN2..........777888....._______....______.......................BLANK!!!
___...........______.....CN3..........888999......................BLANK!!!
CNP1000 456456.....____........._______.....................BLANK!!!
_______... _____.......CNP1001...123456.....................BLANK!!!
G1001.......1234567..._______...._______.....................BLANK!!!
G1002.......1234567..._______...._______.....................BLANK!!!
G1003.......9999999...G1003.......8888888.....DIFF!!!
G1004.......4564564...G1004.......4564564
G1005.......1222222..._______...._______.....................BLANK!!!
G1006 ......2525252.._______....._______.....................BLANK!!!
ZJ8000.......8948947...ZJ8000......8948977.....DIFF!!!
ZJ8500.......1234561...ZJ8501......1234571.....DIFF!!!

..... ( You can ignore the dots they are jus to indicate spaces in the excel
___ ( the underlines indicate the inserted blank cell )

Hope to hear from your soonest reply.
Warmest regards
Balfear

Formula in Cell E2:
=IF(LEN(F2)=0,IF(B2<>D2,"DIFF!",""),"")

Formula in Cell F2:
=IF(OR(LEN(A2&B2)=0,LEN(C2&D2)=0),"BLANK!","")

HTH.
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Balfear" wrote:

>
> Dear all,
>
> I have face a problem stated below:
>
> A B C D
> CN1 555555 CN1 444666
> CN2 777888 CN3 888999
> CNP1000 456456 CNP1001 123456
> G1001 1234567 G1003 8888888
> G1002 1234567 G1004 4564564
> G1003 9999999 G1005 1222222
> G1004 4564564 G1006 2525252
> ZJ8000 894894 ZJ8000 894897
> ZJ8500 123456 ZJ8501 123457
>
> I will be comparing the 4 columns at the same time
> Will it be possible to indicate in column E (DIFF!!!) if there is a
> different between Col B and Col D? And indicate in column F (BLANK!!!)
> when either one of the Col A/ B or Col C/D is not present ( CN2 & CN3
> )and at the same time insert an empty cell ? Here are the lists staed
> below:
>
>
> A..............B............. C..............D.............
> E............... F
> CN1..........555555..... CN1......... 444666......DIFF!!!
> CN2..........777888....._______....______.......................BLANK!!!
>
> ___...........______.....CN3..........888999......................BLANK!!!
>
> CNP1000 456456.....____........._______.....................BLANK!!!
> _______... _____.......CNP1001...123456.....................BLANK!!!
> G1001.......1234567..._______...._______.....................BLANK!!!
> G1002.......1234567..._______...._______.....................BLANK!!!
> G1003.......9999999...G1003.......8888888.....DIFF!!!
> G1004.......4564564...G1004.......4564564
> G1005.......1222222..._______...._______.....................BLANK!!!
> G1006 ......2525252.._______....._______.....................BLANK!!!
> ZJ8000.......8948947...ZJ8000......8948977.....DIFF!!!
> ZJ8500.......1234561...ZJ8501......1234571.....DIFF!!!
>
> ..... ( You can ignore the dots they are jus to indicate spaces in the
> excel
> ___ ( the underlines indicate the inserted blank cell )
>
> Hope to hear from your soonest reply.
> Warmest regards
> Balfear
>
>
> --
> Balfear
> ------------------------------------------------------------------------
> Balfear's Profile: http://www.excelforum.com/member.php...o&userid=35441
> View this thread: http://www.excelforum.com/showthread...hreadid=552147
>
>

Originally Posted by Gary L Brown

Formula in Cell E2:
=IF(LEN(F2)=0,IF(B2<>D2,"DIFF!",""),"")

Formula in Cell F2:
=IF(OR(LEN(A2&B2)=0,LEN(C2&D2)=0),"BLANK!","")

HTH.
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

Hi Gary Brown,

i have try the codes but from coloumn A and C is there are different it cant drop to the next row.

below are the codes i have try:

Sub CompareIt1()
Dim cell As Range
Range("a1:a1000").Select
For Each cell In Selection
If Len(cell) > 1 And Len(cell.Offset(0, 2)) > 1 Then
If Val(Right(cell, 4)) < Val(Right(cell.Offset(0, 2), 4)) Then
Range(cell.Offset(0, 2), cell.Offset(0, 3)).Insert Shift:=xlDown
Else
If Val(Right(cell, 4)) > Val(Right(cell.Offset(0, 2), 4)) Then Range(cell, cell.Offset(0, 1)).Insert Shift:=xlDown
End If
End If
Next cell
For Each cell In Selection
If cell.Text = "" Or cell.Offset(0, 3) = "" Then GoTo NextOne
If cell.Offset(0, 1) <> cell.Offset(0, 3) Then
cell.Offset(0, 4) = "DIFF!!"
End If
NextOne:
Next cell
End Sub

but when it reach a alpha and 3 numerical the marco cannot be used. and also im not sure how to make it blank when either column A and column C do be empty.

please advice.

Regards
Balfear

--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Balfear" wrote:

>
> Gary L Brown Wrote:
> > Formula in Cell E2:
> > =IF(LEN(F2)=0,IF(B2<>D2,"DIFF!",""),"")
> >
> > Formula in Cell F2:
> > =IF(OR(LEN(A2&B2)=0,LEN(C2&D2)=0),"BLANK!","")
> >
> > HTH.
> > --
> > Gary Brown
> > gary_brown@ge_NOSPAM.com
> > If this post was helpful, please click the ''Yes'' button next to ''Was
> > this
> > Post Helpfull to you?''.
>
> Hi Gary Brown,
>
> i have try the codes but from coloumn A and C is there are different it
> cant drop to the next row.
>
> below are the codes i have try:
>
> Sub CompareIt1()
> Dim cell As Range
> Range("a1:a1000").Select
> For Each cell In Selection
> If Len(cell) > 1 And Len(cell.Offset(0, 2)) > 1 Then
> If Val(Right(cell, 4)) < Val(Right(cell.Offset(0, 2), 4)) Then
> Range(cell.Offset(0, 2), cell.Offset(0, 3)).Insert Shift:=xlDown
> Else
> If Val(Right(cell, 4)) > Val(Right(cell.Offset(0, 2), 4)) Then
> Range(cell, cell.Offset(0, 1)).Insert Shift:=xlDown
> End If
> End If
> Next cell
> For Each cell In Selection
> If cell.Text = "" Or cell.Offset(0, 3) = "" Then GoTo NextOne
> If cell.Offset(0, 1) <> cell.Offset(0, 3) Then
> cell.Offset(0, 4) = "DIFF!!"
> End If
> NextOne:
> Next cell
> End Sub
>
> but when it reach a alpha and 3 numerical the marco cannot be used. and
> also im not sure how to make it blank when either column A and column C
> do be empty.
>
> please advice.
>
> Regards
> Balfear
>
>
> --
> Balfear
> ------------------------------------------------------------------------
> Balfear's Profile: http://www.excelforum.com/member.php...o&userid=35441
> View this thread: http://www.excelforum.com/showthread...hreadid=552147
>
>

Balfear,
The macro below is the best I think you're going to get.
Without a LOT of programming, no macro is going to take G1003 in C and put
it down 2 rows to match G1003 in A and take G1005 in C and insert a row below
G1004 in A and insert the value.

'/===============================================/
Sub CompareIt1()
Dim cell As Range, rngSelection As Range

On Error GoTo err_Sub

Range("b1:b9").Select

Set rngSelection = Selection

For Each cell In rngSelection
If Len(Trim(cell.Value)) > 1 And Len(Trim(cell.Offset(0, 2).Value)) > 1
Then
If Right(Trim(cell.Value), 4) < Right(Trim(cell.Offset(0, 2).Value),
4) Then
cell.Offset(1, 0).EntireRow.Insert

Range(cell.Offset(0, 2).Address & ":" & cell.Offset(0, 3).Address).Cut
cell.Offset(1, 2).Select
ActiveSheet.Paste
Else
If Right(Trim(cell.Value), 4) > Right(Trim(cell.Offset(0, 2).Value),
4) Then
cell.Offset(1, 0).EntireRow.Insert
Range(cell.Offset(0, 0).Address & ":" & cell.Offset(0,
1).Address).Cut
cell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End If
End If
Next cell

For Each cell In rngSelection
If Len(Trim(cell.Value)) <> 0 And Len(Trim(cell.Offset(0, 3).Value)) <>
0 Then
If Trim(cell.Offset(0, 1).Value) <> Trim(cell.Offset(0, 3).Value) Then
cell.Offset(0, 4) = "DIFF!!"
End If
Else
cell.Offset(0, 5) = "BLANK!!!"
End If
Next cell

exit_Sub:
On Error Resume Next
rngSelection.Range("A1").Select
Set rngSelection = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: CompareIt1 - " & Now()
Resume Next

End Sub
'/===============================================/


HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Balfear" wrote:

>
> Gary L Brown Wrote:
> > Formula in Cell E2:
> > =IF(LEN(F2)=0,IF(B2<>D2,"DIFF!",""),"")
> >
> > Formula in Cell F2:
> > =IF(OR(LEN(A2&B2)=0,LEN(C2&D2)=0),"BLANK!","")
> >
> > HTH.
> > --
> > Gary Brown
> > gary_brown@ge_NOSPAM.com
> > If this post was helpful, please click the ''Yes'' button next to ''Was
> > this
> > Post Helpfull to you?''.
>
> Hi Gary Brown,
>
> i have try the codes but from coloumn A and C is there are different it
> cant drop to the next row.
>
> below are the codes i have try:
>
> Sub CompareIt1()
> Dim cell As Range
> Range("a1:a1000").Select
> For Each cell In Selection
> If Len(cell) > 1 And Len(cell.Offset(0, 2)) > 1 Then
> If Val(Right(cell, 4)) < Val(Right(cell.Offset(0, 2), 4)) Then
> Range(cell.Offset(0, 2), cell.Offset(0, 3)).Insert Shift:=xlDown
> Else
> If Val(Right(cell, 4)) > Val(Right(cell.Offset(0, 2), 4)) Then
> Range(cell, cell.Offset(0, 1)).Insert Shift:=xlDown
> End If
> End If
> Next cell
> For Each cell In Selection
> If cell.Text = "" Or cell.Offset(0, 3) = "" Then GoTo NextOne
> If cell.Offset(0, 1) <> cell.Offset(0, 3) Then
> cell.Offset(0, 4) = "DIFF!!"
> End If
> NextOne:
> Next cell
> End Sub
>
> but when it reach a alpha and 3 numerical the marco cannot be used. and
> also im not sure how to make it blank when either column A and column C
> do be empty.
>
> please advice.
>
> Regards
> Balfear
>
>
> --
> Balfear
> ------------------------------------------------------------------------
> Balfear's Profile: http://www.excelforum.com/member.php...o&userid=35441
> View this thread: http://www.excelforum.com/showthread...hreadid=552147
>
>