Oraekene
New member
- Joined
- Oct 18, 2022
- Messages
- 2
- Reaction score
- 0
- Points
- 1
- Excel Version(s)
- Excel 2013
Update: Thank you for the correction. Thing is i'm new to VBA and have no idea even how to begin to write the code required. I will try to explain my desired goal in the details below. This is for a personal project and i just got introduced to excel to try to pull it off so i have no idea how to start and put it all together
On the Analysis2 sheet, Each 'contractes' column is a list of of product IDs. There are 25 product IDs in total. The numbered row beside each list of product IDs are the ratings for the top 20 products from the set of products by the customers in each survey. There were 20 sets of product ID and rank columns for 20 surveys in all
What i would like is a VBA scrip that would rank products by their average quality rating
Data sheet (starting point):
In the sample sheet i've gone through the process manually to show the expected result at each stage, but if you notice there are no formulas or macro attached to the workbook. What i would like is a vba script that achieves the same process. I believe it needs to be a vba script because i believe it would have to loop through each column of data performing a set of actions, before moving on to the next column
The analysis2 sheet is generated from filtering and then merging the survey results from their individual sheets into a single sheet. There will be new product surveys performed so the range in analysis2 sheet is dynamic and would expand over time so the vba script would need to work on a dynamic range
Here are the steps:
I would like to first generate both a row of the unique list of the product IDs in sheet3 and a column of the unique list, in report sheet, both from the analysis2 sheet
Then i'd like that For each product ID in sheet 3, it would index its value in analysis2 sheet, find its rank in the adjacent column, list it in a column under its ID in sheet 3. and repeat that through the range until it has listed all its rank numbers in a column.
Then go to the next product ID in sheet3, and repeat the same process till it has listed out all the ranks for all the product IDs
Once it has done this, it would then sort each column in sheet3 from least to most
After sorting, it would then go through a product ID column in sheet3 and find the trimmean of the rank numbers. Using for example a 10% trimming, this would mean removing the top 10% and bottom 10% of values, in this case the top 2 and bottom 2 rank numbers, and then finding the mean of the remaining 16 rank numbers. This is done to remove some effect of outliers.
It would then go to the product ID in report sheet and paste the trimmean in the adjacent column. It would then repeat this for the next product ID column until it completes all the product IDs.
Finally it would then sort the column of product IDs in report sheet by the trimmean values in the adjacent column, from least to most. This would give the product IDs that are on average ranked highest to Lowest in all the surveys by customers. It would then generate a column of row numbers in an adjacent column to show their ranks
Again, my apologies for taking this long to respond. I am totally stumped on how to proceed on this and would appreciate all help
I am still new to forums and haven't fully understood the rules, including the one on cross posting. Below are all the other forums this question has been posted in:
1. https://www.excelforum.com/excel-pr...of-all-the-values-of-an-item.html#post5742313
2. https://forums.excelguru.ca/threads/find-trimmed-mean-of-all-the-values-of-an-item.11623/
3. https://techcommunity.microsoft.com...mean-of-all-the-values-of-an-item/m-p/3655341
4. https://www.mrexcel.com/board/threads/find-trimmed-mean-of-all-the-values-of-an-item.1219566/
5. https://www.reddit.com/r/excel/comments/y6spw1/find_trimmed_mean_of_all_the_values_of_an item
6. https://stackoverflow.com/questions/74104762/find-trimmed-mean-of-all-the-values-of-an-item
On the Analysis2 sheet, Each 'contractes' column is a list of of product IDs. There are 25 product IDs in total. The numbered row beside each list of product IDs are the ratings for the top 20 products from the set of products by the customers in each survey. There were 20 sets of product ID and rank columns for 20 surveys in all
What i would like is a VBA scrip that would rank products by their average quality rating
Data sheet (starting point):
In the sample sheet i've gone through the process manually to show the expected result at each stage, but if you notice there are no formulas or macro attached to the workbook. What i would like is a vba script that achieves the same process. I believe it needs to be a vba script because i believe it would have to loop through each column of data performing a set of actions, before moving on to the next column
The analysis2 sheet is generated from filtering and then merging the survey results from their individual sheets into a single sheet. There will be new product surveys performed so the range in analysis2 sheet is dynamic and would expand over time so the vba script would need to work on a dynamic range
Here are the steps:
I would like to first generate both a row of the unique list of the product IDs in sheet3 and a column of the unique list, in report sheet, both from the analysis2 sheet
Then i'd like that For each product ID in sheet 3, it would index its value in analysis2 sheet, find its rank in the adjacent column, list it in a column under its ID in sheet 3. and repeat that through the range until it has listed all its rank numbers in a column.
Then go to the next product ID in sheet3, and repeat the same process till it has listed out all the ranks for all the product IDs
Once it has done this, it would then sort each column in sheet3 from least to most
After sorting, it would then go through a product ID column in sheet3 and find the trimmean of the rank numbers. Using for example a 10% trimming, this would mean removing the top 10% and bottom 10% of values, in this case the top 2 and bottom 2 rank numbers, and then finding the mean of the remaining 16 rank numbers. This is done to remove some effect of outliers.
It would then go to the product ID in report sheet and paste the trimmean in the adjacent column. It would then repeat this for the next product ID column until it completes all the product IDs.
Finally it would then sort the column of product IDs in report sheet by the trimmean values in the adjacent column, from least to most. This would give the product IDs that are on average ranked highest to Lowest in all the surveys by customers. It would then generate a column of row numbers in an adjacent column to show their ranks
Again, my apologies for taking this long to respond. I am totally stumped on how to proceed on this and would appreciate all help
I am still new to forums and haven't fully understood the rules, including the one on cross posting. Below are all the other forums this question has been posted in:
1. https://www.excelforum.com/excel-pr...of-all-the-values-of-an-item.html#post5742313
2. https://forums.excelguru.ca/threads/find-trimmed-mean-of-all-the-values-of-an-item.11623/
3. https://techcommunity.microsoft.com...mean-of-all-the-values-of-an-item/m-p/3655341
4. https://www.mrexcel.com/board/threads/find-trimmed-mean-of-all-the-values-of-an-item.1219566/
5. https://www.reddit.com/r/excel/comments/y6spw1/find_trimmed_mean_of_all_the_values_of_an item
6. https://stackoverflow.com/questions/74104762/find-trimmed-mean-of-all-the-values-of-an-item
Last edited:
