Jan 20 2021 08:26 PM
Jan 20 2021 09:35 PM - edited Jan 20 2021 09:41 PM
You may need to hit Ctrl+Shift+Enter for it work after keying it into the formula bar. For descending order, try replacing "small" with "large".
If you have office 365 and the newer functions (such as sort and sequence), then it could probably be made more efficient. This is a guess as to how you could try the sort function as I don't have that one, but someone else may be able to speak to whether or not it would work:
Jan 21 2021 12:54 PM
I am not able to do it so far, I am not sure why.
I have added more to what I am trying to get done in this file. Let me know if you can see it and if you can help, otherwise I would post the file to get some help. Thank you so much
Jan 21 2021 08:55 PM
@Marco365 Sorry, but you need to be more specific. What I do get is that, for instance the number in E1 = 351. The largest number that can be created from these digits is 531. Similar for E2 and E3. The digits in the respective numbers can all be "re-grouped" to (the largest number) 531. Now, E4 (on 18 sep 2020) has the number 131. How does this one relate to the 531 in J17, also 18 sep 2020? Similar for the next one (331 on 16 sep). And why is there a number "532" in the list of 531's, dated 17 sep 2020? (See picture). Perhaps you accidentally typed it wrong and it should also be 531 and on 14 sep 2020. But why?
Why not create an example of, say, the first 10 numbers on your list and manually produce the full output of what you need, noting down exactly, the logic for each step.
Jan 23 2021 05:37 PM
Hi Riny, so sorry for the confusion, that is what I was thinking of doing creating a brand new spreadsheet showing what I need to get done manually. I will work on it. Should I do a different post or use this one again? I am not too familiar with this platform, I don't want to have things all over the place.
Jan 23 2021 09:38 PM
@Marco365 Always best to start one thread regarding one problem and not mix in other questions on the go. I noticed that you uploaded the same file and question at the end of the thread in the link below.
Best for now is that you take some of your dates and numbers and carefully produce the output you need, explaining the logic of every step needed to get there. What you have given so far is not clear, at least not to me.
Jan 23 2021 10:15 PM
I am sure it is kind of confusing, I thought so myself. I have attached another spreadsheet with 3 sheets with instruction in the sheet or see the sheet title. Let me know if that is clear and is possible to achieve. Thanks
Jan 23 2021 11:59 PMSolution
@Marco365 Thanks for the file and clarification. Rather than creating a lot of manual steps, I thought it was best to create two pivot tables ("pt"). on the basis of the data sheet "1 Sort by cell....."
The first pt counts the number of numbers in each month of the year.
A second pt creates a list of all dates in the data, with a filter on the number with digits in descending order. I called it "DescNr". This is the basis for individual pt's for each of these numbers. They look like this:
Don't worry! You don't have to create every pt for each "DescNr". Under options on the Pivot Table Analysis ribbon, you can select "Show Report Filter Pages...". This will create one pt for each of the items in the filter field.
Perhaps you find this approach acceptable. Almost fully automated and not much room for error.
Jan 24 2021 08:46 AM
First of all, THANK YOU SO MUCH!, THANK YOU, THANK YOU, THANK YOU!
Now, I have some questions, did I miss the first step? I wanted to sort all the numbers by cell, so 291 and 192 can all become 921, not combining them but just sort each one of them in the column but by cell. I would prefer from highest to lowest.
Second, how can I also count how many days in the second pt, first instance, 331 has 5 for 2020 and 7 for 2019, is that clear?
last one, I know you use pt, would a video be possible at all to see the steps for this particular file? if not I'll just have to go find out how to use a pt and try it on my own. I would love to save that time and learn exactly what I need to learn right now from this file.
Thank you, thank you!
Jan 24 2021 09:12 AM
@Marco365 In sheet "1 Sort by......" click the filter button and sort Descending. Is that what you mean?
Have added a field with sub-total to count the number of occurrences of every "DescNr".
A video is not something I'm able to create just now. Google for "How to create a pivot table" and you'll get many resources/tutorials. Much better than what I can do.
Attaching a file with the changes described above.
Jan 24 2021 09:59 AM
I started looking at tutorial, I will go ahead and learn more about it because I need it. But I would like your help with just one more thing. I feel like I am missing it, but let me clarify again, in sheet "1Sort by cell..." I had to take column E and manually create column F, meaning each cell in column E I had to manually create another cell for it in column F by just typing it. Column E for instance had in each cell these numbers 531, 513, 135, 153, 315, 353 but I had to manually turn all of them in column F to 531, 531, 531, 531, 531, 531. How can I sort each cell from highest to lowest so all become 531 automatically? is that clear?
Jan 24 2021 09:30 PM
Jan 26 2021 11:05 AM
And, you should be able to replace "small" with "large" to go in descending order. Also, I'm assuming the data does not mix upper/lower case?