Want to highlight a helpful answer? Upvote!
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
I need to compare lists of text data (.numbers or .csv) to find duplicate or unique entries. I can't find anything in Numbers or other tool db online. Any ideas? I don't want to spend much $$$.
MacBook Pro (13-inch Late 2011), Mac OS X (10.7.3)
You didn't give much information to go on but I can give you an example.
If column A of your spreadsheet is a list of text data, then in column B you could use in each row the formula =COUNTIF(A,A) . If the count is 1 then it is unique, if >1 then there are duplicates of that particular text string.
You could get more sophisticated by using the formula =COUNTIF(A$2:A2, A2) in cell B2 and fill that down to the end of column B. I am assuming your list starts at row 2. The first occurrence of a particular text string will be a 1, any repeats will be >1.
And you can put the COUNTIF inside an IF statement =IF(COUNTIF(A$2:A2, A2)>1,"DUPLICATE","") to make it easier to see the duplicates.
For a result I want one table complete with all the records from both tables without duplicates.
I've tried the "count" function but don't have the experience yet to tweak it properly.
Thanks
Does it matter if the columns have header rows?
Bell,
Yes, it matters if you have a Header Row or not. It can be made to work either way, but it's good practice and it makes programming easier if you put your column titles is a Header row, and if you have summary statistics in the same column as the data, those stats should also be in header or footer rows.
How much manual work are you willing to do? It would be pretty easy to isolate the unique strings in one table and add them to the other table, then you could Copy them from one and Paste to the other.
Jerry
Job done. I used your first suggestion to identify the dupes. Most were duplicated only once but several were 2x, 3x or 4x. After handling those I sorted by the relevant field and added a new column in which I numbered the rows 1, 2, 1, 2, etc, throught the table so that each duplicated record was assigned first "1" then "2". I used this column to sort and deleted all the "2's". Fixed.
Thank you.
Thanks for the feedback. I think most of the credit should go to Badunit in this case. If you follow this forum area you will see the Countif function used in this way quite often. With a little more complexity it's possible to pull out a list of distinct strings in a summary table, but as you have seen, a combination of simple formulas and a little sorting, deleting and copying you can get there pretty quickly.
Jerry
Need to compare lists.
