Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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 -- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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 -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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 -- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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 -- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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 --- Just a side note on your labelling in Overall's K2. As col K is actually an arbitrary criteria cum tie-breaker col, I'd simply label K2 as, say, "TieBreak" instead, to avoid any future confusion with the actual col G "Date issued". -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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? "Max" wrote: 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 --- Just a side note on your labelling in Overall's K2. As col K is actually an arbitrary criteria cum tie-breaker col, I'd simply label K2 as, say, "TieBreak" instead, to avoid any future confusion with the actual col G "Date issued". -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
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. "Max" wrote: 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 --- Just a side note on your labelling in Overall's K2. As col K is actually an arbitrary criteria cum tie-breaker col, I'd simply label K2 as, say, "TieBreak" instead, to avoid any future confusion with the actual col G "Date issued". -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
"roy.okinawa" wrote
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 .. 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. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unwanted Repeats
Glad it sorted out fine for you, Roy
.... although I'm not sure what you meant by: ".. formatted as a number vice date .." (but it's always good to eradicate "vice" of any kind <g) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "roy.okinawa" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes | Excel Discussion (Misc queries) | |||
Deleting unwanted Cells | Excel Discussion (Misc queries) | |||
Deselecting unwanted cells | New Users to Excel | |||
Unwanted blank line in cell with wrapped text | Excel Discussion (Misc queries) | |||
Count repeats | Excel Worksheet Functions |