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.
3
-
Need to confirm, are the one-times row-wise or column-wise. Meaning do you want the rows in which the value only appears once in the entire column A or do you want the rows in which the value in column A only appears once in that row? My answer works for the first case, while Kaze's answer does the second.– AmerApr 5, 2013 at 5:19
-
Hi @Amer and thank you for your answer, I want the one-timers column-wise. Meaning that from a column which includes many values, I want to filter out only the ones which only appeared once.– Eugene SApr 5, 2013 at 5:28
-
The title, the first paragraph, and the second paragraph are all asking for different things. Can you please clarify which you mean? Do you want to keep the rows in which the value in column A only appears once? Or do you want to remove such rows and leave the rest (which have the value in column A more than once)?– ExcellllApr 6, 2013 at 5:04
Add a comment
|
5 Answers
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.
- In the fifth column (as you stated you had four) enter
=IF(COUNTIF($A:$A,$A3)>1,"",A3) - Drag this formula over three columns to the right (so formula is in total four columns)
- Select the four cells with the formula and double click square at the bottom right of the selection (this should autofill all the way down to your 10k rows).*
- Select the four columns and copy
- Do a Paste Special - Values
- Apply filter on these columns
- Click the filter drop-down on any column and uncheck Blanks
- Select the columns again (if they got unselected) and copy
- Go to a new sheet and paste
*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.
Step 1 - Set up the Criteria
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
Step 2 - Run the Filter & Extract the Rows
To run the filter and extract the data:
- Select your data table.
- Go to Data > Advanced. (Or press ALT+A, Q)
- Select Copy to another location.
- Make sure that List range contains the reference to your data table (including headers).
- For Criteria range, select the Criteria cell (yellow in my example) AND the empty cell above it.
- For Copy To, select the cell where you would like the extracted data to appear. Make sure there's enough real estate for the result. I recommend using an empty sheet.
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
-
This will only filter out the doubles. I want to filter out the values which appeared only once in the first place.– Eugene SApr 5, 2013 at 2:42
-
1Yes, that is what I understand if you print in bold: "I don't want duplicates"– VincentApr 5, 2013 at 2:44
-
Exactly, I don't want to filter out duplicates, I want to filter out one-timers.– Eugene SApr 5, 2013 at 2:48
-
2Now you are changing your line. "I don't want duplicates" means: do not give me duplicates, which means, give me unique values. "I don't want to filter out duplicates" means: leave the duplicates. Formulating your question is very important if you want a good answer. So instead of your downvote, you could also just have reformulated your question.– VincentApr 5, 2013 at 2:51
-
1This is why I mentioned "One-timers". Seems formulated enough to me..– Eugene SApr 5, 2013 at 2:53
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.
