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
<tbody>
</tbody>
<tbody>
</tbody>
Buyside sheet sample:Excel 2013 32 bit
<tbody>
</tbody>
<tbody>
</tbody>
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 | January | Fred bloggs | Iowa | Iowa | Irrelevant | 1/4/2016 0:00 |
3 | Irrelevant | 610000 | # | # | 4-Jan-16 | January | James Brown | West Iowa | Iowa | Irrelevant | 1/4/2016 0:00 |
4 | Irrelevant | 669842 | # | # | 4-Jan-16 | January | John North | Iowa | Iowa | Irrelevant | 1/4/2016 0:00 |
5 | Irrelevant | 272000 | # | # | 4-Jan-16 | January | Dolph Srogget | Iowa | Iowa | Irrelevant | 1/4/2016 0:00 |
6 | Irrelevant | 545000 | # | # | 5-Jan-16 | January | Paul Merson | Iowa | Iowa | Irrelevant | 1/5/2016 0:00 |
7 | Irrelevant | 362500 | # | # | 5-Jan-16 | January | Dennis Beruke | Iowa | Iowa | Irrelevant | 1/5/2016 0:00 |
8 | Irrelevant | 375000 | # | # | 5-Jan-16 | January | Denis Patois | Iowa | Iowa | Irrelevant | 1/5/2016 0:00 |
9 | Irrelevant | 362500 | # | # | 5-Jan-16 | January | Rich James | Iowa | Iowa | Irrelevant | 1/5/2016 0:00 |
10 | Irrelevant | 279900 | # | # | 6-Jan-16 | January | Bill Bloue | North Iowa | Iowa | Irrelevant | 1/6/2016 0:00 |
11 | Irrelevant | 322000 | # | # | 6-Jan-16 | January | Fred bloggs | Iowa | Iowa | Irrelevant | 1/6/2016 0:00 |
12 | Irrelevant | 850000 | # | # | 6-Jan-16 | January | James Brown | Iowa | Iowa | Irrelevant | 1/6/2016 0:00 |
13 | Irrelevant | 235000 | # | # | 6-Jan-16 | January | Madeliene Allclear | Iowa | Iowa | Irrelevant | 1/6/2016 0:00 |
14 | Irrelevant | 318500 | # | # | 7-Jan-16 | January | Emekkka Johnson | North Iowa | Iowa | Irrelevant | 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>
