compare row's if value of corresponding column's not equal insert them

Question

i have two grouped of columns.one of them is subset of another.i want to write a macro compare them and if they were not equal insert second one to first one. algorithm ode:

if (code-1)<>(code-2) AND (serial-1)<>(serial-2) AND (amount-1)<>(amount-2) then
 (code-1)==(code-2) AND (serial-1)==(serial-2) AND (amount-1)==(amount-2)

and i want inserted row be marked.

the table i have is like below:

code-1  serial-1    amount-1    code-2  serial-2    amount-2
1         11          111          1      11          111
3         33          333          1      11           11
                                   2      22           22
                                   3      33           33
                                   3      33          333

the result i looking for is below:

code-1  serial-1    amount-1    code-2  serial-2    amount-2
   1     11           111          1      11          111
   3     33           333          1      11          11
   1     11           11           2      22          22
   2     22           22           3      33          33
   3     33           33           3      33         333

Ygor Yansz · Accepted Answer · 2015-03-18T11:06:41.507

0

UPDATE

This new code will create 2 auxiliar columns to run a vlookup to eliminate all the one that dosen't match the criteria when the macro run to select wich cells to copy and also delete these 2 columns after it's done.

But remenber i wrote the code according to what you gave me, so it will only work if with that arrange, columns A to C and columns D to F

If it works please remember to mark this code as the answer very appreciated =]

new code:

Sub yoursub()

Dim lstr1        As Long
Dim lstr2        As Long
Dim mark1        As Long

lstr2 = Sheets(1).Cells(Rows.Count, 4).End(xlUp).Row

lstr1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1

mark1 = lstr1 - 1

Range("A1").EntireColumn.Insert
Range("A1").EntireColumn.Insert

For i = 2 To mark1

Cells(i, 2) = Cells(i, 3) & "-" & Cells(i, 4) & "-" & Cells(i, 5)

Next

For j = 2 To lstr2

Cells(j, 1).FormulaR1C1 = "=VLOOKUP(RC[5]&""-""&RC[6]&""-""&RC[7],C[1],1,FALSE)"

Next

For j = 2 To lstr2

If WorksheetFunction.IfError(Cells(j, 1), "Error") = "Error" Then

Range(Cells(j, 6), Cells(j, 8)).Copy
Cells(lstr1, 3).PasteSpecial xlPasteValues
lstr1 = Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row + 1

End If

Next

Range("A1").EntireColumn.Delete
Range("A1").EntireColumn.Delete

End Sub

edited Mar 18 '15 at 11:06

answered Mar 17 '15 at 14:19

Ygor Yansz

176
1
4
12

@YgorYansz.thnx.for your respond.did you test that example with your macro.i test but unfortunately that not work as well for example.i think it need to be developed. – shahram Mar 17 '15 at 14:37
I've created a file here and tested, and it worked fine, i used the exac same lines and values you gave and it worked – Ygor Yansz Mar 17 '15 at 14:39
did you changed the sheets() into yours? and changed the collumns? – Ygor Yansz Mar 17 '15 at 14:40
@yagoryansz i modify Range("D" & j & ":F" & j).Copy it work.but still.doesnt prevent insert repeated row's.and my row's after that be duplicateed.i still exploring code.to find problem. – shahram Mar 17 '15 at 15:01
@shahram it looks like i'll have to make another code, this one really has a issue that i can't find, i'll think about another aproach and post it soon sorry about that – Ygor Yansz Mar 17 '15 at 15:38
@shahram i took another aproach and that one really works for me, i've updated the answer with the new code, pls try and give me the feedback – Ygor Yansz Mar 18 '15 at 10:58

compare row's if value of corresponding column's not equal insert them

1 Answers1