Nick's Excel Tip Of The Day - Part 1
I will try to add a tip each day, but it's more of a goal than a promise
; - >
Nick
New Tips added here
Subscribe to new tips
IMPORTANT: Worried about SECURITY? Open Excel in SAFE MODE
| Attachment | Size |
|---|---|
| DaysTillChristmas.xls | 17 KB |
| VLOOKUP.xls | 19 KB |
| VLOOKUP_Currencies.xls | 18 KB |
| IRR.xls | 18 KB |
| SheetName.xls | 17 KB |
| FindingLastCellNonContiguous.xls | 17 KB |
| InsertingColumnsAnd Rows.xls | 16.5 KB |
| DataValidation1.xls | 17.5 KB |
| COUNTIF.xls | 26.5 KB |
| VariableRanges.xls | 20 KB |
| Copy-Down.xls | 17 KB |
»
- Nick's blog
- Login or register to post comments
- 69754 reads
40. Excel Tip Of The Day - Remove Dupes With Multiple Columns
This is a follow-on from yesterday's tip and relates to XL 2003
Suppose you have the following data and you want to remove the dupes (i.e. where both columns are the same):
Add a another column containing the concatenation of the previous 2 columns like so:
Copy it down, and proceed as with yesterday's tip
I need to extract repititive data from multiple columns
Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.
do u want this using macro or
do u want this using macro or this is one time activity that you want the unique values.
I need to extract repititive data from multiple columns
Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.
I need to extract repititive data from multiple columns
Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.
39. Excel Tip Of The Day - Remove Duplicates from a sorted list
For XL 2003, there are many ways to find and remove duplicates from a sorted column of data, but this is my favourite as it doesn't involve leaving formulae on the sheet.
With a bit of practice you can become very quick at it.
Suppose you have the following data:
and you want to end up with the duplicates removed:
Step1:
Enter the following formula in the cell adjacent to the 1st data cell:
This is basically asking if the cell is the same as the one below. If it is, then it's a duplicate.
Step2:
Copy the formula down
Step3:
Select the 1st heading (B2), and press in sequence:
ALT then d then f then f.. this turns on the data filter.
Now filter for TRUE (click on the arrow at cell C2, and select TRUE)- this selects only the duplicates.
Step4:
Select the entire row for data that has been filtered (excluding the heading).. and right click=> Delete.. to delete.
Step5:
Turn off the data filter by using the shortcut: ALT then d then f then f, and clear the formulae that you added... There you have it:
Video Training on how to Remove Duplicates:
Use AdvancedFilter too
As my comment on previous article, you can use AdvancedFilter directly on multi column data too. It will filter out unique records with ease. :)
Advanced Filter
Advanced Filter doesn't actually get rid of the duplicates, it only hides rows.
That's why I use the method as described.
Use AdvancedFilter
Frankly speaking, the method here is neither fast or convinient.
Just select the column, use AdvancedFilter, fill nothing in the Criteria, and click "Unique records only".
You can choose to copy the result to another place or filter in place.
Removing Duplicates
I just timed it - 15 seconds.
Still prefer this to Advanced filter...
; - >