Make multiple replacements in Excel

[kranky]

I have the task of taking a 10,000 line Excel file and boiling it down for an executive summary. To do this, I need to make a bunch of text replacements so the subtotaling will work.

For example, one column might contain the following:
Jr. Technician
Tester
Junior Tech
Bob Smith
Tech
Senior Tech
...and 10 more variants

For the summary, all those rows have to be lumped together. Once I go through and change all those different values to "Tech", I can use subtotals to combine the data.

I need a way to build a list of text replacements and apply it to the file each time I have to get a new update. The list of substitutions will be 98% the same every time and I don't want to have to manually do it one replacement at a time. If I have a list, I can easily update it if a new variation shows up.

 

[mayest]

There are probably several ways to do this. Given what you have said, and assuming that you can't make a pivot table do it (I'd try that first), then I would suggest using VLookup(). Create a table with the original text in the left column and the replacement text in the right column. Then, add a column to your original data set and do the look up.

 

[PowerEngineer]

Hmmm... I must learn about pivot tables some day...

I always seem to come at Excel problems from an odd angle, so with that as a warning here's what I'd try.

It seems to me that you need to start with a list of all the variants (e.g. Junior Tech, Bob Smith, Tech,...). I'd put this on a separate wooksheet (say in column A). In column B, I would use a SUMIF calculation (looking for rows in the variant column on the first sheet that match the variant (right next to it) in column A and then summing whatever column on the first sheet that you want to subtotal). In column C, I would put the corresponding "lump" value (e.g. Tech,...) for that variant in column A

Skip a column and put your list of "lump" values in column E. Now use SUMIF again in column F; this time looking for rows in column C which match the "lump" value (right next to it) in column E and then summing column B.

You should end up with subtotals for each variant (column B) as well as subtotals for each "lump" value (column F). I'd total these subtotals and compare them to the total of the column in question on the first sheet. If they don't match, then you must missing a variant value in column A.

Done right, you should be able to just copy/paste the new file into the first sheet, update your variant names (with their "lump" values), update the "lump" values, (extend the SUMIF calculations), and be done. No replacements necessary...