Forum Discussion

Nick_Depratto's avatar
Nick_Depratto
Copper Contributor
Jan 11, 2022
Solved

Finding the low and high price of a part #

Hello - I am looking for some help with a spreadsheet I am working on. I have a sheet with a tab that has part#'s that have multiple purchases. There are a lot of the same part#'s on them. 

 

I have created another tab and would like to find the highest and lower part# within the first tab for the respective part#. 

 

 

Here is an example of the tab with the raw data. 

 

DatePart#Part DescriptionRec’d QtyParts Cost
01-20-2021200717SEAL KIT1.00$64.10
09-29-2021200717SEAL KIT1.00$72.69
01-18-2021200957SEAL KIT0.00$117.52
02-03-2021200957SEAL KIT1.00$117.52
02-23-2021200957SEAL KIT1.00$117.52
01-18-2021200965BUSHING1.00$128.19
02-24-2021200965BUSHING1.00$128.19
01-03-2022201004PIN M100XY1.00$1,561.33
01-10-2022201004PIN M100XY1.00$1,561.33
01-03-2022201005BUSHING2.00$413.64
01-10-2022201005BUSHING2.00$413.64
01-29-2021202637PIN H, G CY1.00$1,709.24
01-29-2021202638BUSHING2.00$512.78

 

 

Here is an example of what I would like to pull from the raw data. 

 

Part#Part DescriptionStart PriceHighest PricePrice Difference
200717SEAL KIT   
200957SEAL KIT   
200965BUSHING   
201004PIN M100XY   
201005BUSHING   
202637PIN H, G CY   
202638BUSHING   

 

 

Any help would be appreciated, thank in advance.

excel
Formulas and Functions
training

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 11, 2022

    Nick_Depratto 

    In cell C19 in the attached example:

    =INDEX($E$2:$E$14,MATCH(A19&SMALL(IF($B$2:$B$14=A19,$A$2:$A$14),1),$B$2:$B$14&$A$2:$A$14,0),0)

    In cell D19 in the attached example:

    =LARGE(IF($B$2:$B$14=A19,$E$2:$E$14),1)

     

    Maybe with the above formulas. Enter formulas as arrayformulas with ctrl+shift+enter if you don't work with Office365 or 2021. If you work with Excel 2019 or a newer version you might apply MAXIFS and MINIFS formulas.

Resources