Insert blank cell and labelling if difference in comparsion

B

Balfear

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
 
G

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.
 
B

Balfear

Gary said:
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 ''Wa
this
Post Helpfull to you?''.
Click to expand...

Hi Gary Brown,

i have try the codes but from coloumn A and C is there are different i
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)) The
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. an
also im not sure how to make it blank when either column A and column
do be empty.

please advice.

Regards
Balfea
 
G

Gary L Brown

--
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?''.
 
G

Gary L Brown

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,
 
Top