I have 4 columns and ~10K rows. I wonder whether it is possible to extract the rows where the value in column (A in my case) occurs only once.
I don't want to filter out the duplicates, just the one-timers.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI have 4 columns and ~10K rows. I wonder whether it is possible to extract the rows where the value in column (A in my case) occurs only once.
I don't want to filter out the duplicates, just the one-timers.
I am assuming you have a header row, i.e. the first row contains the names of the columns and not actual data. If you do not have such a row, please insert a row at the top and put some dummy values as the headers.
=IF(COUNTIF($A:$A,$A3)>1,"",A3)
*If for some reason it doesn't you can either (a) drag the formula down manually or (b) copy the formula and paste in the area.
The unique records feature of Advanced Filtering will only give you which data sets or rows are unique.
To get what you need you can set up a special criteria for Advanced Filtering. First, make sure your data has unique headers.
Next, set up the formula. In my example below (also with 10k rows), the yellow cell contains a formula that serves as the Criteria for the Advanced Filter. It's important that it has a blank space above it.
=COUNTIF($A2:$D2,$A2)=1
It counts the number of times the first value in each row (column A) appears in each line, and returns TRUE
if it comes up only once. Note where the absolute signs ($) are placed. In this formula, $A2:$D2 & $A2 point to the first line of data, right below your headers.
Edit:
If you want to check the first column for unique values and then extract their corresponding rows (based on your clarification), use this formula instead:
=COUNTIF($A$2:$A$10001,$A2)=1
To run the filter and extract the data:
After running the filter, I get this (note that Value 1 only occurs once per row or line):
Go to:
Data > Advanced Filter
In the first box, typ the range of your first column (e.g. A1:A10000)
Check "only unique records"
Press enter
Perhaps I am missing some nuance here.
If you want to isolate those rows in which the value in column A occurs only once in column A, then use a formula like =SUM(IF(A2=$A$2:$A$10000,1,0))
copied down a helper column. This is an array formula, so it needs to be entered with the Control-Shift-Enter key combination. (It can then be copied down.)
Next set a data filter on your columns, including the helper column, making sure that each column has a header name in row 1. (Set the filter by selecting the "Sort & Filter" button on the Home ribbon.)
Then set the filter value on the helper column to "1". This gives you the set of rows that meet your criterion.
If you need to have a dataset of only those rows, copy the filtered data rows and special-paste them as values in another sheet.
Use conditional formatting to highlight duplicates in the column (conditional formatting > highlight cells rules > Duplicate Values). Most will probably be highlighted. Then use filtering at the top of the column to filter by color > no fill. That will isolate the ones that were only used once.