I have a mailing list that need to be kept in order of first contact. Columns are name, phone, email, first contact, second contact, etc. All of the answers I seem to find online are comparing one cell or one column to another. I need to know how to identify if an email address is ever duplicated in the list. It needs to compare each email that already exists to every email that is ever entered to infinity, because the list is always expanding.
—–
Erica
MacMost Q&A Forum • View All Forum Questions • Ask a Question
How Do I Identify Duplicate Cells In One Column In Numbers?
Comments: 7 Responses to “How Do I Identify Duplicate Cells In One Column In Numbers?”
Comments Closed.
How about using COUNTIF? If the email addresses are in column C, then add another column, say F, where the formula in row 2 is =COUNTIF(C,C2). Then paste that formula into all of the cells in column F. A 1 should appear if the email address in that row is only in column C once, but a 2 or more will be there if it is a duplicate. Now you can spot them easily.
You can also nest the COUNTIF into a logical function if you like, to get a TRUE or FALSE.
=(COUNTIF(C,C2)>1)
Or, you can use Conditional Highlighting to make the cell red if it is greater than 1.
Thank you that is very helpful but now if I copy/paste that formula into every cell in the column that is identifying the duplicates, it is comparing them all to the data in C2 instead of C*, whatever row number that happens to be. Is there a way to easily populate that formula for every row number or do I have to manually change the formula for each line?
When you copy =COUNTIF(C,C2) from F2 to F3, it should paste as =COUNTIF(C,C3). When you paste it in all of the cells in that row, the row number should be relative to the row. So COUNTIF(C,C4), COUNTIF(C,C5), COUNTIF(C,C6), etc. Only if you force the row number to be absolute (persistent) will it stay C2.
This is working great, thanks much. Is there a way to identify duplicate cells across multiple pages in one spreadsheet? The application here is I'm trying to move unsubscribers to a 2nd page to keep things tidy but I want to identify if I add in an email address to the list (pg 1) that has already opted out (pg 2).
Erica: It works the same way. Just refer to the cells/rows in the table you want. Click and drag to select those cells instead of typing the references if you are unsure as to what to type.
It's not letting me drag cells between sheets. I want D2, D3, etc. on Sheet 2 to reference all of column D on Sheet 1. Do you know how to type that?
Erica: You don't drag between sheets. You drag to select. Say you type the formula =SUM( and stop there. Then drag to select a range of cells. Then add the ). That inserts the text for that range. Try it in the same table. Then once you get the hang of that, try selecting a range in another table.