• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Supplier charts|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Supplier charts|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Supplier charts
sp_PrintTopic sp_TopicIcon
Supplier charts
Avatar
Rebekka Hansen

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 23, 2020
sp_UserOfflineSmall Offline
1
September 23, 2020 - 10:40 pm
sp_Permalink sp_Print sp_EditHistory

I want to make a chart where prices for different parts can be compared. So, I have a product that consists of many parts. For each part a price has been requested from different suppliers. This means that the different parts has a price offer from different suppliers. The goal is then to find the price offer that is cheapest and multiply that with how many of that specific part is needed and then calculate a total price for the whole machine/product based on the cheapest price from each part. So, how can this be best visualised so it doesn't leave many boxes blanc? Any suggestions?

 

Thanks!

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
September 24, 2020 - 6:12 am
sp_Permalink sp_Print

Can you post some examples so we dont have to guess or make up incorrect data?

Avatar
Rebekka Hansen

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 23, 2020
sp_UserOfflineSmall Offline
3
September 24, 2020 - 5:12 pm
sp_Permalink sp_Print

This shows an example with just a few parts and suppliers, but in real there will be up to almost 200 parts, and maybe 20-30 different suppliers. And not all parts will have an price offer from all suppliers, they make different things. So, to avoid having too many columns, is there a better way to present this, so that the last column will find the cheapest price between the relevant dealers and calculate in the last row a total price for the whole machine?

[Image Can Not Be Found]

Avatar
Rebekka Hansen

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 23, 2020
sp_UserOfflineSmall Offline
4
September 24, 2020 - 6:06 pm
sp_Permalink sp_Print

Skjermbilde-2020-09-24-kl.-09.05.12.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Skjermbilde-2020-09-24-kl.-09.05.12.png (65 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
September 24, 2020 - 6:18 pm
sp_Permalink sp_Print

Hi Rebekka,

How about using conditional formatting to highlight the lowest price for each part, the use some MIN functions to pull out the lowest price, which you've already done in the last column?

Then just SUM those values in the last column to get the cost or each product.

But without your actual workbook I can't offer anything else.  I can't recreate your data.

Regards

Phil

Avatar
Rebekka Hansen

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 23, 2020
sp_UserOfflineSmall Offline
6
September 24, 2020 - 6:53 pm
sp_Permalink sp_Print sp_EditHistory

Here is the file.

But my main question/problem is tips on how this can be presented in a good visualizing way so that one does not have to scroll far to the right to find the best price. Is there a way to avoid getting so many blank routes? Since there are many parts that do not have a price at all dealers.

This list will be used as a BOM list where one can go in to see the different prices dealers have given and then see if they should look for even cheaper prices to reduce the total cost of the machine, if it makes sense.

PS. I just used 4 suppliers and 14 different parts in this example, but there will be way more suppliers-columns. 

Thanks for help! 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
September 24, 2020 - 8:17 pm
sp_Permalink sp_Print

Hi Rebekka,

Thanks for sharing your file. You need to unpivot your data and put it into a proper tabular format. Then you can use Tables or PivotTables to extract the data and with Slicers your users can filter the data to only see the type, part number or supplier they're interested in.

In the attached file I've used Power Query to unpivot the data layout. Then on the Query sheet you'll see there are Slicers that allow you to filter the table. Alternatively, you can use a PivotTable to filter the data (see Pivot sheet), which will also enable you to sort the total cost from smallest to largest so the cheapest price always appears at the top of the list.

I hope this gives you some ideas to work with.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Aislinn Mellamphy, Lynnette Altomari, Jessica Stewart, Roy Lutke, Jeff Krueger, Tucker Oakley, Natasha Smith, Monique Roussouw
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.