StlSmiln
06-27-2012, 12:00 PM
Hi All,
I had previously been trying to write a macro, http://www.vbaexpress.com/forum/showthread.php?t=42676&page=1, to help me begin to accomplish these tasks. But after spending a bunch of time on it and not getting a usable code, because of time constraints, I put that effort on PAUSE and switched to brute force...but now I'm having trouble with it (and while new to VBA, I feel pretty proficient in excel otherwise).
The task: it's an inventory. I want to analyze and compare data taken by scanners in the field, to a list of known assets prior to the inventory, and find those assets that we have still yet to find (are still missing).
[We have a program that is 'supposed' to handle this but I don't trust the data I get for myriad reasons.]
Need to:
Compile all of the data scanned in the field into one file (Scanned Assets). Scanner data is saved in .txt (tab delimited files). [This is what I was trying to accomplish with VBA, but that's on pause now b/c I just have to get this done.]
Compare 'assetTag' of Scanner Assets against those on the Known Asset List and find status.
Found - Scanned Assets that are ON the Known Asset List
New - Scanned Assets that are NOT ON the Known Asset List
Missing - Assets on the Known Asset List that have not been Scanned yet.Well, I'm having trouble doing that, because this 'program' has made me not trust the data so now I'm being hyper-vigilant.
Problem (the trouble I'm having): I've attached a very watered down version of the workbook I'm now using with all irrelevant data erased, and most of the records deleted to abbreviate.
'StillMissing_CopyALLAssets' Sheet - represents the original Known Assets List. Sheet has 1000's or records. I've added 3 columns to it:
E - modifies columnD to make the assetTags uniform across all sheets, i.e. 102303 changed to 000102303 as text
A - uses an '=If(Isna(vLookup...' to compare the assetTag of this sheet to the assetTag of 'MasterInv... Sheet' and display "Found" is it's on both sheets, and "Missing" is the asset is not on the 'MasterInv... Sheet.'
L - just copies A so that I can use vLookup on the 'CrossCheck' sheet because I'm not feeling trustworthy.
'MasterInv_ComparedALLAssets' Sheet - represents a compilation of all data taken from scanners in the field...and ALSO shows a status as given by the 'program' that I really don't trust (the programs assigned status is in Column P). I've added the same three columns, except:
A - Returns "Found" if it's on both sheets and "New" if the asset is not on the 'StillMissing...' sheet. I've also included conditional formatting to identify when the status that excel returns is different from the status that the program gave. This is one of the biggest reasons why I've abandoned using the program. [If ANYone can tell me that I've done something wrong and should trust the program, PLEASE LET ME KNOW...my life would get much easier!!!]
'CrossCheck' Sheet - I'm trying to verify that my "Found" statuses on each of the first sheets is consistent. I'm getting #N/A errors some of the time...maybe a circular reference? Can't figure it out.Maybe I've gotten silly about this because that program would give me a missing asset list of 1500 items one download, and then 500 the next. At this point, if someone would just tell me that the data on the first two sheets IS correct then I don't really need the third...but I want to KNOW what I have and what I'm still missing.
I know I've written another ridiculously long post, but PLEASE PLEASE HELP ME! I'm grateful for all that you can give me!!! :bow:
I had previously been trying to write a macro, http://www.vbaexpress.com/forum/showthread.php?t=42676&page=1, to help me begin to accomplish these tasks. But after spending a bunch of time on it and not getting a usable code, because of time constraints, I put that effort on PAUSE and switched to brute force...but now I'm having trouble with it (and while new to VBA, I feel pretty proficient in excel otherwise).
The task: it's an inventory. I want to analyze and compare data taken by scanners in the field, to a list of known assets prior to the inventory, and find those assets that we have still yet to find (are still missing).
[We have a program that is 'supposed' to handle this but I don't trust the data I get for myriad reasons.]
Need to:
Compile all of the data scanned in the field into one file (Scanned Assets). Scanner data is saved in .txt (tab delimited files). [This is what I was trying to accomplish with VBA, but that's on pause now b/c I just have to get this done.]
Compare 'assetTag' of Scanner Assets against those on the Known Asset List and find status.
Found - Scanned Assets that are ON the Known Asset List
New - Scanned Assets that are NOT ON the Known Asset List
Missing - Assets on the Known Asset List that have not been Scanned yet.Well, I'm having trouble doing that, because this 'program' has made me not trust the data so now I'm being hyper-vigilant.
Problem (the trouble I'm having): I've attached a very watered down version of the workbook I'm now using with all irrelevant data erased, and most of the records deleted to abbreviate.
'StillMissing_CopyALLAssets' Sheet - represents the original Known Assets List. Sheet has 1000's or records. I've added 3 columns to it:
E - modifies columnD to make the assetTags uniform across all sheets, i.e. 102303 changed to 000102303 as text
A - uses an '=If(Isna(vLookup...' to compare the assetTag of this sheet to the assetTag of 'MasterInv... Sheet' and display "Found" is it's on both sheets, and "Missing" is the asset is not on the 'MasterInv... Sheet.'
L - just copies A so that I can use vLookup on the 'CrossCheck' sheet because I'm not feeling trustworthy.
'MasterInv_ComparedALLAssets' Sheet - represents a compilation of all data taken from scanners in the field...and ALSO shows a status as given by the 'program' that I really don't trust (the programs assigned status is in Column P). I've added the same three columns, except:
A - Returns "Found" if it's on both sheets and "New" if the asset is not on the 'StillMissing...' sheet. I've also included conditional formatting to identify when the status that excel returns is different from the status that the program gave. This is one of the biggest reasons why I've abandoned using the program. [If ANYone can tell me that I've done something wrong and should trust the program, PLEASE LET ME KNOW...my life would get much easier!!!]
'CrossCheck' Sheet - I'm trying to verify that my "Found" statuses on each of the first sheets is consistent. I'm getting #N/A errors some of the time...maybe a circular reference? Can't figure it out.Maybe I've gotten silly about this because that program would give me a missing asset list of 1500 items one download, and then 500 the next. At this point, if someone would just tell me that the data on the first two sheets IS correct then I don't really need the third...but I want to KNOW what I have and what I'm still missing.
I know I've written another ridiculously long post, but PLEASE PLEASE HELP ME! I'm grateful for all that you can give me!!! :bow: