Deleting Rows from a Master Sheet that have the same info in a second sheet (based on values in 2 columns)

merskamp2

New Member
Joined
Dec 8, 2016
Messages
1
I have a Workbook containing 4 sheets.
1) A Master sheet which is everything we've ever done. In it: Column E has a date and Column F has a persons name (which is often duplicated as you go down the column - we have 73 unique names and 800 rows)
2) A Sales sheet that contains some info from the first sheet , much smaller than than the master list. In this sheet Column E has a date again, and this time its column F that has a name (often duplicated)
3) A Buyside sheet which is basically the same set up as the sales sheet. Column E has a date and F has a Name.
4) A copy of the Master Sheet

The goal is to:
a) Look at the buyside sheet. If a Name and Date in one row matches exact a name and its corresponding date in the master sheet (from any row) we wish to delete that row from the master sheet. Both Columns E and F on the Buside Sheet must exactly Columns E and Column G from the master sheet on any row. So if Row 10 in the Buyside sheet has a Column E and F value that match exactly a Column E and column G value (both must match) on any row within the master then we remove that row from the Master sheet

b) Same for the sell side sheet - remove its corresponding items from the main sheet as above.

Eventually the master sheet will be pared down as we can remove items from the buyside and sellside sheets and then work out which items are left that weren't in either.

in my samples below Lines 5-8 of the Sellside sheet match up to lines 11-14 of the master sheet and thus I'd remove them from the master sheet.

Master sheet sample:
Excel 2013 32 bit
A
B
C
D
E
F
G
H
I
J
K
2
Irrelevant
485000​
#​
#​
4-Jan-16​
JanuaryFred bloggsIowaIowaIrrelevant
1/4/2016 0:00​
3
Irrelevant
610000​
#​
#​
4-Jan-16​
JanuaryJames BrownWest IowaIowaIrrelevant
1/4/2016 0:00​
4
Irrelevant
669842​
#​
#​
4-Jan-16​
JanuaryJohn NorthIowaIowaIrrelevant
1/4/2016 0:00​
5
Irrelevant
272000​
#​
#​
4-Jan-16​
JanuaryDolph SroggetIowaIowaIrrelevant
1/4/2016 0:00​
6
Irrelevant
545000​
#​
#​
5-Jan-16​
JanuaryPaul MersonIowaIowaIrrelevant
1/5/2016 0:00​
7
Irrelevant
362500​
#​
#​
5-Jan-16​
JanuaryDennis BerukeIowaIowaIrrelevant
1/5/2016 0:00​
8
Irrelevant
375000​
#​
#​
5-Jan-16​
JanuaryDenis PatoisIowaIowaIrrelevant
1/5/2016 0:00​
9
Irrelevant
362500​
#​
#​
5-Jan-16​
JanuaryRich JamesIowaIowaIrrelevant
1/5/2016 0:00​
10
Irrelevant
279900​
#​
#​
6-Jan-16​
JanuaryBill BloueNorth IowaIowaIrrelevant
1/6/2016 0:00​
11
Irrelevant
322000​
#​
#​
6-Jan-16​
JanuaryFred bloggsIowaIowaIrrelevant
1/6/2016 0:00​
12
Irrelevant
850000​
#​
#​
6-Jan-16​
JanuaryJames BrownIowaIowaIrrelevant
1/6/2016 0:00​
13
Irrelevant
235000​
#​
#​
6-Jan-16​
JanuaryMadeliene AllclearIowaIowaIrrelevant
1/6/2016 0:00​
14
Irrelevant
318500​
#​
#​
7-Jan-16​
JanuaryEmekkka JohnsonNorth IowaIowaIrrelevant
1/7/2016 0:00​

<tbody>
</tbody>
Sheet: Master

<tbody>
</tbody>


Buyside sheet sample:Excel 2013 32 bit
A
B
C
D
E
F
2
Iowa
8​
Sell
00:00.0​
12/3/2015​
Fred bloggs
3
Iowa
8​
Sell
00:00.0​
1/8/2016​
James Brown
4
Iowa
8​
Sell
00:00.0​
1/8/2016​
John North
5
Iowa
8​
Sell
00:00.0​
1/6/2016​
Fred bloggs
6
Iowa
8​
Sell
00:00.0​
1/6/2016​
James Brown
7
Iowa
8​
Sell
00:00.0​
1/6/2016​
Madeliene Allclear
8
Iowa
8​
Sell
00:00.0​
1/7/2016​
Emekkka Johnson
9
Iowa
8​
Sell
00:00.0​
1/7/2016​
Paul Merson
10
Iowa
8​
Sell
00:00.0​
2/5/2016​
Dennis Beruke
11
Iowa
8​
Sell
00:00.0​
2/16/2016​
Denis Patois
12
Iowa
8​
Sell
00:00.0​
1/6/2016​
James Brown
13
Iowa
8​
Sell
00:00.0​
1/6/2016​
Madeliene Allclear
14
Iowa
8​
Sell
00:00.0​
1/7/2016​
Emekkka Johnson
15
Iowa
8​
Sell
00:00.0​
1/7/2016​
Paul Merson
16
Iowa
8​
Sell
00:00.0​
2/5/2016​
Dennis Beruke
17
Iowa
8​
Sell
00:00.0​
2/16/2016​
Denis Patois
18
Iowa
8​
Sell
00:00.0​
2/18/2016​
Rich James
19
Iowa
8​
Sell
00:00.0​
2/25/2016​
Bill Bloue
20
Iowa
8​
Sell
00:00.0​
2/26/2016​
Fred bloggs
21
Iowa
8​
Sell
00:00.0​
2/26/2016​
James Brown
22
Iowa
8​
Sell
00:00.0​
2/29/2016​
John North

<tbody>
</tbody>
Sheet: 2016 EAR - Listings

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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
Top