Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates in 2 worksheets
I have 2 worksheets of data for a mailing list I'm doing. The first worksheet has criteria pulled from one set of software and the other is from a separate system. What I want to do is find which account numbers from Sheet 2 appear on Sheet 1. Those that match get deleted from both Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded. I'll then use Sheet 1 for one mailing, and Sheet 3 for the other. Any ideas? -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=541624 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates in 2 worksheets
Put both datasets in MS Access. Let's call one Table1 and the other Table2.
Assuming the account# column is named AcctNum, use a query like this to get duplicates: Select T1.* FROM Table1 T1 INNER JOIN Table2 T2 on T1.AcctNum = T2.AcctNum These are all your duplicates To get the remaining records from T1, the ones that are not duplicates, use this query Select T1.* FROM Table1 T1 LEFT JOIN Table2 T2 on T1.AcctNum = T2.AcctNum WHERE T2.AcctNum IS NULL "darkwood" wrote: I have 2 worksheets of data for a mailing list I'm doing. The first worksheet has criteria pulled from one set of software and the other is from a separate system. What I want to do is find which account numbers from Sheet 2 appear on Sheet 1. Those that match get deleted from both Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded. I'll then use Sheet 1 for one mailing, and Sheet 3 for the other. Any ideas? -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=541624 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicates in 2 worksheets
This is something that I have setup an index column on both sheets with a
distinctive character or pattern of characters in between each cell for the different columns that's getting compared. Example: Columns A, B, C and D are getting compared for the rows of 5:3000 on "Sheet1" and "Sheet2" In an empty column, let's say you have to go all the way out to column AA, you would type in the following formula in Cell AA5: =A5 & "|" & B5 & "|" & C5 & "|" & D5 I'm also assuming that both sheets are setup in the same format, but if not, adjust accordingly. This formula would be put on both sheets. Now, for this second part, you will be using the MATCH function, which the more records there are, the more costly it gets to be time wise as such a function can cause the system spend an extra amount of time calculating. It isn't noticeable if you have just a few of these, but with a lot of them, it's very noticeable. Therefore, before you continue, you may want to set your calculation mode to "Manual" on a temporary basis, so as you aren't waiting for it each and every time you make a change in Excel. Some might say the background calculation that been put into Excel starting with the 2000 version alleviate this issue. However, in my experience that I have found, sometimes, it works like it suppose to, but other times, it doesn't work like it suppose to. Like it seems to work properly most of the time on both computers that I work on at work using Excel 2002, but for the system at home using Excel 2003, it rarely works properly. Anyhow, here's the formula you will put in Sheet1!AB5 (or adjust accordingly like you did with the first formula depending on where you put the first formula): =IF(ISERROR(MATCH(AA5,Sheet2!AA:AA,0)),0,1) After you have put in your formula, go to the very bottom of the list, move the active cell to column AA of that last row on "Sheet1". Hold down the Shift key, and press the right arrow key. While still holding down the Shift key, also hold down the Ctrl key, and press the up arrow key. Now, press and hold the Ctrl key, and then press the letter D for Fill Down. Make sure you have the first formula on Sheet2 as well. If calculation mode has been set to Manual, press and hold down the Shift key, and press F9 to calculate that worksheet (Sheet2 first, then Sheet1 in this case). This may take minutes depending on how many records you have of both sheets as well as what your system resources are and how much of those resources are being used. The reason you calculate Sheet2 first is cause none of the formulas on Sheet2 is dependent on Sheet1, while some of the formulas on Sheet1 is dependent on the calculated values of Sheet2. Now, you can convert the formulas to values by doing a copy (Ctrl-C), and then paste special as values (Alt, E, S, V, Enter). Sort the data by the AB column, then what other order you want after that. Note, all of the rows with a 1 in the AB column are the ones that are found on both, Sheet1, and Sheet2. The ones with a 0 in the AB column, are not, and it sorted so as the duplicates are at the bottom of the list, so you can select those records on Sheet1 and copy them (Ctrl-C), paste to Sheet3 (Ctrl-V), go back to Sheet1 and delete those records (Either Delete or press and hold the Ctrl key while pressing the numpad minus key, then down arrow one time may be needed to move rows up). All that's left to do at this point, delete Sheet2, and if you want, delete columns AA:AB on Sheet1 as those 2 columns aren't needed anymore (or which ever 2 columns you use for these formulas on Sheet1). It's not the most efficient way of doing it, but it's the quick and dirty way of doing it. Unless you plan on doing something like this on a more regular basis, this would probably serve you best, as the more efficient way of doing it has more of a setup time done via macros. -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 "darkwood" wrote in message ... I have 2 worksheets of data for a mailing list I'm doing. The first worksheet has criteria pulled from one set of software and the other is from a separate system. What I want to do is find which account numbers from Sheet 2 appear on Sheet 1. Those that match get deleted from both Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded. I'll then use Sheet 1 for one mailing, and Sheet 3 for the other. Any ideas? -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=541624 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help finding and summing across worksheets | Excel Worksheet Functions | |||
excel duplicates on 2 worksheets | Excel Discussion (Misc queries) | |||
Finding Data in multiple worksheets | Excel Discussion (Misc queries) | |||
Finding Duplicates | Excel Worksheet Functions | |||
Finding and Deleting duplicates in a column | Excel Worksheet Functions |