Jump to content


Photo

Compare 2 values in order to get one result

VLOOKUP filter pivot table

  • Please log in to reply
6 replies to this topic

#1 LT27

LT27

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 14 January 2016 - 01:02 AM

Good day!

 

Hope all is well with you! I need help trying to figure out the final quote price located in another tab when I compare the part number and winner supplier.

 

Thank you so much for all your support.

 

Love your videos.

 

Luz

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 14 January 2016 - 07:45 PM

Hi Luz,

 

I'm not 100% sure of what you're trying to do here. I thought you might be trying to extract the smallest value from the Full Bid Report sheet that corresponds with each Part Number on the Award Values sheet, but when I checked it looks like there is only one item for each Part Number on the Full Bid Report, so maybe all you are trying to do is link the two tables.

 

If this is the case, you can do so with VLOOKUP like this:

=VLOOKUP(B7,'Full Bid Report (Prices Tab)'!B6:E13,4,FALSE)

 

If you don't understand this VLOOKUP formula, you might want to review the video lesson on VLOOKUP in: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

I have attached a copy of your workbook with the VLOOKUP function applied:

 

Attached File  Practice 1.xlsx   240.69KB   119 downloads

Practice 1.png

 

If this isn't what you were trying to do, please feel free to reply and I'll try to offer further advice.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 LT27

LT27

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 15 January 2016 - 12:23 AM

Good day!

 

Hope all is well with you! Sorry I didn't explain myself in a clear way. I want to compare the part number 3038203-2 and the Winning supplier Romeo with the LOT PRICING TAB and provide me with the Best Quote Price that will be $1,955.88.

 

If part number 3038203-2 and Winning supplier Romeo is true then look for the quoted price located in the "Lot Pricing Tab" the value will be $1,955.88.

 

Hope it makes sense now.

 

Appreciate your support.



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 15 January 2016 - 08:28 PM

Hi Luz,

 

I have taken a look at the Lot Pricing tab, and things are a lot clearer to me now.

 

I can see that you have created columns named Supplier1, Supplier2, Supplier3 etc. to account for each supplier.

 

Quotes1.png

 

When you see this kind of layout, you know that you're working with 'relational' data. In a true relational database, you'd have a table of Suppliers, a table of Parts, a table of Auctions and a table of Quotes, all linked together with relationships.

 

To properly store this kind of data, you need to restructure your spreadsheet to add a Supplier column instead of having separate columns for each supplier.

You can see an in-depth explanation of relational data and why this is needed in the video lesson: Expert Skills Lesson 6-1 Understand primary and foreign keys.

 

I have re-structured the worksheet to move the supplier names into a Supplier column:

 

Quotes2.png

 

Now that the data is restructured, the next challenge lies in working out which bid is the 'winning' bid for each part number. You can now achieve this quite easily by using a Pivot Table, like this:

 

Quotes3.png

 

For instructions on how to create a pivot table like this one, see the video tutorial: Expert Skills Lesson 5-1 Create a pivot table.

 

This may be the solution in itself, but I've noticed that on your other workbooks you want to see the names of the suppliers for the winning bids and other information. Extracting this information as well is a greater challenge, as Pivot Tables are only intended to display summaries rather than complete sets of data.

 

The only way to achieve this will be to find a way to determine the winning bid for each product within the Lot Pricing table itself, and this is only possible with a very complex formula:

=AGGREGATE(15,6,1/([Part Number]=[@[Part Number]])*[Bid],1)

 

Please feel free to reply if you'd like an explanation of why this formula works, but for the moment I'll just say that it will work out the minimum bid for each part number.

 

Armed with that information, you can quickly determine which of the bids is the winner by checking whether it is the same as the minimum bid.

 

This can be done as simply as:

=[@Bid]=[@[Minimum Bid]]

 

The items in square brackets are 'structured references'. If you're not familiar with these, you can see an explanation in: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

 

Finally, you need to create a key value that will allow you to use VLOOKUP to search for the winning bid for each product code. To do this, I'm going to add a column called 'Key' that concatenates the Part Number and the result of the 'winner' formula above:

=[@[Part Number]]&[@Winner]

 

For more on concatenating with the & operator, see the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

Here's how the Lot Pricing table looks after restructuring:

 

Quotes4.png

 

Now that you have the Key that you need, you can use VLOOKUP on the Award Values sheet to extract all of the values for each winning bid:

=VLOOKUP(B7&TRUE,Table1,4,FALSE)

 

Notice how I've concatenated TRUE onto the product code to find the winning bid instead of the losing bids.

 

For a more in-depth explanation of VLOOKUP, see the video lesson: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

Of course, you might prefer to simply filter the Lot Pricing table to only show the winning bids. You can see how to do this in: Expert Skills Lesson 1-3 Apply a simple filter to a range.

 

I have attached a copy of your workbook with all of the changes applied:

 

Attached File  Practice 2.xlsx   66.55KB   177 downloads

Practice2.png

 

I hope this is useful and you're able to get the results you need, but please feel free to reply if I can offer any more help.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 netgazer

netgazer

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 28 January 2016 - 05:11 PM

Hi,

 

without re-structuring the original file, could Luz' objective be accomplished with the following lookup formula on the Award Values tab?

 

=VLOOKUP(B7,'Lot Pricing'!E11:M19,8,FALSE)

=VLOOKUP(B8,'Lot Pricing'!E11:M19,9,FALSE)

=VLOOKUP(B9,'Lot Pricing'!E11:M19,9,FALSE)

=VLOOKUP(B10,'Lot Pricing'!E11:M19,9,FALSE)

=VLOOKUP(B11,'Lot Pricing'!E11:M19,8,FALSE)

=VLOOKUP(B12,'Lot Pricing'!E11:M19,9,FALSE)

=VLOOKUP(B13,'Lot Pricing'!E11:M19,9,FALSE)

=VLOOKUP(B14,'Lot Pricing'!E11:M19,8,FALSE)

 

See attached file.

 

Just practicing my understanding.

 

Cheers,

 

Attached Files



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 28 January 2016 - 07:41 PM

Hi netgazer,

 

You're correct that these VLOOKUP functions will return the correct values within the original framework.

 

The newer framework I've suggested should make it much easier to add new suppliers and reduce the need to manually adjust formulas.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#7 netgazer

netgazer

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 28 January 2016 - 10:15 PM

Great, thank you.  I also follow your recommendation to re-structure the original spreadsheet like that of a database, its just that I thought it might be too advance for us to follow, but still, thank you for sharing that approach.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users