Unwanted Repeats

R

roy.okinawa

I am getting repeats when there should not be any. Here are the formulas and
below is a portion of the worksheet. You can see hhow the Section and Work
Order are repeating. It should only show a one time entry.

=IF(ISERROR(SMALL(Overall!$AP:$AP,ROWS(A27:$A$29))),"",INDEX(Overall!AI:AI,MATCH(SMALL(Overall!$AP:$AP,ROWS(A27:$A$29)),Overall!$AP:$AP,0)))

=IF(ISNA(MATCH(A2,Overall!AI:AI,0)),"",INDEX(Overall!A:A,MATCH(A2,Overall!AI:AI,0)))

Section Work Order
00910 OK00021
20380 OK00017
29025 OK00002
20380 OK00017
00910 OK00021
13301 OK00022
20380 OK00017
13001 OK00008
13001 OK00008
 
M

Max

Roy,
=IF(ISERROR(SMALL(Overall!$AP:$AP,ROWS(A27:$A$29))),"",INDEX(Overall!AI:AI,M
ATCH(SMALL(Overall!$AP:$AP,ROWS(A27:$A$29)),Overall!$AP:$AP,0)))

If I'm not mistaken, think you need to go back to your starting cell and
correct this part of the formula over there: ROWS(A27:$A$29)
(You've put the dollar sign in the wrong end in the expression above <g>)

The starting cell should have: ROWS($A$1:A1)
i.e. it should read as:

=IF(ISERROR(SMALL(Overall!$AP:$AP,ROWS($A$1:A1))),"",INDEX(Overall!AI:AI,MAT
CH(SMALL(Overall!$AP:$AP,ROWS($A$1:A1)),Overall!$AP:$AP,0)))

Then copy down from the starting cell
 
M

Max

One other thought would be to check the ("tie-breaker"?) formula
that's in: Overall!$AP:$AP

What exactly is the formula that you have there ?
Where does the formula start and end?

If the formula's starting in mid-stream in col AP, i.e. not from row1 down,
then it's safer to use the exact range in col AP instead of (albeit neater)
entire col references. If you use entire col refs, then ensure that there's
nothing in col AP above the starting cell which could be throwing the
correct extracts off (look for especially other numbers unrelated to what
you're extracting with the posted formula).
 
R

roy.okinawa

After trying different variations of the formula, I believe this could be the
problem. Not sure how to fix it.

Column A on this worksheet list the work sections that are pulled from
column AI on the Overall worksheet. Column A may find/list the same work
section more than once. However, when column B looks for the work order, it
should list the work order that corresponds with the work section and date
(contained in column AP on the overall worksheet.)

I think since it finds the work section more than once it is repeating that
match/find in column A. That is the cut/paste that I showed in my 1st post.

So here is how it should look without the repeating work order:

Section Work Order
29025 OK00121
29001 OK00105
29025 OK00114
00910 OK00002
29025 OK00005


Hope that makes sense?
 
M

Max

Roy,

It's tough to visualize what's happening over at your end

Could you upload a "sanitized" small sample copy of your file via a free
filehost* and then post the link to it in response here (the link is
generated when you upload, just copy and paste it here)

*Some free filehosts that could be used:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

For cjoint.com (it's in French), just click "Browse" button, navigate to
folder > select the file > Open, then click the button centred in the page
below (labelled "Creer le lien Cjoint") and it'll generate the link. Then
copy & paste the generated link as part and parcel of your response here.

But kindly note that no attachments
should be posted *directly* to the newsgroup
 
M

Max

Think this should yield correctly what is required

In Sheet1
---------
Put in A1:

=IF(ISERROR(SMALL(Overall!$K:$K,ROWS($A$1:A1))),"",
INDEX(Overall!F:F,
MATCH(SMALL(Overall!$K:$K,ROWS($A$1:A1)),Overall!$K:$K,0)))

A slight correction made to the row incrementer part:
... ROWS($A$3:A3) ...

In the starting cell, irrespective of which row this might be in,
the row incrementer part must always be:
... ROWS($A$1:A1) ..

Put instead in B1:

=IF(ISERROR(SMALL(Overall!$K:$K,ROWS($A$1:A1))),"",
INDEX(Overall!A:A,
MATCH(SMALL(Overall!$K:$K,ROWS($A$1:A1)),Overall!$K:$K,0)))

(The previous formula in B1 needs to be revised. The revised formula is
essentially the same formula as that placed in A1 above, except that it
points to col A in Overall, to retrieve the PO Number.)

Select A1:B1, fill down
Cols A & B should now return the correct results
 
R

roy.okinawa

OK. It works fine while in the test.xls. However, when I enter the formula
in the overall.xls it still comes up with repeats.

Could there be something in the cells that is causing this to happen? Wrong
number format or something?
 
R

roy.okinawa

I found the bad cell. It was formatted as a number vice date and was putting
out bad info. It is all good now.

Thanks.
 
M

Max

OK. It works fine while in the test.xls.
However, when I enter the formula
in the overall.xls it still comes up with repeats ..
Click to expand...

No reason, imo, why it should work only in the 1st test file, which
presumably was already a "prime" cut / subset of your actual. Could you
upload another similar file with the revised formulas loaded and with the
repeats still showing, as you say above ? That's the best way to diagnose.
 
M

Max

Glad it sorted out fine for you, Roy

.... although I'm not sure what you meant by:
".. formatted as a number vice date .."
Click to expand...
(but it's always good to eradicate "vice" of any kind <g>)
 
Top