SOLVED

Finding the low and high price of a part #

New Contributor

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.

2 Replies

@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.

best response confirmed by Nick_Depratto (New Contributor)
Solution
Thanks @Quadruple_Pawn. The Minifs/Maxifs worked great.