#1
|
|||
|
|||
Help with Inventory-Job on the line Folks
Help with Inventory-Job on the line Folks I have a spreadsheet that has two worksheets...One has the physical counts taken at a store...The other worksheets is the master item table that contains the pricing, UPC's and other various information. I must match the Physical Worksheet UPC on one column in the Master Item Table as an exact match, or, match it to another column in the Master Table less the last digit in the Physical Worksheet UPC column. I have been working on this for two weeks and just can't figure it out. Can anyone help me with this? I have tried Crystal Reports, doing joins, etc. but I always keep coming back to Excel...I would be very grateful for any help...I wished I could attach the spreadsheet, but can't....Any help out there? |
#2
|
|||
|
|||
Have you considered using VLOOKUP()? It sounds like you'd have to nest it inside an IF(), along the lines of--
=IF( ISNA( VLOOKUP(PW_UPC, Master_Table, PW_UPC_Column, FALSE)), VLOOKUP(Trunc(PW_UPC/10), Master_Table_Alt, PW_UPC_Alt_Column, FALSE), VLOOKUP(PW_UPC, Master_Table, PW_UPC_Column, FALSE)) The IF() statement condition is whether or not VLOOKUP() fails trying to find the PW_UPC value in the first column of the Master_Table. If ISNA() returns True, it can't find it, and tries to find the PW_UPC value less the last digit [I'm assuming it's an integer, so division by 10 gives us what we want; if it's text, use something like Left(PW_UPC, Len(PW_UPC) - 1)] in the first column of Master_Table_Alt (presumably a sub-range of Master_Table), returning the value in whichever column is indicated by PW_UPC_Alt_Column. If ISNA() returns False, it found the PW_UPC in Master_Table and returns the value in whichever column is indicated by PW_UPC_Column. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need help with keeping an inventory organized w/ WORD or Excel? | CeltFan09 | Excel | 2 | 05-04-2011 05:46 PM |
First line on page is truncated (top of line) | OwlCat1212 | Word | 0 | 02-14-2011 03:24 PM |
I need help with keeping an inventory organized w/ WORD | CeltFan09 | Word | 1 | 01-31-2011 06:44 AM |
Line breaks at each line in Web text | ahazelwood | Word | 5 | 01-06-2011 02:37 PM |
Cannot delete line | adkr | Office | 1 | 04-07-2010 09:26 PM |