Compare and Delete Excel

[dedo8816]

Hi,

Need some help, I am trying to compare two lists on two different sheets in excel.

The two lists are almost identical, 2000 lines long each +/-. Im looking for differences and am not bothered about lines that are the same. Is there a way I can get excel to compare the two lists completely and display the differences on a third sheet?

Would like to turn this into a macro

Thanks

 

[kjv1611]

You could use either VLOOKUP or MATCH and INDEX combination to do this, formula wise. You could do it via your own custom-built VBA function as well.

If you are saying you want it as a macro, then I'm assuming you get this on a regular basis from another source, so you'd need to manually add the formulas/functions each time. Yes, that can be done as well.

My first guess at it without taking much thought is to use VLOOKUP in each list to show the match where each item exists in the other list. However, I mentioned MATCH and INDEX, b/c using one or a combination of those may work better in this scenario.

Another thing you could do, since you're only dealing with about 2,000 records - therefore performance likely will not be an issue - would be to do this:
1. Add an extra column that says "Sheet" or whatever you want to call it - basically showing which list it came from.
2. Copy each of the sheets to the 3rd sheet, thus combinine them... so copy/paste one, then copy/paste the other just below, and make sure you don't wind up with 2 header lines.
3. then you can use formulas, conditional formatting, etc, to show which ones exist in both places. Or if you just want to keep a unique values list, regardless of source, you can use the Data - Remove Duplicates functionality in Excel. I don't remember off-hand how to add that into a macro, but I'm sure it's possible.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

hi,

I'd use MS Query faq68-5829
I have two tables with a headgin of List on each sheet (Sheet1 & Sheet2)

Select List

From
(
SELECT `Sheet1$`.List
FROM `C:\Documents and Settings\ii36250\My Documents\Samlecompare.xlsx`.`Sheet1$` `Sheet1$`

union all

SELECT `Sheet2$`.List
FROM `C:\Documents and Settings\ii36250\My Documents\Samlecompare.xlsx`.`Sheet2$` `Sheet2$`
) 
where Count(*)=1

group by list

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Yes, MSQuery would be the best route, as Skip said. One of these days, I'll remember that option. I use it VERY rarely, b/c 99% of my data comparisons where that would help are done on SQL Server anyway.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57