one time appearance on the list

[Guest]

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

 

[Guest]

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)+1>ROWS(Blanks)-
COUNTBLANK(Blanks),"",INDIRECT(ADDRESS(SMALL((IF(Blanks<>"",
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

 

[Guest]

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:

 

[Ragdyer]

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.

 

[Guest]

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:

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------

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: