Discrepancy Qty from 2 column's

sue ponter

Hi I need some help.

I'm self trained and only been using SS for a couple of years, so sorry if this sounds stupid.

I have 3 columns Qty AX, QTY WMS & Discrepancy Qty. If the qty in either of the first 2 columns differ I want the difference to be applied to the 3rd column.

AX could show 20 WMS could show 100 and visa versa I just need the Discrepancy Qty to auto fill

I think this will be easy but I just can't get my head round it! Many Thanks in advance :-)

Andrée Starå

Hi @sue ponter

I hope you're well and safe!

Try something like this.

=[Qty AX]@row - [Qty WMS]@row

Did that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi @sue ponter

Hope you don't mind if I hop in here, @Andrée Starå!

Instead of copy/pasting, try clicking on the cells in the referenced columns to bring them into your formula - that way it will automatically grab the column name in the correct syntax 🙂

You could also get a little fancier with the formula if you didn't want a negative number to show up when the MAX number is on the other side:

=IF([Qty AX]@row >= [Qty WMS]@row, [Qty AX]@row - [Qty WMS]@row, [Qty WMS]@row - [Qty AX]@row)

This checks to see if AX is greater than WMS, and if it is, then it subtracts WMS from AX. Otherwise, if WMS is greater, it subtracts AX from WMS. Does that make sense?

Cheers!

Genevieve

Andrée Starå

Hi @sue ponter

I hope you're well and safe!

Try something like this.

=[Qty AX]@row - [Qty WMS]@row

Did that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

sue ponter

Hi Andree

Yes all good thank you, bar his conundrum! Thank you but no I'm sorry I got the dreaded #UNPARSEABLE!!

Is there anything that we can try?

Thanks Sue

Andrée Starå

@sue ponter

Happy to help!

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

sue ponter

Hi Andree So the top 4 lines are as good as I could get it, before asking the question. The bottom 2 rows are how I would ideally like it.

So instead of the checkbox when the 2 columns don't match I want the total difference between the 2 columns One columns figure won't always be more or less than the other. If you understand me.

Thanks Sue

Andrée Starå

@sue ponter

You're more than welcome!

Did you try and paste my formula in the Discrepancy in Qty column?

sue ponter

Hi Andree

I sure did and it came back #UNPARSEABLE :-(

Cheers Sue

Genevieve P.

Hi @sue ponter

Hope you don't mind if I hop in here, @Andrée Starå!

Instead of copy/pasting, try clicking on the cells in the referenced columns to bring them into your formula - that way it will automatically grab the column name in the correct syntax 🙂

You could also get a little fancier with the formula if you didn't want a negative number to show up when the MAX number is on the other side:

=IF([Qty AX]@row >= [Qty WMS]@row, [Qty AX]@row - [Qty WMS]@row, [Qty WMS]@row - [Qty AX]@row)

This checks to see if AX is greater than WMS, and if it is, then it subtracts WMS from AX. Otherwise, if WMS is greater, it subtracts AX from WMS. Does that make sense?

Cheers!

Genevieve

sue ponter

Hi Genevieve/Andree

I did as you instructed and tapped on cells, rather than Cut/coping and typing in myself, I got #BOOLEAN EXPECTED. I'm sorry I don't know what that means (head in hands emoji)

I'm hoping its me and I've put a space where one shouldn't be or something, hence the snippet.

Happy Days!! I would at this point settle for a negative answer :-) but I'm not sure how to do that either. I had to watch a VLOOKUP video yesterday to learn that!!!

Any more help from either of you would be greatly appreciated.

Thanks Sue

Genevieve P.

Hi @sue ponter

You've done it correctly, then! It's just in the wrong column type. Checkbox columns are expecting a 1, or 0 (checked or un-checked) called a "boolean value".

Try putting the formula in a Text column instead!

Cheers,

Genevieve

Dale Murphy

@sue ponter ... and to simplify the formula just a bit, try the ABS function to return the absolute value of the calculation.

dm

sue ponter

Hi All

I've now changed it to text and bingo it works. Yeehaaaa :-)

Thank you all soo much, and Happy Friday.

Cheers Sue

Andrée Starå

@sue ponter

Excellent!

You're more than welcome!

@Genevieve P. No, I don't mind! Teamwork! 🙌

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!