Hi
I am a bit of a novice however I have had excellent assistance here before so hoping for more. I am stretching it a bit here. I have used much of what I have learned here to get me this far.
I now have a list of 85000 rows with columns A to BK. Many of these columns are entries I may need when I convert this to an access database. My current issue would be very simple in an access database but I only have an Excel sheet.
Given ………..an example just showing the columns I wish to use in a sorting exercise.
In my table there are 9 states. 3 more to come. Perhaps more later.
Showing licenses as they are the only unique entry per row in the sheet.
Owners – Some are repeated up to 3000. 500 owners have over 10 entries/Rows. Most are single entries/Rows per owner.
Description Common is a description I have set to harmonize Store descriptions across the states. (I am likely to change this later)
An owner can have more than one description Common.
A description common can have more than one owner.
I would like to sort all the states together
Then sort the owners per state
Showing the frequency of the owners per state with this in a number beside each owner entry in the state.
Show the Description Common per owner per state.
Show the number of times that owner has that Description common in that state.
I can do all this by sorting per state. Copying into a separate sheet and sorting by owner. Then creating duplicate columns for Owner and description common. Running =COUNTIF($a$2:$a$85000,$c$2:$c$85000). Sorting by highest first. I can have a separate count with the duplicates removed.
But I was wondering if this was the best approach and if there is a formula or function I can run on the main sheet which will perform the lot in one go.
I have attached the file.
Looking forward to hearing from someone. I am sure that I will think of a better way of say this as soon as I post it.
Mark
Bookmarks