Thread: formula ignores last infor - please help

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('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

"sonar" <sonar.1vxnmd_1127682306.4971@excelforum-nospam.com> wrote in
message news:sonar.1vxnmd_1127682306.4971@excelforum-nospam.com...
>
> 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
>
>
> --
> sonar
> ------------------------------------------------------------------------
> sonar's Profile:
> http://www.excelforum.com/member.php...fo&userid=8424
> View this thread: http://www.excelforum.com/showthread...hreadid=470611
>

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
--

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" <sonar.1vy72d_1127707529.2505@excelforum-nospam.com> wrote in
message news:sonar.1vy72d_1127707529.2505@excelforum-nospam.com...
>
> 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('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 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
>

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.

Originally Posted by sonar

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('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 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

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" 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
--