Hi there. I'm trying to count the data and rank it for every category on the same table. The data is from the yellow table. Is there a formula to creat a result on column H (blue table) and column M (green table). Specifically to creat the end result like the green table and auto arrange the data based on the rank.
Here I attach a screenshot and the sample file.
Appreciate your help.
Thank you.
Screenshot 2023-07-05 125337.png
Example 1.xlsx
Last edited by darylglennt; 07-05-2023 at 10:52 PM.
I want to create a formula that creates the entire tables at once.
Can the modes of transport not used by a category be omitted from the tables?
For example Car and Taxi in Category Childeren.
Hi HansDouwe
Yes, its okay. That will do just fine.
Can you help me with that?
Count:
Please remove unnessesary spaces in Column C and try in O4:Formula:
Please Login or Register to view this content.
Thank you HansDouwe
It works.
But here's another problem:
I find that the real data is separated by other data in the middle of the Category and Transport column (pict below on yellow table). I tried but the formula didn't work well for such condition.
And the data is sorted from lowest to highest. I need the data to be sorted from highest to lowest.
Can the same formula work for this?
Appreciate your effort. Thank you.
Attachment 835364Example HD.xlsx
How can you suddenly 'find' this? Surely you knew it was there? What is the nature of the data?
Please try to give us much more realistic sample data at the start in future.
Last edited by AliGW; 07-05-2023 at 03:20 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
As in your example, the count table is not sorted by a number.
In the rank table, the number is sorted descending by number.
See attached example (fitting in your sheet of post #1)
Formula rank table: Please try :Formula:Please Login or Register to view this content.
I can't access your attachment in post #5 (invalid attachment).
Please upload your attachment via Go advanced and manage attachments. Instructions, see yellow banner at the top of this page.
@Hans - click on the bit that says Example - there are two attachments.
Hai AliGW
Sorry, I forgot to include the column data between the two.
Thank you for reminding me. I will give a more appropriate sample next time.
Count table, new lay out, please tryFormula:Please Login or Register to view this content.
For the rank table, please adjust the formula in the same way.
For count table put on J4 and copied down:
=SUMPRODUCT(($B$3:$B$21=LOOKUP(2,1/NOT(ISBLANK($H$4:H4));$H$4:H4))*($E$3:$E$21=$I4))
Thank you HansDouwe
The formula works great!
Appreciate your help
Kind regards,
darylglennt
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
thanks for the feedback and rep. Glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks