bassnsjp
11-25-2009, 09:55 AM
I have several Excel sorting issues that I believe are related. I'm using MS XP Pro and MS Office 2003. I do not have access to the internet from the computer that contains the code and data. So, I apologize upfront for having to write the scenario vice providing code.
Here is what I'm trying to do. I have several lists of computer hardware inventory. I need to compare the lists and determine:
records / items that match
records / items that are in the first list and not the second
records / items that are in the second list and not the firstOkay, I have the program developed that produces the results above HOWEVER, in order to generate valid results I need to have the data sorted in a similar manner. I have selected the Serial Number (SN) of the equipment as the primary key to sort by. Here are some of the issues with the data in the SN column:
Contains normal numeric data (53982, 2346509, etc.)
Contains numeric data with leading zeros which an apostrophe has been entered to treat the data as text ('430987, '00012, '0001, etc.)
Contains alphnumeric values (2SUA09367, CN-0G4447H-8790, etc.)The way in which I perform the comparsion is via a one pass through of both lists. By this I mean I take the first entry from list one compare it to list two if the entry:
is equal then I write it out to the MATCH-SN worksheet
if the entry from list one is greater then I write it out to the List1NFinList2 worksheet
if the entry from list two is less then I write it out to the List2NFinList1 worksheet
Then I increment the indexes to both lists appropriatelyNow because SN consists of various values, as stated above, when I sort by SN the results is inconsistent between the two lists. In that, list one may have an entry of '0001 in a different order than how it is sorted in list two. For instance, list one would have it at the very top while list two may have this record 30 or 40 records from the top which follows entries that appear should come after that entry. So, I need to find a common way to normalize the data (i.e. make them all text) and sort the data. I first attempted to set all SN data by copying the data to another column using the =Text(A2, "###") and then tried using =Text(A2,"@"). Both of which caused the leading zero to be removed from certain cells, but yet not all. For instance '0001 became 1 however, '0028735609 retained the leading zeros. Very confusing and frustrating.
I would like to be able to convert all the data to text and have the leading zeros retained. I believe once this is accomplished the sort will produce the desired order and subsequently the report will produce valid results.
Thank you in advance for your time and advice.
Here is what I'm trying to do. I have several lists of computer hardware inventory. I need to compare the lists and determine:
records / items that match
records / items that are in the first list and not the second
records / items that are in the second list and not the firstOkay, I have the program developed that produces the results above HOWEVER, in order to generate valid results I need to have the data sorted in a similar manner. I have selected the Serial Number (SN) of the equipment as the primary key to sort by. Here are some of the issues with the data in the SN column:
Contains normal numeric data (53982, 2346509, etc.)
Contains numeric data with leading zeros which an apostrophe has been entered to treat the data as text ('430987, '00012, '0001, etc.)
Contains alphnumeric values (2SUA09367, CN-0G4447H-8790, etc.)The way in which I perform the comparsion is via a one pass through of both lists. By this I mean I take the first entry from list one compare it to list two if the entry:
is equal then I write it out to the MATCH-SN worksheet
if the entry from list one is greater then I write it out to the List1NFinList2 worksheet
if the entry from list two is less then I write it out to the List2NFinList1 worksheet
Then I increment the indexes to both lists appropriatelyNow because SN consists of various values, as stated above, when I sort by SN the results is inconsistent between the two lists. In that, list one may have an entry of '0001 in a different order than how it is sorted in list two. For instance, list one would have it at the very top while list two may have this record 30 or 40 records from the top which follows entries that appear should come after that entry. So, I need to find a common way to normalize the data (i.e. make them all text) and sort the data. I first attempted to set all SN data by copying the data to another column using the =Text(A2, "###") and then tried using =Text(A2,"@"). Both of which caused the leading zero to be removed from certain cells, but yet not all. For instance '0001 became 1 however, '0028735609 retained the leading zeros. Very confusing and frustrating.
I would like to be able to convert all the data to text and have the leading zeros retained. I believe once this is accomplished the sort will produce the desired order and subsequently the report will produce valid results.
Thank you in advance for your time and advice.