Hello all. I've come unstuck trying to use the correct formula to find the result I'm after. Example file attached.
I would like to return the ID and Item for the highest value per Item.
For example, the highest value of Item 'A6' = 750, so need to return ID of '4' and Item of 'A6'.
(My actual file contacts tens of thousands of records).
i doubt you are still using Excel 2007 - please update your profile.
In the sample workbook, please mock up the results you are looking for. Thanks.
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.
Revised example file now attached displaying the proposed results I'd like to retrieve.
So the ID number for each Item will ALWAYS be unique, e.g this...
ID ITEM VALUE
2 A4 350
2 A4 370
can never happen?
I'm think Pivot Table or PowerQuery so each item is listed
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
A basic SUMIFS is all you need. In H2 copied down:
=SUMIFS($C$2:$C$14,$A$2:$A$14,G2,$B$2:$B$14,F2)
Thanks. That doesn't seem to do anything, however. It merely returns the Value from every row.
In the results, only 1 result should appear per Item. So from my overall file, I may have 50 records with an Item of 'A1'. I just need to find the highest Value against 'A1' but also return all three columns i.e. the highest value of Item 'A1' = 350 and is also recorded against ID '2' so return the three columns with the data:
Item 'A1'
ID '2'
Value '350'
Only one Item will ever appear in the results
More than one of the same ID can be aligned to different Items
thanks,
Just wondering if anyone else has been able to look at this conundrum?
I'm not sure that this is what you need, but maybe I guessed right.
F2:
=INDEX($B$2:$B$30,MATCH(1,INDEX((COUNTIF($F$1:F1,$B$2:$B$30)=0)*($B$2:$B$30>0),),))
H2:
=AGGREGATE(14,6,$C$2:$C$32/(MATCH(A$2:A$32&B$2:B$32,A$2:A$32&B$2:B$32,)=ROW(A$2:A$32)-1)/(B$2:B$32=F2),1)
G2:
=INDEX($A$2:$A$30,MATCH(1,INDEX(($B$2:$B$30=F2)*(H2=$C$2:$C$30),),))
Last edited by T.I.; 08-06-2021 at 08:10 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks