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

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

Hi Geoff, Thanks for the answer, but it didn't help me much so far. Each
formula I try to use, I get the error in formula responce.

Have any idea what could be the problem?

"Geoff" je napisal:

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

You could try this:

Say your change list starts in A1 to A5000.

In B1 enter:
=A1

In B2 enter this *array* formula:
=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$5000&"" ),0)),"",INDEX(IF(ISBLANK(
$A$1:$A$5000),"",$A$1:$A$5000),MATCH(0,COUNTIF(B$1 :B1,$A$1:$A$5000&""),0)))

Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Mondo" wrote in message
...
Hi Geoff, Thanks for the answer, but it didn't help me much so far. Each
formula I try to use, I get the error in formula responce.

Have any idea what could be the problem?

"Geoff" je napisal:

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

Hi Ragdyer,

thank you for your answer, but it's still not working. I don't know am I
doing something wrong. I just copied your formula into B2 as you said, but
again formula error apears, marking first COUNTIF in your formula. Is
something wrong with the formula or am I doing something wrong? I know array
formulas, a tried to activate it with CSE, but nothing.

Any ideas?

"Ragdyer" je napisal:

You could try this:

Say your change list starts in A1 to A5000.

In B1 enter:
=A1

In B2 enter this *array* formula:
=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$5000&"" ),0)),"",INDEX(IF(ISBLANK(
$A$1:$A$5000),"",$A$1:$A$5000),MATCH(0,COUNTIF(B$1 :B1,$A$1:$A$5000&""),0)))

Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Mondo" wrote in message
...
Hi Geoff, Thanks for the answer, but it didn't help me much so far. Each
formula I try to use, I get the error in formula responce.

Have any idea what could be the problem?

"Geoff" je napisal:

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

Try making a test sheet of 10 rows or so, with similar data manually
entered.

If the formula works , you'll know there's something wrong with the data
list.

Good luck.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Mondo" wrote in message
...
Hi Ragdyer,

thank you for your answer, but it's still not working. I don't know am I
doing something wrong. I just copied your formula into B2 as you said, but
again formula error apears, marking first COUNTIF in your formula. Is
something wrong with the formula or am I doing something wrong? I know array
formulas, a tried to activate it with CSE, but nothing.

Any ideas?

"Ragdyer" je napisal:

You could try this:

Say your change list starts in A1 to A5000.

In B1 enter:
=A1

In B2 enter this *array* formula:
=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$5000&"" ),0)),"",INDEX(IF(ISBLANK(
$A$1:$A$5000),"",$A$1:$A$5000),MATCH(0,COUNTIF(B$1 :B1,$A$1:$A$5000&""),0)))

Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Mondo" wrote in message
...
Hi Geoff, Thanks for the answer, but it didn't help me much so far. Each
formula I try to use, I get the error in formula responce.

Have any idea what could be the problem?

"Geoff" je napisal:

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