Question description:
This user has given permission to use the problem statement for this
blog.
I work at an Investment Bank, and am currently conducting a churn analysis. Essentially, this requires us to track which customers cancelled their contracts with our client. The issue is that the data is listed by account in one column, but that there are multiple accounts for a given customer. For example, if Walmart were a client, there might be 50 accounts for Walmart, each in a different city. How might we be able to combine all these entities, without running a manual process, as the file contains well over 60,000 accounts?
Solved by T. L. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
20/11/2017 - 08:49
Hi, How can I help you
Excelchat Expert
20/11/2017 - 08:50
Since this is a very challenging one, I need to understand it clearly. Could you please paste the same values here in this online excel.
Excelchat Expert
20/11/2017 - 08:52
Kindly reply
User
20/11/2017 - 08:52
Unfortunately, I don't think I am able to provide you with that
User
20/11/2017 - 08:53
Due to confidentialility laws
Excelchat Expert
20/11/2017 - 08:53
Yes, I can understand.
User
20/11/2017 - 08:53
But I will give an example of what we're looking to do
Excelchat Expert
20/11/2017 - 08:53
Yes, any sample data would be fine. Ill give you the solution
User
20/11/2017 - 08:53
One moment please
Excelchat Expert
20/11/2017 - 08:54
yes
User
20/11/2017 - 08:58
What I put in on the right represents a simplified version of our file
User
20/11/2017 - 08:58
Essentially, what we want to do is make sure all the Wal - Marts count as one account, and all the Targets count as one account
User
20/11/2017 - 08:58
And that we can account for some mispellings, although we're aware it won't be perfect
Excelchat Expert
20/11/2017 - 09:00
ok
User
20/11/2017 - 09:00
It's a tough problem - we're aware
User
20/11/2017 - 09:00
A bunch of my colleagues and I have been trying to find solutions
Excelchat Expert
20/11/2017 - 09:00
Do all the Account name has any patter like what you provided ?
Excelchat Expert
20/11/2017 - 09:00
XXXXX - Tulsa
User
20/11/2017 - 09:01
In a lot of cases
User
20/11/2017 - 09:01
But not all
Excelchat Expert
20/11/2017 - 09:04
Okey, please proceed, let me trey to understand your problem completely
User
20/11/2017 - 09:05
I added a sample from my file
User
20/11/2017 - 09:06
We'd want to be able to reconcile at least all the 1st choice accounts
Excelchat Expert
20/11/2017 - 09:09
Okey, Please continue your explanation.
Excelchat Expert
20/11/2017 - 09:14
Please proceed
Excelchat Expert
20/11/2017 - 09:15
I need little more insight to this, to provide you solution
Excelchat Expert
20/11/2017 - 09:23
Please refer the formula I have worked on column H
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.