Functions VLOOKUP or MATCH

[senaka]

HI,

i have made tables for count and tracking bar-cord numbers. i am stuck with formula.

if you see attached file that has tow pages, count here and item list. what this suppose to do on page one under UPC adding numbers
it check on page tow item list and keep tracking.

whats not working now 1.it don't count quantity now 2. accrual count column not update by it self ( count here page )

i have very poor knowledge of formula in Oder to make this my friend help me.

Thank you in advanced.

[MrProgrammer]

[Tutorial] VLOOKUP questions and answers

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[morchat]

I am sending the corrected file.
changes:
1. Changing the formula in COUNT HERE column Status Check (corrected only to 10th row).
2. In ITEM LIST instead of the formula that creates * number * string I used user formatting (it will work only if UPC are numbers).
3. In ITEM LIST, the corrected formula in the Actual count column.

Good luck

[senaka]

Hi, thank you and really appreciate helping out me for this matter

I opened that file and its not working properly. This is how it supposes to work for me.

What I am trying to do track and count under “UPC” number and “QTY” on item list table.

For example i have first row under “UPC” number 3 quantity 2, if, you go to “COUNT HERE”

Page we gonna adding numbers through bar code reader, under “UPC” column, while we add number 3 will appear tow times randomly, only I want this result update actual count column in “ITEM LIST” table. In this case same row i have umber 3 “actual count” has to be updated: 2.

[morchat]

In my answer I only corrected your formulas, which seemed to me unnecessarily complicated.
Forgive me but I do not understand the idea of your project.
What is primary in your project and what is secondary?
The COUNT HERE sheet has 9998 lines. This is probably the basic data set (primary).
The ITEM LIST sheet is probably a secondary sheet. The user enters UPC code and quantity (QTY) and should see the status of this item (subtracting QTY from Qantity actual count)?
If this is the case, STATUS CHECK should appear in the ITEM LIST sheet entries.
Or maybe my English is too weak for understanding?

[senaka]

Hi, first of all you don't have to ask forgiveness, its all my fault also my poor English, you forgive me for all that please.

let me explain like this, forget all what i did, I approach stupid way to my project.
This is all my project:
Lets say this is for production line, they make boxes.
One table we have information : ID NUMBER, QUANTITY
End of the line i have to count how many they done.
only thing it could be, one ID number different quantity always.
for example: ID = 300567 QTY = 6
that's why i add another column " actual count "
That's all about my project, and we adding back those"ID" numbers
through bar code reader.

[keme]

Is this something you use for counting between production and storage?

If so, I don't see what it is that doesn't work. There seems to be a few unneeded/confusing columns:

• Item list - I cannot see the purpose of the New item column. Please explain!
  Count here - The Status check is not strictly necessary, unless all items of one type must always be counted in one go, and there is a risk of counting a batch twice. The "Actual count" formula handles multiple entries for the same product type.

At the end of a run (between shifts, when a full truckload goes to storage, etc.) you'd copy the actual count column, and "paste special" to QTY with only the "Numbers" category selected and operation "Add" enabled, then clear the list in "Count here" for a new run.

The "new run" procedure, as outlined above, could be automated. However, I see no significant gain from that. The copy/paste-special operation only takes a few keystrokes and there is little risk of making mistakes.

Is there something I am missing, or did I misunderstand it entirely?

[morchat]

I would like to think about whether to use the pivot table technique for this problem.
I expanded the COUNT HERE sheet a bit and created an example of a new Pivot sheet with an example of pivot table.
COUNT HERE contains the right formulas for row 40th inclusive.