Log In
Register
Home
New Member
August 21, 2020
Offline
Hi Im Masashi
Im using Excel 2013 on my PC... trying to analyze some sales data from the past year... Since I'm getting new customers every month I need a list of them and sorted by largest customer to smallest customer...
{=IFERROR(INDEX(SORT, MATCH(LARGE(IF(COUNTIF($B$20:B20, SORT)=0, COUNTIF(_2019, "<"&_2019), ""), 1), COUNTIF(_2019, "<"&_2019), 0)),"")}
I used this formula but it doesn't finish the sorting... You will find the Problem I am talking about in Sheet ( By Sales Volume ) column B....
Note ** there are hidden sheets...
Regards.
Masashi Baba
See Answer
Trusted Members
December 20, 2019
Offline
To my knowledge the sort function doesnt exist in Excel 2013
https://support.microsoft.com/.....0e8e44b86c
Which might explain when its hanging when trying to open
Answers Post
July 16, 2010
Offline
Hi Masashi,
I would use PivotTables for this task. You can use the built in sorting capabilities and the conditional formatting etc. Formulas are error prone, especially on a scale like you're trying to use them in. Your file also has major performance issues. My PC has 4.33GHz CPU and 16GB RAM and it took forever to calculate your file!
Mynda
New Member
August 21, 2020
Offline
U see... since big files cannot be uploaded here I deleted lots of data from my ledger... On my PC it takes like 15~20 minutes to calculate the sorting formula's...
May I ask... can Pivot Tables change with the data? Bcuz I would like to sort all names by the biggest customer... however customers are always changing... our biggest customer may not be the biggest the following year... which is why I chose the formula to sort it for me instead... ( Also we are always getting new customers and to always be finding the code for them and inputting them manually into my chart is a hassle...
So what I'm trying to say here is can the Pivot Table provide me with what I need... can it give me the most efficient solution?
**Pls note that I'm analyzing data for a different department in our company which makes it a little more inconvenient to get the data I need, if you know what I meen.**
Thanks
Masashi
July 16, 2010
Offline
Hi Masashi,
Yes, PivotTables automatically re-sort upon changes in Slicer selections (filters) or new data being added to the source. More on sorting in PivotTables here.
Mynda
1 Guest(s)
