Hi Mondo
I had a similar problem myself not so long ago, and I found this page
helpful:
http://cpearson.com/excel/duplicat.htm is very helpful for dealing
with duplicate items like your material names on lists. Here are the formulas
from there you will need to use:
(caution - to make this explanation simpler to follow, I will use named cell
ranges. I will start with the ("raw") list of names, and call it Duplicate.
First, we eliminate any names on the list and replace them with blanks. This
new cell range will be called Blanks. Secondly, we eliminate all blanks from
the list and call the new list (with no blanks and no duplicates) NoBlanks.
This is what you are looking for, I hope :) - correct me if I'm wrong.)
So, first, to change Duplicates to Blanks, enter this formula into the first
cell (A1):
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")
This assumes that Duplicates starts in column A, row 1 - if this is not the
case, you'll need to change the references to suit. Fill down to the end of
the column, so that Blanks has the same number of rows as Duplicates.
Next, enter the following as an array formula (if you haven't used these
before, be aware that you need to use CTRL+SHIFT+ENTER to enter it):
=IF(ROW()-ROW(NoBlanks)+1ROWS(Blanks)-
COUNTBLANK(Blanks),"",INDIRECT(ADDRESS(SMALL((IF(B lanks<"",
ROW(Blanks),ROW()+ROWS(Blanks))),ROW()-ROW(NoBlanks)+1),
COLUMN(Blanks),4)))
When you hit CTRL+SHIFT+ENTER, this will put braces around the formula. Fill
this down to the end of the column, and voila! You should now have a list of
all unique, non-blank entries in the NoBlanks range.
If this was not so easy to follow, I suggest checking the Pearson software
link from above - it may be easier to work with, and has examples you can
download.
Good luck
Geoff
--
There are 10 types of people in the world - those who understand binary and
those who don''''t.
"Mondo" wrote:
Hi,
I am sometimes working with list of materials, where I have a line for each
change on the material. That means that sometimes there can be lots of lines
for the same material. For example there has been 5000 changes on 2000
materials. That means that in my report I have 5000 lines. How can I
eliminate those extra 3000, to just get the list of all the materials with a
change(s) once on the list?
Thank you