i want to show the lage amount in "Summary" worksheet as per the sequence / Ranking in B column, but in rank 10,11,12,13,14,15,16,17,21,22,23,24 getting repeated? I cant understand the resolution for that, the amount date is in "Stock Sold Code" worksheet in the same file. the formula I'm using is =SUM(LARGE('Stock Sold Code'!$U:$U,B4)) its works well but it affecting the actual ranking by repeating some values and affects the accuracy.
i have attached the excel sheet for your reference
Last edited by MAJID1479; 07-31-2021 at 03:50 AM.
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
thank you alansidman for your response I'm so touched to be here on this fantastic platform, loving it from now. thank you aliGW for your valid input as well, yes I'm new here, and ill do my best to make my statement clear as much I can, ill be attaching the excel sheet for better results.
highly appreciated all your responses.
Chances are there are indeed 3 different SKUs with the same 2,793 values. The problem is with the formulas in SKU column.
Attach a sample workbook as suggested by Alan, so other members may offer alternative solutions.
Josephtech I have attached the workbook as per your advice , I hope I can get the resolution for that
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. As you are new here i have done it for you today.)
As requested above, attach a sample workbook. There are instructions at the top of the page telling you how to do this. Images are of no use to us.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
highly appreciated if someone will be able to help me here
May I propose a simpler solution - using PivotTable instead of formulas.
First, I have formatted as tables the data in columns A to E in both the Stock Balance Code and Stock Sold Code sheets. This is to ensure any new data added will automatically be picked up in PivotTables.
Then insert PivotTable based on either table, remember to tick the "Add this data to the Data Model". Insert Slicer for Category.
Remember to hit [Refresh] (right-click inside the PivotTable > Refresh) when new data are added.
Note: I have cleaned up your workbook as the file size exceeded the forum's limit of 1MB.
Josephtech I'm so glad and happy to hear from you and thank you so much for your effort I really appreciated it brother.
I have gone through the attachment and PivotTable is very ideal, but I was not targeting that I'm really looking for a formula that will help to get the target result that I'm looking for, the only problem I'm facing now is the Large number in sequence ranking is getting repeated sometime! what can be the solution for that, I want the Large amount in the ranking sequence to be unique and not to get repeated like what happening in the attached workbook that was shared earlier.
I hope their will be formula for that
hello team, I hope someone can help me with the correct formula to do the desired target as mentioned earlier.
one option, given XL2010
Formula:
C4: =INDEX('Stock Sold Code'!$R:$R,AGGREGATE(15,6,ROW('Stock Sold Code'!$U$1:$U$1000)/('Stock Sold Code'!$U$1:$U$1000=$F4),COUNTIF($F$4:$F4,$F4)))
confirmed with Enter (not an Array)
copied down
WOW Xlent Bro thank you so much its works perfectly, I really appreciate your effort man and really this is a wonderful platform I'm so happy to find the right formula this is just amazing. you make my day bro. thank you again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks