formula ignores last infor - please help

  • Thread starter Thread starter sonar
  • Start date Start date
S

sonar

Hi

My code in my SHORT is:

=IF(ISERROR(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)),"",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(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)),"",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: Tools>Protection>unprotect.
there is no password.

Thanks.
Sonar :confused
 
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)
Click to expand...

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('13DBC'!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
 
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
 
Hi Biff

Thanks for the help

It is much appreciated. You can try and clean up the file if yo
wish.

The reason why I have seperated the 8digits from the 13digits, i
because they do work differently somehow.

the 8 digits you will find to work fine in Number format, it brings u
the descriptions, the 13 digits works fine in Text format again, a
that brings up the descriptions, it does not want to work when put i
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('13DBC'!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
will do however, is take away the " ' " from the 13 digits, convert th
cell formats in 13dbc to number format and see if it accepts, try t
make it like the 8dbc.

Lets see what happens.


regards
Sona
 
Hi!
Ok, I have tried your formula:
It does not work as good as the one I had
Click to expand...

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
 
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.
 
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!
 
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 said:
.. Never thought 2 extra columns would make such a difference.
Click to expand...

I'm not sure, but was this response of yours mis-routed ? <g>
Don't think Biff's suggestions include "2 extra columns" ..
 
Back
Top