K gents lets see if any of you can help.
I have 2 files.
File A: A list of 15000 accounts for all clients with multiple rows per client. Also the client name can appear in several form (i.e. Fedex and Federal Express). This file also has revenue totals by type in each row. Basically a data dump of sorts
File B: A list of 450 client names
My task: Provide revenue totals for the 450 clients in File B using File A as my data source.
How do I go about completing this without having to manually search file A by name for each client.
My current approach - filter client name column in file A using a unique word/string of text, sum up the total and add to file B
Is there a way to run a lookup using the above approach? Or a sumif formula?
I would standardize the names in File A (or a copy) and then use a sumif. Vlookup will only return the first line item, unless you complicate things - sumif is easy for aggregating like you intend.
How do I standardize them in file A? Its over 15000 rows long.
I come across this a lot in purchase price allocations. I send it back to the client and tell them to fix it.
you can do a find & replace to standardize the names. ctrl + f and replace all the Federal expresses with Fedex or vice versa. assuming you know all the iterations used by your client for a given copmany. then running the lookup should be easier
…there’s also this add-in from MS that looks like it could help…never tried it though.
http://www.microsoft.com/en-us/download/details.aspx?id=15011
When you standardize it, it is probably a good idea to create second column with the translated client names - in case you need to check for issues, recreate any similar analysis in the future, or provide it to a coworker/client.