## OpenOffice macro needed will pay

Just a meeting place for professional offers & requests
Forum rules
Disclaimer: this section of the forum is just a meeting place for professional offers & requests. We do not and cannot insure the liability of the proposal made publicly in the forum or privately after contact has been made in the forum. Therefore, please take up the offers made here at your own risk.

### OpenOffice macro needed will pay

So I have a spreadsheet that i get from my eCommerce platform and it sends my inventory , I sell items in singles,5packs and 25packs . I need to reset my inventory often when the singles or 5 packs get

low I am sent an email from my ecommerce warning me when my 5 packs or my singles get low.

right now what i have done is
1. I copied into "simple macro.ods" Columns/rows A1-F121 from my ecommerce.csv export (light yellow highlighting)
2. The output is somewhat correct as you can see in Columns/rows R1-T121 (brighter yellow highlight) compare the qty in colum "S" vs Column "B"
3. how it works is it takes all the qtys of single, 5 ,and 25's coming in from my ecommerce export and compiles it into H4 to be a total qty example 435 = I4*25+I3*5+I2
4. Then J4 Divides the total qty by 25 if the total qty larger than 76 IF(H4>=76;QUOTIENT(H4;25);"too small") This column isnt necesary it just so you can see numbers.
5. Then K4 gets the remainder after being divided by 25 IF(H4>=76;MOD(H4;25);"too small")
6. Then L4 Looks to see if the remainder is less than 10 it then decides to deduce either 2 or 3 from my 25 pack totals by doing IF(K4>10;QUOTIENT(H4; 25)-2;QUOTIENT(H4; 25)-3)
7. Then in M2 it looks at the remainder and says if it is K4<10 then leave it otherwise add 25 to it IF(K4>10;K4;K4+25)
8. Then N3 looks at H4 ato see if it is bigger than 76 if so it resets the 5 packs to the qty of 10 previously deduced from 25packs in step 6 by either 2 or 3 IF(H4>=76;10;"")
9. is where O2,O3,O4 simply take the data from O2=M2 O3=N3 O4=L4

So as you can see i am runing into problems when the qty is lower than 76

I think a different approach is needed when the total qty gets below 76 then i think we only need stock 5 packs and singles and 0 the 25packs something like
the Quotent and the Mod of Total QTY become divisible by 5 instead of 25 similar to what i have done with values over 75 and if the remainder is less than say 4 we add 10 too it by deducing 2 of the

5packs

I am by no means skilled but i know there is someone who can help me , maybe this needs to be writen in basicor VB or something any help or reccomendations please reply !!!

Thanks
Attachments
simple _macro.ods
save often
Last edited by snowjockey on Tue Apr 04, 2017 8:20 am, edited 1 time in total.
Open office 4.1.3 windows 8.1
snowjockey

Posts: 2
Joined: Mon Apr 03, 2017 7:03 pm

### Re: OpenOffice macro needed will pay

If the current structure of your file is reasonably tidy, I guess this is best done through the database. (A spreadsheet can be registered as a data source with Base.) Sending you a PM.

keme
Volunteer

Posts: 3185
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway