Find and Copy Unique Entries (Excel 2003 SP2)

Topic

New Reply

Hi all,

I have 2 workbooks from 2 different sources. I want to compare the entries in sheet 1 of these 2 workbooks and list or highlight all unique rows into sheet 2 of the these same workbooks via a macro.

Thanks in advance

regards, francis

Reply | Quote

Replies

There’s a free add-in for reconciliation of worksheets: Reconcile Wizard Add-In For Excel Free Download.

Reply | Quote

(Edited by sdckapr on 11-Nov-07 06:56. Added PS)

What do you consider “unique”?

From what I can tell all the entries in both lists are unique to not only the list but to the individual lists. The SMD list does have trailing spaces and spaces within the phrases that are not in the other list.

Even removing the spaces from both lists, the 2 lists are still unique in both places.

If you don not include Col D (the amount) in the comparison then there are matching items in both lists as well as matching items within each list.

Do you want the unique items (without comparing spaces) and without Col D? Is it unique items from just the other list or unique items within the same list as well?

Steve

PS some is just the difference between a blank and value of zero and some of the differences in the “Amount” are only a few cents but that makes them “unique”

Reply | Quote

Hi Steve,

Thank for looking into this. As this is for reconciliation purposes, Can the 2 list be compare without comparing the trailing spaces / spaces and have the unique entries extracted? if not, the data with trailing spaces and spaces in SMD file need to be remove and some of them requires to put a dash before the next character which would make it identical to the TSD file.

The column D is one of the required data for the reconciliation process for comparing to the other list. Hence, I am trying to extract rows of entries that are unique from the other list and not within the same list.

The blank on one list are suppose to be 0 value, therefore, we can either fill in a value of 0 for the blank or delete the value of 0 to make it blank. But the differences in Amount can’t be round even through there are few cents differences.

I remember that there aren’t many unmatched entries on both files when I did the process manually.

Thank for your assistance

regards, francis

Reply | Quote

[indent]

---

Can the 2 list be compare without comparing the trailing spaces / spaces and have the unique entries extracted?

---

[/indent]

Yes I mentioned this in post 676,005: “Even removing the spaces from both lists, the 2 lists are still unique in both places.” Removing the spaces still makes them all unique since the amounts never are the same (one list has cents the other does not)

[indent]

---

The column D is one of the required data for the reconciliation process for comparing to the other list. Hence, I am trying to extract rows of entries that are unique from the other list and not within the same list.

---

[/indent]

As mentioned this in post 676,005: this is all of them, though many are “similar” within a few cents of the “Amount” for some and other missing the amount completely.

[indent]

---

The blank on one list are suppose to be 0 value, therefore, we can either fill in a value of 0 for the blank or delete the value of 0 to make it blank.

---

[/indent]

This can be “fixed” and is only a minor point, since it is so few anyway.

[indent]

---

I remember that there aren’t many unmatched entries on both files when I did the process manually.

---

[/indent]

Then you must have looked at a separate list than you posted as all are unique (most if you presume a blank amount = 0). Or it could be true if you round the values (but you indicate:”But the differences in Amount can’t be round even through there are few cents differences.” which suggests otherwise.

Which rows in the TSD file do you think “match” a row in the SMD file? and Which rows in the MSD file do you think match a row in the TSD? This would help me understand what you call the same so I can understand your definition of “unique” since it appears to be different than mine.

Steve

Reply | Quote

Hi Steve,

After having gone thru this dataset, I think something is amissed as I remember that there are not so much differences between the two list.
I will post another dataset asap.

Thank in advance.

cheers, francis

Reply | Quote