• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup values with additional checking

Junarkar

Member
Hi all

I have an excel file which has two sheets. 1st sheet with data, 2nd sheet is used to enter bar codes (not manually but with scanner).
While entering a bar code, I need two figures - Location (to which that product will be delivered) and Qty. (the total qty. to be send).
I need to send same product to multiple locations. So product bar code will repeat many times.

Example;
Product 1237799 is going to two location - Max123 (3 qty.) & Act123 (2 qty.).

Desired Output given below - User will keep feeding only the bar code;


63992

The activity goes like this;
User Scan the bar code,
Req. qty & Location need to be fetched from Data sheet,
Scan qty. is, number of times we scan a particular bar code which keeps increasing,
balance is the difference between scan qty and required qty.

Once the balance qty. becomes zero then second location need to come. If total qty. has reached and again the user tries to feed the bar code, then some kind of warning message should flash like, "Invalid". Bar code will be entered in random not consecutively as shown in the example.

It would be great if some of you could give some advice if it is possible to achieve because the manual work which we are currently doing is huge since the SKUs count could be even 20k at times. This will help us to segregate the stock quickly and dispatch to various locations. Sample worksheet is attached.

Thanks a tone in advance.
 

Attachments

  • Sample Worksheet.xlsx
    29.4 KB · Views: 6
Junarkar
Other sample ...
Because I do not have barcode reader, I did a demo...
Press [ Do It ]-button in 'scan sheet'
You will see how do it works.
This demo runs with 'random Item code'.
Do You really need every steps of balance?
... I didn't check everything!
 

Attachments

  • Sample Worksheet.xlsb
    32.8 KB · Views: 2
Junarkar
Other sample ...
Because I do not have barcode reader, I did a demo...
Press [ Do It ]-button in 'scan sheet'
You will see how do it works.
This demo runs with 'random Item code'.
Do You really need every steps of balance?
... I didn't check everything!

Hi vletm

Thanks alot for the time you have spent to write all those codes.

Can you help me to do it in this way;
if we enter a bar code in scan sheet, it should check what is the first location to which we need to dispatch and its required qty.
Again if we enter the same bar code it should check if the number of times we enter the bar code is equal to the required qty. Let me give an example from Data sheet;
ITEMReq. qty.Location Name
8907352577582​
3​
Max-Sana Arcade-Changanassery
8907352577582​
1​
Purackal Fashions-Titus-Thiruvalla-Max
8907352577582​
1​
Max-Indian Ladder Mall-Manjeri
8907352577582​
5​
Kurisupally Junction-Pala-Max

Here item 8907352577582 is going to 4 locations.

So in the scan sheet while we enter this bar code first it should take Location - Max-Sana Arcade and it's required qty., 3 in this case.
Scan sheet will looks like this;
Item codeReq QtyScan QtyBalanceLocation
8907352577582​
3​
1​
2​
Max-Sana Arcade-Changanassery

If I enter the bar code again, It should check if the req. qty. of the first location is satisfied. If not it should take the first location again.
Once the req. qty of first location is equal to the scan qty. then it should search if the same bar code is mentioned for any other location. Purackal Fashions-Titus in this example.

In case we are trying to enter the bar code again and already all the required qty has been reached, then some kind of warning should flash like " Invalid", Max. Qty. reached".

Is this possible?

Thanks
 
Junarkar

Your: if we enter a bar code in scan sheet ... as I wrote, I do not have a bar code. That's why I 'entered' those item codes 'my way'.
I have other image, how to enter something ... but ... it won't be just Your way then extra coding for me ...
There could be
a) one cell, there You could enter Your bar code
b) list, there You could add in one column as many bar codes as want --- someway same as in my sample

This samples functionality should be someway same as You've written two times.
It brings to 'scan sheet' only those rows from 'data' which needs.
As well as it marks with yellow, those balances which still could/should use before next row from 'data'.
Of course, sorting of those 'item codes' could be more clear for You.

Your: In case we are trying to enter the bar code again and already all the required qty has been reached, then some kind of warning should flash like " Invalid", Max. Qty. reached".
Screenshot 2019-11-17 at 11.08.18.png So far, You'll get this kind of note ... did You noticed that?

Many things are possible.
 
vietm

can this work only if I manually enter bar code in scan sheet (the bar code are the same as those in "A column" in Data sheet).

Thanks
 
Junarkar
Yes ...
... fill Your ITEM CODEs (one-by-one) to cell G1 and press <ENTER> (after every fill)
'Bonus': cell F1 shows status.
The 'scan sheet' needs 'manual reset'.

There are also MY needed test options!
[ Demo ] - runs itself
[ ReSet DATA ] - resets data-sheet
 

Attachments

  • Sample Worksheet.xlsb
    40.2 KB · Views: 4
Hi vietm

Just a clarification what's purpose of demo button. It randomly fills the data and I didn't understood whats happening.
 
Junarkar
[ Demo ]
Did You read that smaller font text? MY needed test options!
Because, I don't myself want to fill - fill - fill many ITEM CODEs to that G1-cell - I did that for TESTING my code!
It could work same way if You would fill those ITEM CODEs one-by-one to G1-cell.
It would work same way as You would use that MANUALLY, if try to add ITEM CODE which is not valid - it'll give a note!
as well as that [ ReSet DATA ] is for my testings --- You can delete those anytime!
 
Junarkar
[ Demo ]
Did You read that smaller font text? MY needed test options!
Because, I don't myself want to fill - fill - fill many ITEM CODEs to that G1-cell - I did that for TESTING my code!
It could work same way if You would fill those ITEM CODEs one-by-one to G1-cell.
It would work same way as You would use that MANUALLY, if try to add ITEM CODE which is not valid - it'll give a note!
as well as that [ ReSet DATA ] is for my testings --- You can delete those anytime!
Oh...ok.. I missed that..

Thanks alot for the help vIetm.. I went through your codes, even though can't figure out a thing from it :). I can see the time you have spent to write all those lines. And also thanks for correctly understanding my requirement. I should say that I have surprised to see your solution. It will save so many man hours. You are awesome...

Thanks Again.
 
Back
Top