Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula ignores last infor - please help
Hi My code in my SHORT is: =IF(ISERROR(INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$60)*('13DBC'!$C$10:$C$60<"" ),ROW()-9+COUNTA('13DBC'!$C$10:$C$60)-COUNTIF('13DBC'!$C$10:$C$60,"0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(SMALL( ROW('13DBC'!$10:$60)*('13DBC'!$C$10:$C$60<""),ROW ()-9+COUNTA('13DBC'!$C$10:$C$60)-COUNTIF('13DBC'!$C$10:$C$60,"0"))),COLUMN()-1)) and my OVER is: =IF(ISERROR(INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$60)*('13DBC'!$D$10:$D$60<"" ),ROW()-9+COUNTA('13DBC'!$D$10:$D$60)-COUNTIF('13DBC'!$D$10:$D$60,"0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(SMALL( ROW('13DBC'!$10:$60)*('13DBC'!$D$10:$D$60<""),ROW ()-9+COUNTA('13DBC'!$D$10:$D$60)-COUNTIF('13DBC'!$D$10:$D$60,"0"))),COLUMN()-1)) for some reason, it ignores the last short and over barcodes in 13DBC, which is: SHORT: 6001009002600 12.000 units (column C52) OVER: 2906818000004 2.000 units (column C60) It does not give the same problem in my 8DBC's please help My file is a little big, and I cant make it any smaller. here it is http://www.epping.co.za/stockproblem.zip you can take the password off as follows: ToolsProtectionunprotect. there is no password. Thanks. Sonar -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=470611 |
#2
|
|||
|
|||
Hi!
for some reason, it ignores the last short and over barcodes in 13DBC, which is: SHORT: 6001009002600 12.000 units (column C52) OVER: 2906818000004 2.000 units (column C60) You only have data down to row 52 so there's nothing to miss in "(column C60)". Actually, you're only missing data in the "Over" sheet. There are 3 entries that should appear in "Short" (and they're there) and 2 entries that should appear in "Over" with only 1 being there. You can replace your current formula with this array formula: =IF(ROWS($1:1)<=COUNT('13DBC'!D$10:D$60),INDEX('13 DBC'!A$10:A$60,SMALL(IF('13DBC'!D$10:D$60<"",(ROW ('13DBC'!A$10:A$60)-ROW(A$10))+1),ROW(1:1))),"") Replacing all your current formulas in column A of sheet OVER-2 with the above formula reduced the file size by 6kb. You can make most of your other "lookup" formulas more efficient, also. If you recall, I made a similar suggestion to use the above formula about a month or so ago when you posted this but you replied back about having trouble with barcodes in TEXT and NUMBERS. Well, with just a quick look-over I can see no reason for having the mixed types. That's causing you to use overly complex formulas. (like the one you posted ). When using the formula I suggested some of your lookup formulas now return errors due to the MIXED DATA TYPES. Example: Vlookups that use a numeric lookup value in a text lookup table and vice versa. Formatting numeric data as text just leads to problems! Would you like me to "clean-up" this file? Biff "sonar" wrote in message ... Hi My code in my SHORT is: =IF(ISERROR(INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$60)*('13DBC'!$C$10:$C$60<"" ),ROW()-9+COUNTA('13DBC'!$C$10:$C$60)-COUNTIF('13DBC'!$C$10:$C$60,"0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(SMALL( ROW('13DBC'!$10:$60)*('13DBC'!$C$10:$C$60<""),ROW ()-9+COUNTA('13DBC'!$C$10:$C$60)-COUNTIF('13DBC'!$C$10:$C$60,"0"))),COLUMN()-1)) and my OVER is: =IF(ISERROR(INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$60)*('13DBC'!$D$10:$D$60<"" ),ROW()-9+COUNTA('13DBC'!$D$10:$D$60)-COUNTIF('13DBC'!$D$10:$D$60,"0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$AE$50,SUMPRODUCT(SMALL( ROW('13DBC'!$10:$60)*('13DBC'!$D$10:$D$60<""),ROW ()-9+COUNTA('13DBC'!$D$10:$D$60)-COUNTIF('13DBC'!$D$10:$D$60,"0"))),COLUMN()-1)) for some reason, it ignores the last short and over barcodes in 13DBC, which is: SHORT: 6001009002600 12.000 units (column C52) OVER: 2906818000004 2.000 units (column C60) It does not give the same problem in my 8DBC's please help My file is a little big, and I cant make it any smaller. here it is http://www.epping.co.za/stockproblem.zip you can take the password off as follows: ToolsProtectionunprotect. there is no password. Thanks. Sonar -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=470611 |
#3
|
|||
|
|||
Perhaps try a simpler? non-array alternative at the cost
of using just 2 helper criteria columns in sheet: 13DBC (it's your preference, of course <g) and ... some suggested simplifications for the formulas in cols B, D and F in sheets SHORT-2 and OVER-2 as well Sample at: http://www.savefile.com/files/9947056 File: sonar_stockproblem_wksht.xls In sheet: 13DBC ----------------------- Insert 2 new columns E & F Put in E10: =IF(C10="","",ROW()) Copy across to F10, fill down to F60 (or more as desired. Can fill down ahead of expected data) (Leave the cells above the starting row, i.e. E1:F9 empty) In sheet: SHORT-2 ------------------------- Put in A10: =IF(ISERROR(SMALL('13DBC'!E:E,ROWS($A$1:A1))),"", INDEX('13DBC'!A:A,MATCH(SMALL('13DBC'!E:E,ROWS($A$ 1:A1)), '13DBC'!E:E,0))) Put in B10: =IF(ISNA(MATCH(A10,STOCK!A:A,0)),"", INDEX(STOCK!B:B,MATCH(A10,STOCK!A:A,0))) Put in D10: =IF(ISNA(MATCH($A10,'13DBC'!A:A,0)),"", INDEX('13DBC'!C:C,MATCH($A10,'13DBC'!A:A,0))) Put in F10: =IF(ISNA(MATCH($A10,'13DBC'!A:A,0)),"", INDEX('13DBC'!AZ:AZ,MATCH($A10,'13DBC'!A:A,0))) (ensure that col AZ is the results col - it's just a guess here. adapt to suit.) Fill down the respective cols In sheet: OVER-2 ------------------------- Put in A10: =IF(ISERROR(SMALL('13DBC'!F:F,ROWS($A$1:A1))),"", INDEX('13DBC'!A:A,MATCH(SMALL('13DBC'!F:F,ROWS($A$ 1:A1)),'13DBC'!F:F,0))) Put in B10: =IF(ISNA(MATCH(A10,STOCK!A:A,0)),"", INDEX(STOCK!B:B,MATCH(A10,STOCK!A:A,0))) Put in D10: =IF(ISNA(MATCH($A10,'13DBC'!A:A,0)),"", INDEX('13DBC'!D:D,MATCH($A10,'13DBC'!A:A,0))) Put in F10: =IF(ISNA(MATCH($A10,'13DBC'!A:A,0)),"", INDEX('13DBC'!AZ:AZ,MATCH($A10,'13DBC'!A:A,0))) (ensure that col AZ is the results col - it's just a guess here. adapt to suit.) Fill down the respective cols -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#4
|
|||
|
|||
Hi Biff Thanks for the help It is much appreciated. You can try and clean up the file if you wish. The reason why I have seperated the 8digits from the 13digits, is because they do work differently somehow. the 8 digits you will find to work fine in Number format, it brings up the descriptions, the 13 digits works fine in Text format again, as that brings up the descriptions, it does not want to work when put in number format. - unless of course, its because my stock items have " ' " in front of the 13 digits, thinking about it now. Maybe your right, it can still work. I myself will try that. Ok, I have tried your formula: =IF(ROWS($1:1)<=COUNT('13DBC'!D$10:D$60),INDEX('13 DBC'!A$10:A$60, SMALL(IF('13DBC'!D$10:D$60<"",(ROW('13DBC'!A$10:A $60)-ROW(A$10))+1),ROW(1:1))),"") It does not work as good as the one I had, it gives me 6001009010681 WW - CHOCOLATE BROWNIE DESSERT 6001009012692 WW - TIRAMISU 90G Instead of 6001009011985 WW - SPIN/FETA ROULADE 500G 2906818000004 WW BUTTERNUT WHOLE Still does not explain why it ignore my butternut though. but what I will do however, is take away the " ' " from the 13 digits, convert the cell formats in 13dbc to number format and see if it accepts, try to make it like the 8dbc. Lets see what happens. regards Sonar -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=470611 |
#5
|
|||
|
|||
Hi!
Ok, I have tried your formula: It does not work as good as the one I had It works just fine on my end! Let me fix this for you and you'll see. Where should I send the file when I'm done? Biff "sonar" wrote in message ... Hi Biff Thanks for the help It is much appreciated. You can try and clean up the file if you wish. The reason why I have seperated the 8digits from the 13digits, is because they do work differently somehow. the 8 digits you will find to work fine in Number format, it brings up the descriptions, the 13 digits works fine in Text format again, as that brings up the descriptions, it does not want to work when put in number format. - unless of course, its because my stock items have " ' " in front of the 13 digits, thinking about it now. Maybe your right, it can still work. I myself will try that. Ok, I have tried your formula: =IF(ROWS($1:1)<=COUNT('13DBC'!D$10:D$60),INDEX('13 DBC'!A$10:A$60, SMALL(IF('13DBC'!D$10:D$60<"",(ROW('13DBC'!A$10:A $60)-ROW(A$10))+1),ROW(1:1))),"") It does not work as good as the one I had, it gives me 6001009010681 WW - CHOCOLATE BROWNIE DESSERT 6001009012692 WW - TIRAMISU 90G Instead of 6001009011985 WW - SPIN/FETA ROULADE 500G 2906818000004 WW BUTTERNUT WHOLE Still does not explain why it ignore my butternut though. but what I will do however, is take away the " ' " from the 13 digits, convert the cell formats in 13dbc to number format and see if it accepts, try to make it like the 8dbc. Lets see what happens. regards Sonar -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=470611 |
#6
|
|||
|
|||
The reason why BUTTERNUT does not show is because your INDEX() range is set at between row 1 to row 50. However, BUTTERNUT is in row 52. Of course you cannot output something that is out of the search range. I am sure you know how to fix it now. Speaking of Biff's formula, it's an array formula. Thus, you must accept with Ctrl+Shift+Enter. sonar Wrote: Hi Biff Thanks for the help It is much appreciated. You can try and clean up the file if you wish. The reason why I have seperated the 8digits from the 13digits, is because they do work differently somehow. the 8 digits you will find to work fine in Number format, it brings up the descriptions, the 13 digits works fine in Text format again, as that brings up the descriptions, it does not want to work when put in number format. - unless of course, its because my stock items have " ' " in front of the 13 digits, thinking about it now. Maybe your right, it can still work. I myself will try that. Ok, I have tried your formula: =IF(ROWS($1:1)<=COUNT('13DBC'!D$10:D$60),INDEX('13 DBC'!A$10:A$60, SMALL(IF('13DBC'!D$10:D$60<"",(ROW('13DBC'!A$10:A $60)-ROW(A$10))+1),ROW(1:1))),"") It does not work as good as the one I had, it gives me 6001009010681 WW - CHOCOLATE BROWNIE DESSERT 6001009012692 WW - TIRAMISU 90G Instead of 6001009011985 WW - SPIN/FETA ROULADE 500G 2906818000004 WW BUTTERNUT WHOLE Still does not explain why it ignore my butternut though. but what I will do however, is take away the " ' " from the 13 digits, convert the cell formats in 13dbc to number format and see if it accepts, try to make it like the 8dbc. Lets see what happens. regards Sonar -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=470611 |
#7
|
|||
|
|||
Thank you very much Biff and Morrigan, its much appreciated, and I will certainly look into these solutions you've given me. Regards Sonar P.S. its almost everytime I get something right, something seems to bugger it up again! -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=470611 |
#8
|
|||
|
|||
Ah Biff, thank you, thank you, thank you.!! Never thought 2 extra columns would make such a difference. I have not been able to sleep because of endless little problems. Not that the other formulas did'nt work, it just seems as if yours are less problematic. Thanks again. -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=470611 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula ignores last infor - please help
"sonar" wrote:
.. Never thought 2 extra columns would make such a difference. I'm not sure, but was this response of yours mis-routed ? <g Don't think Biff's suggestions include "2 extra columns" .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |