Macro that checks a list, and gives user prompt for action

Thanks for the reply.

The Data tab is the latest update and I use the pivot table to update the Table tab information on a daily basis. The table tab serves as a historical record of when items moved to phase 4, so has thousands of rows, hundreds of which do not show any item with quantities anywhere other than phase 4. Hundreds of others show no change from one day to the next and that's the part that seems the easiest to program for; simply look for the item number, check the quantity is the same, in the same column and turn the text say, blue.

Where it gets complicated for me is when thinking about the items that have changed phase, they usually have a +/- 200 variance in quantity and of course, move from one column to another. I haven't ever tried populating a dialog box with anything other than a simple list of cell lookups.

It gives me hope that you say things like this are feasible.

Originally Posted by millz

Edit:
Another question, for Item 5, it has 100 under phase 3 in "Data" worksheet, and has 100 under both phase 3 and 4 in "Table" worksheet. You mentioned it's because half of it is being moved to phase 4? How do you determine that 'half' ?

For some items, I may choose to move all, or just some of the quantity to phase 4 on a given date. Item 5 is an example of a time when i might choose to move only part of the total amount. As you can see the data table cannot see what is at phase 4 and is requesting that the information be updated from 200 at phase 3 to only 100 at phase 3.

(In macro terms, I think the easiest way to do it would be to have the code look for a quantity in phase 4 and then check to see if the date is relevant - the only time it is relevant is when the date is "yesterday"). The macro can then see that this is the reason for the reduction, and after applying an acceptable variance (say P3+P4 +/-200) would make the change without prompt

I don't really understand the purpose of the pivot table, which seems to be summing the values (qty in this case) for each phase. Am I right to say that whatever appears in "Data" tab should be updated/appended into "Table" tab?

Edit for Item 5 again: I do not see any 200 being shown, where is it?

Originally Posted by millz

I don't really understand the purpose of the pivot table, which seems to be summing the values (qty in this case) for each phase. Am I right to say that whatever appears in "Data" tab should be updated/appended into "Table" tab?

Edit for Item 5 again: I do not see any 200 being shown, where is it?

The only sole purpose of the pivot table is to provide a printable form for me to have in front of me, when updating the Table tab. (These two tabs are in reality, on differenct workbooks.


Item 5 would have shown on the Data table as 200 on the 21/august. 100 of it got moved to phase 4 on the 22nd august so the update for the data tab shows only 100 left as it can't see phase 4. Sorry that part isn't clear, i hope i cleared that up.

Hi jay, sorry was having a busy weekend. Are you able to provide a more 'filled' sample file, preferably with 20 or more rows that requires updates, and only show what is needed for the macro. And preferably with a 3rd worksheet that shows the results of the updated table. Right now I'm still semi guessing what the macro should do

Thanks for finding the time to help me out even though you are busy.

I can certainly provide an expanded example, though it will have to be tomorrow when I can get back to my computer. I can't edit excel files on iPhone.

I will elaborate on what the whole thing is here though, basically the data table is my simulation of a stock sheet from a warehouse. Each day I need to take their latest update of what movements they did with the stock, moving through the 3 phases they handle (phases are things like repackaging the items Etc.) my sheet, represented by the first tab in the example workbook I posted, needs to basically match their daily update with one or two differences. 1, I need to keep previous items on record to view back later, the data they send me doesn't keep a record once the item has left their warehouse. 2. My sheet has a phase that theirs doesn't. Phase 4 is actually my column for showing which items I want them to deliver to me, on which date, so theirs has no record of phase 4 because at that stage the items have left their warehouse.

The daily inventory of what is at the warehouse and what isn't, including the phase changes and the new intakes is currently taking over an hour by hand, I think a macro could take this down to minutes by checking off the say, 90% of items that don't change phase, then giving the user an itemised report and asking to confirm any changes that it has noticed.

Eg 1 list giving information about new intakes "item 7 has been added to phase 1"
1 list giving details on phase changes like "item 5 100units has been moved from phase 1 to phase 3, new quantity 250.
And another list reporting "item 2 100 units has been removed from phase 3"

I'm wanting to either get a confirmation button for each individual item, or some way to select which changes to make, click confirm and then highlight the ones not changed in some way.

In the case of anything but a removal from phase 3, there may be investigated needed, say new quantity is too low or something, but the phase 3 is much simpler really, it either has been moved out of the warehouse in full, or part of it has. In every case of this, phase 3 changes just need to be checked to see if there is a valid move date.

I hope this makes the workings clearer, I will post an elaborated example tomorrow

I have uploaded an expanded example sheet. The link is below (though i think the original link in the first post will also take you to the bigger example.

The new workbook has a data tab for day 1, a table for day 1 and then a data tab for day 2 to show the progress through each update. I have included a tab with an explanation and details of all updates that are being made. This is about the closest thing i can provide to actually being the sheet i use (for data protection i can't show you the original, and besides, it's a 120mb behemoth).

Any more questions let me know, I will start to work on my own code at some point tomorrow and i'll share it as i go.

http://www.mediafire.com/?2c1zzndxc5x16v1

How do you determine a unique line? e.g. Rows 41 and 42 are both Customer Three and Item code are both 23. It's like almost impossible to programmatically differentiate them.
Unless you apply a logic like,
1) this item code appears more than once
2) for each of the appearance, check if the respective customer has a matching qty, if yes, that line is ignored
3) if no, display every appearance (that are not previously/already ignored) and some human intervention will be required to determine which is the line that should be used/updated

It may sound very feasible to be done, but would be quite complicated to be done through programming, and will still require human decision to continue.

Another easy way would be, if you have and is able to add a unique document number, like a sales order number, purchase order, or a transfer/delivery order etc. to each row.

It would be complicated to add another identifier to the database. I have thought about this kind of thing before so I'm glad you are thinking about things like that.

Maybe the answer here lies in simplifying the system a little. The only reason that there would be two item 23's is that at some point I have tried to split the two quantities for a phase 4 movement. So, if I simply remove any doubling up like this, would that help?

The only instances where item 23 would be on more than one row would be when some of it has moved to phase 4 already and wouldn't appear on the data updates.

Originally Posted by jayherring86

Maybe the answer here lies in simplifying the system a little

I don't really get what you mean by simplifying the system. If you can make sure that in both Data and Table Tab, each Customer number + Item code combination only appears once, then it would be much easier to code.