Hi,
I have a data with some names and there IDs and a count against them. Few names are similar for ex. there is ALEXSALESANDCOMPANY and then there is ALEXSERVICES and both have different IDs and different counts. What I want is to have just one, be it ALEXSALESANDCOMPANY or be it ALEXSERVICES, both IDs mentioned against a single name and the count of both names added.
Would it be possible or would I have to do it manually which is like a impossible since the data is of about 900 entries.
It is going to have to be manual, but there is no reason for it to be totally manual. The attached shows one way to get some assistance.
First, put your data in an Excel Table because Excel Tables know how big they are. We will be using pivot tables to help with this project.
In Cell B1, you can enter a name like ALEX or SUSAN or BAKER. Column D contains the list of your companies. Column E contains the formula: =ISNUMBER(FIND(UPPER($B$1),UPPER([@Name]))) which is true if the Root Name in Cell B1 is contained in the name in Column D.
Column H:I is a pivot table that filters on TRUE. This gives a result in Column H of possible matches.
You will have to refresh the pivot table after entering a root name.
I suggest that when you find confirmed matches that you take them out of the excel tables. This process will reduce the list to look at for guessing good root names. Because it is an excel table, the pivot table will still work - it will re-adjust to the new excel table size.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks