Can anyone help me, I am trying to understand some key info on a very large spreadsheet (image below).
It is all about an order review system- there are three main columns for me: A. order number. B. Initial order type and C.Final order type.
I want to know how many orders that come in as Type 1 are changed to type 2, and 3. And vice-versa: how many are changed to Type 1 from 2 or 3. (Type 1 is my main concern here).
The problem is, before now, every-time any change was made to an order, this order was reproduced onto a new row in the sheet (when changes may not have happened to the actual order type, but rather the details). Another issue is that a lot of orders come in with the type as blank in column B. This blank order is then assigned a type in column C (so does not count as having changed).
I need to know:
-How many individual orders there are in total (some orders won't change of course)
-How many individual Type 1 jobs were recorded at the beginning of the process (column B)
-How many individual Type 1 jobs were recorded at the end of the process (column C)
-How many individual jobs changed from type 1, to type 2 or 3
-As well as how many changed from Type 2/3 to type 1.
Not counting repeats or blanks.
I started doing this with filters (see pic) and a count of how many have been changed (excluding blanks), then how many began as type 1 and became a different type, and how many began as type 2/3 and ended up as type 1, but I keep being tripped up by repeats. I also tried simply removing duplicates, but then it indiscriminately removes repeats and can remove the repeated list item that shows the type change. I have tried for a long time on this and have reached my limit of understanding; are there any excel braniacs out there who can share a simple solution?
Screenshot 2020-04-30 at 02.45.00.png
Bookmarks