ExcelBanter - Insert blank cell and labelling if difference in comparsion

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Insert blank cell and labelling if difference in comparsion (https://www.excelbanter.com/excel-programming/364338-insert-blank-cell-labelling-if-difference-comparsion.html)

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.....................B LANK!!!
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.....DIF F!!!
ZJ8500.......1234561...ZJ8501......1234571.....DIF F!!!

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

Insert blank cell and labelling if difference in comparsion

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

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

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