[Solved] Formula Help

[Wildie]

Hi Guys,

I have attached a spreadsheet that I am working on. Sheet 1 is basically a set of part numbers I sell, Sheet 2 (named Stock) is a supplier's stock list. I want a formula to pull only the stock for the part numbers I have in Sheet 1.

I have used the formula - =INDEX($'Stock'.$B$2:$B$999999;MATCH(A2;$'Stock'.$A$2:$A$999999;0))

This is the same formula I am using on a similar spreadsheet, that one works, and this one doesn't - can anyone advise?

There will be 112,628 lines on the Stock worksheet, is there a limit as to what you can have? (I have reduced the number of rows for the purpose of this as the file size was too big!)

[RoryOF]

Calc can support one million rows, but it is generally felt by Calc experts (I am not one) that if one needs that many entries a database approach is better. It is also safer to work in OO's native formats (.ods for Calc).

I cannot answer your specific question about the formula.

[Wildie]

Hi Rory - I have uploaded the .ods version instead

[keme]

I see a "1" from the first formula, which seems to correspond with the supplier stock level. Copying the formula down your list seems to pick the right values every time (judging from a few samples). It may go wrong if the supplier has duplicates (same SKU in multiple rows) in the stock report. Otherwise it should work.

What do you see on your system, and what do you expect?

[Wildie]

I get #N/A

[keme]

Auto Calculate may be disabled. If you entered the supplier stock report after the formula(s), a recalculation is required.

Press F9 to manually initiate recalculation.

Menu selection Tools - Cell content - AutoCalculate will toggle automatic recalc. on/off.

Trailing spaces in the SKU field may also turn match to mismatch and return that #N/A error. You don't see the space, but Calc does.
I see no stray spaces in the sample data, but if you use different input in your actual work, this is something to look for.

[JohnSUN-Pensioner]

In view of this observation,

It may go wrong if the supplier has duplicates (same SKU in multiple rows) in the stock report

it may be worthwhile to apply another function. For example,

Code: Select all

=SUMIF($Stock.$A$2:$A$113000;A2;$Stock.$B$2:$B$113000)

[Wildie]

SUMIF isn't working, it just says there is Zero stock on every part number.

As I have said, I have copied the formula I have used in a different sheet, and it works in that one, I cannot see anything in the formula that doesn't look correct??

=INDEX($'Stock'.$A$2:A113000;MATCH(A2;$'Stock'.$C$2:$C$113000;0))

[robleyd]

Wild guess - is the stock number in the sheet(s) giving you the problem a text or number value? Ctrl-F8 or View | Value Highlighting - text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted. If I remember correctly, SUMIF ignores text cells.

[Wildie]

Yes, the correct Stock Level from Worksheet 'Stock' is not feeding into the Main sheet where I am entering the formula. With SUMIF, I am just getting a list of zero's. If i use the INDEX & MATCH formula it is coming back with #N/A

[robleyd]

Perhaps I phrased my response poorly.

If the stock numbers are stored as text rather than numbers, SUMIF will not add them. To see what type of values you have in cells, use Ctrl-F8 or View | Value Highlighting to mark cells - text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted

[Wildie]

They are highlighted as numbers

[keme]

They are also numbers in the sample file. INDEX() will happily fetch text as well as numbers, while SUMIF will discard text without returning any error.

Something else is at play here. The sample file looks fine to me. Are anyone other than Wildie experiencing problems with that?

Not likely to be a user profile issue either. Possibly something to do with file locking on your computer. Do you get any message to the effect that "file is locked (by another user)" when you open the file? Is the file in a "protected area" (downloads/dropzone folder, or other folder where files may be regarded as "unsafe")?

[Wildie]

No nothing like that. The inital Stock file comes from a txt document which I have to import. Could that be affecting things?

[Wildie]

So I have gone back into the Example and it is working fine with this formula - =INDEX($Stock.$B$2:$B$887390;MATCH(A2;$Stock.$A$2:$A$887390;0))

However, when that is on a sample of 100 part numbers. When i put in the full stock list (112,628 lines), that is when it is reverting to #N/A

Could it be the size of the sheet that is causing issue?

[JohnSUN-Pensioner]

SUMIF isn't working

What am I doing wrong?
I just took your book Example.ods and inserted the formula

Maybe you really turned off Autocalculate?

Autocalculate.png (12.56 KiB) Viewed 4440 times

[Wildie]

Exactly. It is working for me on the Example. It is when I try to use that formula on the full worksheet (112,628 lines) that it is not working

[robleyd]

Is there any way you can make the full document available as it is hard to diagnose the problem without seeing the patient

[Wildie]

File too large to post on here. Could I email it?

[robleyd]

Someone may be around to accept at this time - for me it is late evening. Alternatively, depending on the content, posting on a file sharing service like Mediafire may be an option?

[JohnSUN-Pensioner]

Exactly. It is working for me on the Example. It is when I try to use that formula on the full worksheet (112,628 lines) that it is not working

When I copy-paste data in sheet Stock many times (1152 times) then I have 152065 rows. Both formulas - with Index and with SumIf - still work...

And once more:

Trailing spaces in the SKU field may also turn match to mismatch and return that #N/A error. You don't see the space, but Calc does.
I see no stray spaces in the sample data, but if you use different input in your actual work, this is something to look for.

[Wildie]

I have uploaded the full file to Mediafire - https://www.mediafire.com/file/trhl19xf ... 20FILE.ods

If I can get this working then that would be amazing, I have been looking at this for 5 hours this morning!

[JohnSUN-Pensioner]

I applaud standing distinguished colleague keme! It was a guess at the level of telepathy - bravo!

@Wildie:
Go to the sheet 'Stock', select any cell in column A and just press F2. Do you see where the text cursor is now? This means that after the SKU in the cell extra spaces are written. Repeat the same with 'Master' sheet - no extra characters. From the point of view of Calс, these are different meanings.
So, now you can use Find&Replace or Text to Columns to remove extra spaces in 'Stock' or you can try to set to OFF Tools - Options - OpenOffice Calc - Calculate - Search criteria = and <> must apply to whole cells

Update How to delete extra spaces with Data - Text to Columns

They are highlighted as numbers

Are you talking about the same file? In STOCK FILE.ods column B in 'Stock' sheet is string, not number - it also can be converted with Text to Columns or Find&Replace

(Probably Friday 13)

[keme]

I applaud standing distinguished colleague keme! It was a guess at the level of telepathy - bravo!
...

Not really but thanks anyway . No ESP at play here, I have just seen too many spreadsheets with content requiring cleanup.

Spreadsheets have their strength in the free-format grid, allowing prototyping of data models without much limitation. This freedom turns to a weakness when it comes to ensuring data integrity, which is often important when you receive data from external sources (which is what happens in this case, if I read the OP right).
The challenge with proper database tools is that they require precise skills. This makes a threshold in the learning curve.
With spreadsheets the learning curve is smoother, you get results immediately and remove errors and correct mistakes as you go.
The challenge when using spreadsheets for database tasks is to circumvent or eliminate said inherent weakness.

Nice trick to use text-to-columns to remove the spaces. Obvious when you see it, but I never thought of that.
Thanks! I learned something today.

[Wildie]

Fantastic Help! Thanks Guys!