delete data for sheet contains 7000 rows based on two columns same values

Ali M

Active Member
Joined
Oct 10, 2021
Messages
287
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi

I want matching data bewteen two sheets based on column C,D contains the same data , the second sheet should delete data based on first sheet

if the same values for column C,D are existed in second sheet with matching first sheet should keeping otherwise should delete the others

as you note in first sheet there is duplicate items also second sheet , if the second sheet contains duplicates items and the same values but the duplicates are more than firs sheet then should delete it and just keep duplicates based on first sheet , and if there is new in second sheet but not existed in first sheet also should delete and if if there is new items in first sheet but not existed in second sheet should be add to second sheet all of this depend on column C,D except one case if there is the same values in column C,D but different item then should delete it .
so in other meaning
1- different values in column C,D with the same item in column B should delete it.
2- same item & values shouldn't delete it ,but doesn't duplicates more than first sheet.
3- same values for column C,D but different item in column B should delete it
4- different values and item should delete it
5- different item and different values in first sheet and not existed in second sheet should add second sheet .
6- same item & values in first sheet and not existed in second sheet should add to second sheet
7- differen item & values for column C,D should delete from second sheet
thanks


DAILY.xlsm
ABCDE
1DATECODEBRANDQTYQTY1
211/21/2020CC1FOOD1234
35/21/2020CC2FOOD278
45/22/2020CC3FOOD398
55/23/2020CC4FOOD45645
65/24/2020CC5FOOD534
75/25/2020CC6FOOD623467
85/26/2020CC7FOOD778
95/27/2020CC8FOOD8875
105/28/2020CC9FOOD9675
115/29/2020CC10FOOD1050087
125/30/2020CC1FOOD178965
135/31/2020CC2FOOD2456
146/1/2020CC3FOOD3678
156/2/2020CC4FOOD445667
166/3/2020CC5FOOD578999
176/4/2020CC6FOOD6234
186/5/2020CC7FOOD778954
196/6/2020CC8FOOD856
206/7/2020CC9FOOD945676
216/8/2020CC11FOOD1145676
FIRST



DAILY.xlsm
ABCDE
1DATECODEBRANDQTYQTY1
25/29/2020CC10FOOD1050087
312/21/2020CC1FOOD1234
45/22/2020CC3FOOD39998
55/23/2020CC4FOOD45645
65/24/2020CC5FOOD534
711/21/2020CC1FOOD1234
85/25/2020CC6FOOD623467
95/26/2020CC7FOOD778
105/27/2020CC8FOOD8875
115/28/2020CC9FOOD9675
125/21/2020CC2FOOD278
135/30/2020CC1FOOD178965
145/31/2020CC2FOOD2456
156/1/2020CC3FOOD3678
166/2/2020CC4FOOD445667
176/3/2020CC5FOOD578999
186/4/2020CC6FOOD6234
196/5/2020CC7FOOD778954
206/6/2020CC8FOOD856
216/7/2020CC19FOOD1957
226/16/2020CC8FOOD856
236/16/2020CC8FOOD8200
246/7/2020CC19FOOD1957
256/8/2020CC20FOOD2058
266/9/2020CC21FOOD2159
276/10/2020CC22FOOD2260
286/11/2020CC23FOOD2361
296/12/2020CC24FOOD2462
306/13/2020CC25FOOD2563
SECOND



result
DAILY.xlsm
ABCDE
1DATECODEBRANDQTYQTY1
211/21/2020CC1FOOD1234
35/21/2020CC2FOOD278
45/22/2020CC3FOOD398
55/23/2020CC4FOOD45645
65/24/2020CC5FOOD534
75/25/2020CC6FOOD623467
85/26/2020CC7FOOD778
95/27/2020CC8FOOD8875
105/28/2020CC9FOOD9675
115/29/2020CC10FOOD1050087
125/30/2020CC1FOOD178965
135/31/2020CC2FOOD2456
146/1/2020CC3FOOD3678
156/2/2020CC4FOOD445667
166/3/2020CC5FOOD578999
176/4/2020CC6FOOD6234
186/5/2020CC7FOOD778954
196/6/2020CC8FOOD856
206/7/2020CC9FOOD945676
216/8/2020CC11FOOD1145676
RESULT
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I would suggest create helper columns on both sheets to test the rules you want and then deleting rows will be a easy.
 
Upvote 0
Is it the same statement:

1- Filter rows of sheet1 those all 5 columns match with sheet2
2- Update sheet2 with new filterred result from sheet1.

???
 
Upvote 0
Hi bebo,
keep the original data in sheet1 should filter sheet 2 based on sheet1
sheet2 should update if I add or change in sheet1 or sheet2
in other meaning result implement for sheet2 based on matching with sheet1
 
Upvote 0
for correction should be columns D,E not C,D because depends on values columns D,E
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top