JulianBorisov
New Member
- Joined
- Jan 21, 2017
- Messages
- 2
Hello all,
I am new to the forum and excel as a whole and I am experiencing issues formating a large sets of data in excel. I was wondering if anyone could help me by giving me an idea or explain why certain things are not working.
I am currently trying to format a 200 000 rows excel file with the format:
Product level 1 Pruduct leve 2 Product level 3 Country Customer Date Units Sales
The names of the customers and products are in gibberish and one of the tasks is to assign them a name such as Client 1, Client 2.... Product 1, Product 2... etc., thus making it clearer to understand. This is where I have stumbled upon a problem because I have no idea how to replace the names of all Clients. What I did so far is to copy all the clients, paste them in a new sheet, remove duplicates to show 5798 unique clients and tried to write Client 1, Client 2... next to their names with the idea to later find how I can substitute all of them at once. But now that I have removed the duplicates, the numbers counting the rows are blue and the autofill function is not working( I cannot extend the Client 1, Client 2 cells down to fill them with Client 3, Client 4 and etc.). Also I have no idea if this is the right thing to do at all because even if I manage to do it, I dont now how to further proceed with the substitution of the names.
The next task is to prepare tables to better show the results, but I have no idea what that Product Level is as it contains different "products" in it.
The third task is to make an ABC analysis - to devide all customers in 3 major groups, one representing 50% of all sales, one representing 30% and one 20%. This I thought of doing by finding the average sales and with an if function to find all values above 30% of the average and put them in category A, else if value is < than 30% but > than 20% to go to category B and else go to category C. But I have no idea if it can work in excel like that (with 2 else conditions that contain an if as well).
The task is part of a course at work I am taking to increase my level of competence so it is quite important for me to be able to do these kind of functions.
I do hope someone is willing to help me out here and give me their insight on the topic.
I am new to the forum and excel as a whole and I am experiencing issues formating a large sets of data in excel. I was wondering if anyone could help me by giving me an idea or explain why certain things are not working.
I am currently trying to format a 200 000 rows excel file with the format:
Product level 1 Pruduct leve 2 Product level 3 Country Customer Date Units Sales
The names of the customers and products are in gibberish and one of the tasks is to assign them a name such as Client 1, Client 2.... Product 1, Product 2... etc., thus making it clearer to understand. This is where I have stumbled upon a problem because I have no idea how to replace the names of all Clients. What I did so far is to copy all the clients, paste them in a new sheet, remove duplicates to show 5798 unique clients and tried to write Client 1, Client 2... next to their names with the idea to later find how I can substitute all of them at once. But now that I have removed the duplicates, the numbers counting the rows are blue and the autofill function is not working( I cannot extend the Client 1, Client 2 cells down to fill them with Client 3, Client 4 and etc.). Also I have no idea if this is the right thing to do at all because even if I manage to do it, I dont now how to further proceed with the substitution of the names.
The next task is to prepare tables to better show the results, but I have no idea what that Product Level is as it contains different "products" in it.
The third task is to make an ABC analysis - to devide all customers in 3 major groups, one representing 50% of all sales, one representing 30% and one 20%. This I thought of doing by finding the average sales and with an if function to find all values above 30% of the average and put them in category A, else if value is < than 30% but > than 20% to go to category B and else go to category C. But I have no idea if it can work in excel like that (with 2 else conditions that contain an if as well).
The task is part of a course at work I am taking to increase my level of competence so it is quite important for me to be able to do these kind of functions.
I do hope someone is willing to help me out here and give me their insight on the topic.
