Biff,
Now I truly understand what you meant by using formulas. So, no DATA menu period. There are probably more formulas if we want to have a list of all the duplicates.
Thank you for taking the time typing up the sample and posting the explanation. I kind of understand the overall idea and I should recheck this again when I am more experienced. For some reason, SMALL ( ) as it is in the formula doesn't quite click with me. I see that we have done the logical test and that the calculation of the row number is performed. I can't quite grasp the role of SMALL ( ). Out of curiosity, I took out SMALL in the formula and I thought the formula wouldn't work at all. To my surprise, it still pulled out the right records. I selected 1000218 in the drop down list. I saw most of the info that I was supposed to see i.e. it displayed three records. The only problem was the first "Mandy" didn't show. The error was #VALUE! I don't know what is going on. But I don't want to take up your time. I'll let it go.
I am more interested in knowing more about sorting and filtering. The other day, you mentioned that it is a good idea to sort a huge table used by VLOOKUP. Later, Dave suggested that it is undesirable to sort the list when we try to do advanced filter. Obviously, for this formula, we don't need to sort. Is it true that when we deal with lists, regardless of whether we do filter, we don't usually sort?
By the way, I agree with everything you said about going to classes and learning from books. My experience of learning MS at the college was miserable. I had to be very careful that I didn't allow the wrong information to get into my system. I wish I found the forums long ago.
Appreciate your guidance.
Epinn
Biff said:
Do you think this will work?
Yes.
Do you think my above array formula will grind the system to a halt?
No.
Did you have the same formula in mind?
No. I had something completely different in mind. Not everyone likes to or
can use filters (me included) or, you might need this data on another sheet
AND you want it to be dynamic (not filter then copy/paste). (one of the
reasons I don't like filters) Then what do you do?
You do this:
Sample file: Extract_data 19kb
http://cjoint.com/?jxgj2rCtO0
The file is based on this thread. Select an ID number from the drop down.
The highlighted area is where the formulas are located. Actually, it's one
formula copied to all the cells.
For a detailed explanation see this:
http://tinyurl.com/j2x2w
Biff
Biff,
If I understand correctly, with AutoFilter > custom we can only select a
maximum of two ID's at a time. Am I correct? If Jared wants to see all
ID's that are duplicated, can he try the following?
Sort column A that contains the ID number.
Put the formula in a blank column, say G.
=IF(COUNTIF(A:A,A2)>1,"Duplicate","")
Please note that the formula is an array formula i.e. must be entered with
ctrl+shift+enter.
The argument for COUNTIF is A2 assuming row 1 is the column heading and A2
is the first piece of data. Adjust accordingly based on the number of rows
for headers.
Fill column G with the formula.
Then do AutoFilter to select "Duplicate" in column G.
Then sort on column A, ID no. again. Not really necessary.
Do you think this will work?
<<This could also be done using formulas but it's more complicated and may
not
be the best solution if you have 1000's of rows of data.
Did you have the same formula in mind? Do you think my above array formula
will grind the system to a halt?
Appreciate guidance.
Epinn
The easiest way to do it is to use the AutoFilter.
Select the column header ID NUMBER
Goto the menu Date>Filter AutoFilter
Click the drop arrow and then select the ID number of interest.
To remove the filter just goto the menu Data>Filter>Autofilter. It toggles
on/off just by selecting it.
This could also be done using formulas but it's more complicated and may not
be the best solution if you have 1000's of rows of data.
Biff
