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(Overa ll!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

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)),Overal l!$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 the 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!$A P:$AP,0)))

Then copy down from the starting cell
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"roy.okinawa" wrote in message
...
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,M
ATCH(SMALL(Overall!$AP:$AP,ROWS(A27:$A$29)),Overal l!$AP:$AP,0)))


=IF(ISNA(MATCH(A2,Overall!AI:AI,0)),"",INDEX(Overa ll!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

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).
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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?



"Max" wrote:

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).
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

http://www.flypicture.com?display=updone&id=rd/wkqXY

I chopped most of the worksheet. It includes just the area of concern.


"Max" wrote:

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--