Data Sorting - Quickly?

Evening

We run a computer system which tracks all sales and on a monthly basis the data is removed from there into a CSV file which I then open in excel to sort into a manor that we want.
On a monthly basis there is a vast amount of information pooled from column A to AK and Row 2 to however many sales (last month was down to row 7101)

To enable me to report the sales in a manor that we want I end up having to Select column T (Product Name) and then Filtering out certain products e.g. consults/time/vaccines/surgery/lab etc and replacing the wording in column AH (Product Type) with the relevant coding
Obviously at the moment I do this manualy for 40 different products

i.e. select column T (product name), select filter, filter out a specific product name (e.g. Consultation) then find and replace (find - service, replace - consult) and then start on the next product on the list
Once this has been done pivot tables are made to show the sales and number of units

Unfortunately I can't just make the pivot table from the product name as a number of them need to come under the same heading

This is done on a monthly basis and is starting to get somewhat time consuming and boring!

Is anyone aware of another way that it can be done quickly?
I was wondering would a macro be the way forward? Should it be something that can be set up so that it records my steps and then can just be run to do it for me?
If so how do you go about this?

Thoughts greatly appreciated!

Hi, welcome to the forum

Hard to say for sure without seeing some samples of what you are working with, and what you want. However, you could probably use something like INDEX/SMALL for this.

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

It looks like the values in sheet 2 column P are more dependent on column D than column O, where all of the cells say Services. If I am correct and if you want a formula to automatically fill in column P the following is a solution. I first used "Advanced Filter" to make a unique table list of column D as placed in columns U - Y (the placement is not important). I then put this formula in Sheet2!P2:P116:

Please Login or Register  to view this content.

Here is your file with the formula applied in Sheet2:
Copy of ex1 (Index Sumproduct).xlsx
One issue I found was that "Remove Stitches" which is usually classified as product type "Consult", was classified as "Surgery" in row 98. The table is set up to classify it as "Consult".
Let me know if you have any questions.

Lets see if this array formula gets us any closer:

Please Login or Register  to view this content.

Activate with Ctrl+Shift+Enter after pasting in P2, then double click the fill handle to pass the formula to the rest of the column. It will try to match the Product name in column B with one of the reporting categories on sheet 2. If it is unable to find a match it will show the consultation type from column A instead. If it finds a match then it will show the reporting category from sheet 2.
Let me know if you have any questions.

You're Welcome and thank you for the feedback. If you haven't already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Browse through the forums and you'll pick up understanding, also running the evaluate formula on formulas that you'd like to understand is a good way to learn. Hope that you have a nice day.