Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

meatpath: Feb 13, 2003

My question:

I have a list of several hundred objects that are posted each week at work from two different outside vendors. Each list is of the same objects, but each vendor abbreviates some of them differently.

For example, vendor #1 sends me a list that looks like this in a column:

code:

Name A
Name B
Name C
Name D
Name E

While vendor #2 sends me a list of the exact same objects that looks like this:

code:

Name A
Nm. B
Nm. C
Name D
Nm. e

Each list has corresponding data in adjacent columns. Basically, I want to be able to combine both lists - since they are listing the same objects - and transfer all data into one worksheet that can be analyzed. The list of objects will be exactly the same each week, so static rules for cell changes can be utilized. The problem is that, when the second vendor abbreviates objects, it throws them out of sync alphabetically and does not allow for easy sorting and combining.

I have never really gotten too deep with macros in Excel before, but I'm thinking all I really need is basically a "Find and Replace" macro that can have a set of rules on what objects are to be replaced each week in list B to make it exactly the same as list A. In other words, every week I will need to change the exact same abbreviation into its full name. Something like "Change 'Nm. B' to 'Name B', then change 'Nm. C' to 'Name C', then change 'Nm. e' to 'Name E'" and so on.

I hope that makes sense. I'm not asking anyone to write the macro, I just don't really know where to start or if this is possible. Obviously, the easier solution would be to just implement consistency between the sources, but we don't really have any control of how the vendors setup their database.

# ¿ Jan 15, 2010 21:43

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 14:53

meatpath: Feb 13, 2003

Sub Par posted:

Edit: if I'm not understanding it right, you could probably just turn on the macro recorder and then manually do all the finding/replacing you want, and then stop the recorder and have a look at the code in the vba editor. It's pretty straightforward to adapt the recorded macro for your own purposes.

I actually started this process, but then before I got very far I realized that I could solve my problem by simply building a "corrected" list that I keep in a separate file and just copy and paste onto the lovely list every week before actually sorting it alphabetically, that way I can preserve the corresponding data during the sort. Not sure why I didn't think of this solution before, but whatever. Thanks for the response.

# ¿ Jan 16, 2010 05:03

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread