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