[Solved] Formula Help
[Solved] Formula Help
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!)
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 99 times
Last edited by Hagar Delest on Sat Oct 21, 2017 10:11 pm, edited 2 times in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
Open Office 4.1.3 on Windows 10
Re: Formula Help
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.
I cannot answer your specific question about the formula.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Formula Help
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?
What do you see on your system, and what do you expect?
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: Formula Help
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.
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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Formula Help
In view of this observation,
it may be worthwhile to apply another function. For example,keme wrote:It may go wrong if the supplier has duplicates (same SKU in multiple rows) in the stock report
Code: Select all
=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.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Formula Help
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))
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
Re: Formula Help
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Formula Help
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
Re: Formula Help
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
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Formula Help
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")?
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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: Formula Help
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
Re: Formula Help
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?
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Formula Help
What am I doing wrong?Wildie wrote:SUMIF isn't working
I just took your book Example.ods and inserted the formula Maybe you really turned off Autocalculate?
- Attachments
-
- Example.ods
- (18.73 KiB) Downloaded 107 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Formula Help
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
Re: Formula Help
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Formula Help
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Formula Help
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...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
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.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Formula Help
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!
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Formula Help
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
(Probably Friday 13)
@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
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&ReplaceWildie wrote:They are highlighted as numbers
(Probably Friday 13)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Formula Help
Not really but thanks anyway . No ESP at play here, I have just seen too many spreadsheets with content requiring cleanup.JohnSUN-Pensioner wrote:I applaud standing distinguished colleague keme! It was a guess at the level of telepathy - bravo!
...
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.