| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert blank cell and labelling if difference in comparsion
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 ''Wa this Post Helpfull to you?''. 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 -- Balfea ----------------------------------------------------------------------- Balfear's Profile: http://www.excelforum.com/member.php...fo&userid=3544 View this thread: http://www.excelforum.com/showthread.php?threadid=55214 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert blank cell and labelling if difference in comparsion
-- 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How calculate a difference btwn 2 TextBox and insert the result inthe appropriate cell | Excel Discussion (Misc queries) | |||
| how do I change cell labelling from R1C1 to A1? | Excel Discussion (Misc queries) | |||
| Labelling a row so it can be used as an offset for sum, difference or range | Excel Discussion (Misc queries) | |||
| store cell contents and cell address for comparsion & suming | Excel Programming | |||
| Cell Comparsion (a bug?) | Excel Programming | |||
Linear Mode
