Compare Two Value in Different Sheets

I'm a newbie in the vba.
Could someone show me how to do a macro if sheet 1 & sheet 2 have different value then copy into sheet 3.

Condition copy in sheet 3 is if sheet1(No) = Sheet2(No) & sheet1(Amount) <> sheet2(Amount)
Anyone could help me this

Sheet 1 || Sheet 2 ||
No |Amount || No | Amount | Time
E10001 | 1000 || E10003 | 1000 | 12am
E10002 | 1000 || E10005 | 1000 | 12am
E10003 | 1000 || E10001 | 1000 | 12am
E10004 | 2000 || E10004 | 1000 | 12am
E10005 | 3000 || E10002 | 1000 | 12am

Sheet 3
|| Name | Amount | Time|
|| E10005 | 1000 | 12am|
|| E10004 | 1000 | 12am|

Hi

Try this

Please Login or Register  to view this content.

rylo

Hi

Given that the data isn't sorted, your first example would probably bring back pretty much every item.

Are you trying to extract on different output sheets:
(a) all items that match on both sheets
(b) items from sheet2 that don't match the same item on sheet1
(c) items that are on sheet2, but aren't on sheet1

or some other combinations?

rylo

Hi

See how this goes.

Please Login or Register  to view this content.

If this still doesn't give you all your required outputs, can you please create and attach an example file showing exactly what outputs should be returned, and why. Makes things much easier that having to try to replicate your data.

rylo

Hi Rylo,

Please refer to the attachment. It was the excel file i need to copy to another sheets base on the condition below.
1st Condition:-
If Label No (Sheet1) = Label No(Sheet2) & amount (sheet1) = amount (sheet2) than
-copy sheet2 detail into sheet3
2nd Condition:-
If Label no (Sheet1) NOT EXIST IN Label No(Sheet2) than
- copy sheet2(label no not exist in sheet1) detail into sheet4

3th Condition:-

IF Label no (Sheet1) = Label no(Sheet2) & amount (sheet1) NOT EQUAL Amount (sheet2) than
-copy sheet2 detail into sheet 5

Hi

How do the results obtained from the code in #7 differ from what you want to see (other than the output on different sheets, and the number of columns of output).

rylo

Hi

Ok, I've updated it for you revised structure, and put the results into sheet3 and sheet5. I can't see how your condition 2 can work as if the data from sheet1 isn't in Sheet2, then how can you transfer data from Sheet2? Shouldn't it be the sheet1 data?

Please Login or Register  to view this content.

rylo

Hi

1) If this is to run on 2003, then you will have to revise the countifs formula and make it work with sumproduct. Manually build the sumproduct formula, and then convert it for the macro.

2) what is your business process for handling duplicate label numbers? If one pair matches, then does it go on the matched sheet and the non matching one goes on the non matching sheet? Explain exactly what you want to happen for this situation.

rylo

Hello gingank, and welcome to the forum.

Sorry to interrupt but please notice that I have removed whole post quotes from your posts. Please read the following and keep it mind for your future posts. Thanks.

Rule 12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Hi

here goes

Please Login or Register  to view this content.

rylo

Hi guys, I think the problem I have is closely related to this. Ive been pulling my hair out over this one. Basically Im trying to track user deletions and adds. I have column A that contains sequential values (like an Id number) and the other columns contain user data. To track deletions and adds I have implemented the following:

1. when the workbook is open, copy the original sheet to a new sheet in the same workbook.
2. allow the user to make changes
3. determine the total number of rows that contain user data (i.e. exclude rows where only a rowID value exists) in each sheet
4. in a third sheet, copy the rowId values for which there is user data from sheet1 to column A in sheet3
5. copy the rowID values for which there is user data from sheet2 to column B in sheet3

What I now need to do is compare the two columns of sheet 3

If there are rowID values present in column A of sheet3 but not in Column B of sheet 3, then there have been additions made and I need to note the rowID value
If there are rowID values present in column B of sheet3 but not in Column A of sheet 3, then there have been deletions made and I need to note the rowID value

So its just a straight up comparison but I cant seem to get it right.

Here's an example of the two columns and what their values might be

colA colB

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
13 11
14 12
16 13
17 14
18 16
24 17
18
23

so in this case, the comparison should pick up that rowID's 11,12 and 23 are to be deleted and that rowID 24 has been added.

Any help with this one would save me from going insane.

Thanks in advance

Nevermind I found the solution. For those who are interested, I used variations of the Match method in the Application class. The code below will compare the values in column A of two sheets and paste matches in column A of a third sheet, those which appear in sheet 1 but not sheet 2 into column B of the third sheet, and those which appear in sheet 2 but not sheet 1 into column3 of the third sheet

Please Login or Register  to view this content.

@ gwunta Nice to see that you found a solution to your problem, however, you should have started your own thread instead of asking for help in someone else's thread. Please read the following and keep it in mind for next time. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.