[Solved] Formula Help

Discuss the spreadsheet application

[Solved] Formula Help

Postby Wildie » Thu Oct 12, 2017 10:41 am

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!)
Attachments
Example.ods
(15.19 KiB) Downloaded 18 times
Last edited by Hagar Delest on Sat Oct 21, 2017 10:11 pm, edited 2 times in total.
Reason: tagged [Solved].
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby RoryOF » Thu Oct 12, 2017 10:44 am

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.
Apache OpenOffice 4.1.4 on Xubuntu 16.04.03 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 25513
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Formula Help

Postby Wildie » Thu Oct 12, 2017 10:47 am

Hi Rory - I have uploaded the .ods version instead :)
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby keme » Thu Oct 12, 2017 11:05 pm

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?
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 2663
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula Help

Postby Wildie » Thu Oct 12, 2017 11:48 pm

I get #N/A
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby keme » Fri Oct 13, 2017 8:56 am

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.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 2663
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula Help

Postby JohnSUN-Pensioner » Fri Oct 13, 2017 9:15 am

In view of this observation,
keme wrote: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   Expand viewCollapse view
=SUMIF($Stock.$A$2:$A$113000;A2;$Stock.$B$2:$B$113000)
Last edited by JohnSUN-Pensioner on Fri Oct 13, 2017 1:12 pm, edited 1 time in total.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 755
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 12:18 pm

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))
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby robleyd » Fri Oct 13, 2017 12:31 pm

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.
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.4 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1087
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 12:46 pm

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
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby robleyd » Fri Oct 13, 2017 12:54 pm

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
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.4 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1087
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 12:55 pm

They are highlighted as numbers
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby keme » Fri Oct 13, 2017 1:08 pm

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")?
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 2663
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 1:11 pm

No nothing like that. The inital Stock file comes from a txt document which I have to import. Could that be affecting things?
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 1:16 pm

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?
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby JohnSUN-Pensioner » Fri Oct 13, 2017 1:17 pm

Wildie wrote:SUMIF isn't working

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


Maybe you really turned off Autocalculate?
Autocalculate.png
Autocalculate.png (12.56 KiB) Viewed 608 times
Attachments
Example.ods
(18.73 KiB) Downloaded 7 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 755
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 1:22 pm

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
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby robleyd » Fri Oct 13, 2017 1:24 pm

Is there any way you can make the full document available as it is hard to diagnose the problem without seeing the patient :)
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.4 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1087
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 1:26 pm

File too large to post on here. Could I email it?
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby robleyd » Fri Oct 13, 2017 1:29 pm

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?
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.4 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1087
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula Help

Postby JohnSUN-Pensioner » Fri Oct 13, 2017 1:33 pm

Wildie wrote: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:
keme wrote: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.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 755
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Formula Help

Postby Wildie » Fri Oct 13, 2017 1:36 pm

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!
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm

Re: Formula Help

Postby JohnSUN-Pensioner » Fri Oct 13, 2017 5:51 pm

I applaud standing distinguished colleague keme! It was a guess at the level of telepathy - bravo! :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
SetItOff.png


Update How to delete extra spaces with Data - Text to Columns
Remove extra spaces.png


Wildie wrote: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)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 755
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Formula Help

Postby keme » Sat Oct 14, 2017 10:15 am

JohnSUN-Pensioner wrote:I applaud standing distinguished colleague keme! It was a guess at the level of telepathy - bravo! :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. :super:
User avatar
keme
Volunteer
 
Posts: 2663
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula Help

Postby Wildie » Mon Oct 16, 2017 1:27 pm

Fantastic Help! Thanks Guys!
Open Office 4.1.3 on Windows 10
Wildie
 
Posts: 16
Joined: Thu Aug 24, 2017 2:31 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests