Formating large quantities of data

[JulianBorisov]

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.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[JulianBorisov]

asdasdsdgdsfgsdv