Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-10-2011, 02:51 PM
prokf prokf is offline Help with Inventory-Job on the line Folks Windows XP Help with Inventory-Job on the line Folks Office 2007
Novice
Help with Inventory-Job on the line Folks
 
Join Date: Mar 2011
Posts: 1
prokf is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 03-11-2011, 11:20 AM
CGM3 CGM3 is offline Help with Inventory-Job on the line Folks Windows XP Help with Inventory-Job on the line Folks Office 2007
Advanced Beginner
 
Join Date: Oct 2009
Posts: 38
CGM3 is on a distinguished road
Default

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.
Reply With Quote
Reply

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
Help with Inventory-Job on the line Folks 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:28 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft