-
Comparing two columns with empty rows but same data and delete
Hi all, I am quite at the basic level in Excel VBA and i will really be thankful if someone can take sometime to help me out
I have data in two sheets, termed Sheet 1 and Sheet 2 as shown below. The idea is that when the command button is pressed the macro should execute the following:
1. Search for the Product ID in Sheet 1 from Sheet 2, if it finds it in Sheet ,
It will only change the Amount found on Row 3 in Column C in Sheet 1 with the Amount on Sheet 2 and Column F. The output result of how it should look is shown below
2. If it does not find a Product ID in Sheet 1 that is found in Sheet 2, it should add it in Sheet 1 in Ascending order and have the same format in Sheet 1.
3. If it finds a Product ID in Sheet 1 that is not in Sheet 2, it should delete the corresponding rows. That is ONLY the first two rows connected to every Product ID
4. If it finds a Product ID in Sheet 1 and Sheet 2 with the same amount, it should not alter it (move to next)
Sheet 1
Product ID |
Color/Type |
Amount |
10001 |
White |
|
|
Cotton |
1000 |
10003 |
Blue |
|
|
Cotton |
1500 |
10004 |
Black |
|
|
Cotton |
4500 |
|
|
|
Sheet 2
A-------------------B--------------C-----------D----------E------------F-------
10001 |
White |
500 |
500 |
1000 |
2000 |
10002 |
Purple |
1800 |
900 |
300 |
3000 |
10003 |
Blue |
1500 |
0 |
0 |
1500 |
10004 |
Black |
2100 |
250 |
1650 |
4000 |
Output result = Notice that the Word Cotton does not change even with a new product id added
You will also notice that the Amounts replaced are from Column F
Product ID |
Color/Type |
Amount |
10001 |
White |
|
|
Cotton |
2000 |
10002 |
Purple |
|
|
Cotton |
3000 |
10003 |
Blue |
|
|
Cotton |
1500 |
10004 |
Black |
|
|
Cotton |
4000 |
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules