Hi All
I have a list of part no's with various prices and I need a formula that can do the following
Part No Lowest Price Next Price Next Price Next Price ........ until Max price.
PHAE150007 5 5.79 6.72 8.85
PHAE150012 5.57 6.63 7.95 8.00 9.00
Any one got any clues
Pivot table too big.
Kindest regards
Why do you say a Pivot Table is too big?
Would you also update your profile please so that we may know your location and the Excel version(s) you have.
Knowing these is often important when considering solutions.
Sight of your workbook would also be useful along with a manually created example of what you expect to see.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I have updated my Profile. My Apologies. Please see attached example.
Just a guess!
In M6 copied down:
=MAXIFS($C6:$L6,$C$5:$L$5,"Charge")
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.
The List in Columns B come from A Data table with a long list of 3 columns. Part No Part Description and Charges. Instead of creating a pivot table with the rows
Part No Description
Columns Charges
I did not want any blank Cells so I though maybe use the Pivot table rows to create the list of Part No's and Descriptions then use a formula to list the Values of the Charges by Part No and the No of these charges.
Kindest regards
Is this what you want?
Excel 2016 (Windows) 32 bit
Part Desc Anti Vandal 2 + 1 Toilet 12' Anti Vandal 3 + 1 Toilet 16' Anti Vandal Canteen + Drying Room 20' 24' x 10' Canteen - C/W Sink Unit, 4 Mess Tables, 20 Poly Chairs, Microwave and Fridge Anti Vandal Drying Room 20'
Sheet: Sheet1
PowerQuery M Code for the solution above:
Please Login or Register to view this content.
Sort of but like Book 2 attached
Wow never used power pivots like this I will load a table into this and try. Many thanks. IT's going to take a while
Can be done, but do you need to be shown how or have you worked it out?Originally Posted by TuboDieselOne
I Could not load the query. I attach the sheet with the table Table3.
Kindest regards
But that isn't your source data - it's what you want to achieve. Can you attach the source data instead?
This is the data source a list of Part No's and Descriptions and Rates.
Which I have to break down into a list of showing the
part no and description on the Y axis with the Rate and no of items charged at said rate.
So are you aiming for a 3-column list? I am not clear which is the before and which is the after.
The last workbook you attached was a matrix, not a list.
I have sorted this out using the following:
Part No;s Only
IF(MINIFS(Table3[Charge],Table3[Part No],$A2,Table3[Charge],">"&0)=0,"",MINIFS(Table3[Charge],Table3[Part No],$A2,Table3[Charge],">"&0))
Part NO's and Descriptions
=IF(MINIFS(Table3[Charge],Table3[Part No],$A2,Table3[Part Desc],$B2,Table3[Charge],">"&0)=0,"",MINIFS(Table3[Charge],Table3[Part No],$A2,Table3[Part Desc],$B2,Table3[Charge],">"&0))
Thanks for your help I was able to get the next value up by
Incrementing using the > than previous value.
=IF(MINIFS(Table3[Charge],Table3[Part No],$A2,Table3[Charge],">"&B2)=0,"",MINIFS(Table3[Charge],Table3[Part No],$A2,Table3[Charge],">"&B2))
Many thanks
I still have no idea what you were really trying to do.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks