Good morning or afternoon! I run through tons of excel reports 
that are sent to me regarding different part references.
I try try to tidy the data to unearth the gems of info that are hidden away in there...
The main issue that I have is that the part references are written down (depending on their whim that particular day!) differently & dashes, slashes, spaces, prefixes & suffixes can be either omitted OR added... so an ABC123 could be written as BC123, ABC-123, ABC_123, ABC12/3, etc Grr
Also, people send the excel reports to us, but organise them one day by column, one day by row, and on other days - just so that it looks "pretty". I'm being gradually swamped as there are more and more reports that I have to run through.
My botched solutions to trying to find where part references are the same have been to:
1. Do a ctrl F using wildcards i.e. search for A*B*C*1*2*3. BUT that also brings up ABCD123 if I'm searching for variants of ABC123 so no good
2. If I'm having to cross reference larger lists:
-concatenate all of the data on each line of info from the report into one column
-run a macro on that cell to remove everything apart from the Alpha (A-Z,a-z) or Numeric (0-9) characters
-do a partial vlookup if there is a larger amount of info to cross ref (=vlookup,"*"&C2&"*",define range,define look up column,false)
-make sure that the cell doesn't exceed 255 characters or the lookups won't work
The trouble I'm running up against is that I'm running out of time to do it this way around. And I can't automate it as people won't stick to sending the info in in the same way each time. I even tried sending out excel templates for them to stick to (that provoked at least a laugh from them!)
In an ideal world, I'd somehow have:
-a special ctrl F function which would find search strings whilst ignoring any characters that are not AlphaNumeric
......eg search on my special Ctrl Find function for search string BC123 would find BC123, ABC-123, ABC_123, ABC12/3
-a partial vlookup function that did the same cross-referencing but only for Alphanumeric characters for the longer lists
If somebody could help me out of this muddle, I'de be all ears, immensely grateful & possibly able to get through my backlog of work!
Best
Neil
that are sent to me regarding different part references. I try try to tidy the data to unearth the gems of info that are hidden away in there...
The main issue that I have is that the part references are written down (depending on their whim that particular day!) differently & dashes, slashes, spaces, prefixes & suffixes can be either omitted OR added... so an ABC123 could be written as BC123, ABC-123, ABC_123, ABC12/3, etc Grr
Also, people send the excel reports to us, but organise them one day by column, one day by row, and on other days - just so that it looks "pretty". I'm being gradually swamped as there are more and more reports that I have to run through.
My botched solutions to trying to find where part references are the same have been to:
1. Do a ctrl F using wildcards i.e. search for A*B*C*1*2*3. BUT that also brings up ABCD123 if I'm searching for variants of ABC123 so no good
2. If I'm having to cross reference larger lists:
-concatenate all of the data on each line of info from the report into one column
-run a macro on that cell to remove everything apart from the Alpha (A-Z,a-z) or Numeric (0-9) characters
-do a partial vlookup if there is a larger amount of info to cross ref (=vlookup,"*"&C2&"*",define range,define look up column,false)
-make sure that the cell doesn't exceed 255 characters or the lookups won't work
The trouble I'm running up against is that I'm running out of time to do it this way around. And I can't automate it as people won't stick to sending the info in in the same way each time. I even tried sending out excel templates for them to stick to (that provoked at least a laugh from them!)
In an ideal world, I'd somehow have:
-a special ctrl F function which would find search strings whilst ignoring any characters that are not AlphaNumeric
......eg search on my special Ctrl Find function for search string BC123 would find BC123, ABC-123, ABC_123, ABC12/3
-a partial vlookup function that did the same cross-referencing but only for Alphanumeric characters for the longer lists
If somebody could help me out of this muddle, I'de be all ears, immensely grateful & possibly able to get through my backlog of work!
Best
Neil
