compare prices in sheet AND HIGHLIGHT lowest and highesr Values then change my price

[chiller]

Hi all
I have struggled with this for days, Basically i need to compare competitors prices, and highlight top and bottom values in red and green Respectively..
Then wish to compare MyPrice(F) with Cheapest price(G).

If (F) is Higher then (G)THEN select value IN(G) and reduce the value by 1% round Down to nearest whole figure and paste in NewPrice(I).

If (F) is lower then (G) THEN copy (F) DATA and paste in NewPrice(I) AND
If in any case NewPrice(I) IS lower than Buy Price(H) then highlight cell in red.

First 2 rows as examples
[TABLE="width: 621"]
<tbody>[TR]
[TD]sku[/TD]
[TD]Comp1[/TD]
[TD]Comp2[/TD]
[TD]Comp3[/TD]
[TD]Comp4[/TD]
[TD]MyPrice[/TD]
[TD]Cheapest price[/TD]
[TD]BuyPrice[/TD]
[TD]NewPrice[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]70.81[/TD]
[TD="align: right"]74.16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78.01[/TD]
[TD="align: right"]70.81[/TD]
[TD="align: right"]32.50[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]72.49[/TD]
[TD="align: right"]72.49[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]66.54[/TD]
[TD="align: right"]72.49[/TD]
[TD="align: right"]59.00[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]52.00[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]52.00[/TD]
[TD="align: right"]20.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]33.00[/TD]
[TD="align: right"]25.00[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]25.00[/TD]
[TD="align: right"]20.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]98.00[/TD]
[TD="align: right"]78.00[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]21.00[/TD]
[TD="align: right"]36.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]25.00[/TD]
[TD="align: right"]25.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD="align: right"]85.00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]21.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]68.21[/TD]
[TD="align: right"]68.21[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]68.21[/TD]
[TD="align: right"]52.00[/TD]
[TD="align: right"]43.10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you

 

[Joe4]

You can do your highlighting with Conditional Formatting.

For example, let say that your data range for your Comp prices is B2:E9. To find the minimum for each sku (row) and highlight it, highlight the range B2:E9, then enter this Conditional Formatting formula:

=B2=MIN($B2:$E2)

then select your formatting color.

Do the same thing for the highest prices, just using MAX instead of MIN.

If in any case NewPrice(I) IS lower than Buy Price(H) then highlight cell in red.

The Conditional Formatting for comparing one cell to another is greater straight forward using Greater Than or Less Than, i.e.

=I2 < H2

If (F) is Higher then (G)THEN select value IN(G) and reduce the value by 1% round Down to nearest whole figure and paste in NewPrice(I).

If (F) is lower then (G) THEN copy (F) DATA

Place this formula in I2 and copy down:

=IF(F2 > G2,ROUND(G2*0.99,0),F2)

A few things though:
- you didn't mention what to do if column F equals column G
- based on your rules, your example does not look correct for the second row of data (where is 57 coming from)?

 

[chiller]

HI
Thank you for your swift response, the data 57 is an error and should have been on the sheet.
- you didn't mention what to do if column F equals column G? as per your formula if the values are the same it in F and G it pastes the same value. this works for me at present.

However i have another question , is their a way to keep all competitor data in their own respective sheet Comp1 (sheet2),Comp2 (sheet3),Comp3 (sheet4),
and have a lookup that matches data sheet1 coloumnA with sheet 2 coloumn A,sheet3 coloumnA AND PULL in the values of columns b out of each sheet in to sheeta.

 

[Joe4]

However i have another question , is their a way to keep all competitor data in their own respective sheet Comp1 (sheet2),Comp2 (sheet3),Comp3 (sheet4),
and have a lookup that matches data sheet1 coloumnA with sheet 2 coloumn A,sheet3 coloumnA AND PULL in the values of columns b out of each sheet in to sheeta.

I'm sure there is, through a set of more complex functions. However, what you are really describing is a Relational Database. As such, I find those sort of projects much easier to do in a Database program, like Microsoft Access (but then again, I use Access quite a bit, so am very familiar with it).

Since I don't really do these sort of things in Excel, I am afraid I cannot really give your guidance on the best way to do that in Excel.

 

[chiller]

Ok

I have another question : =IF(F4 > G4,ROUND(G4*0.99,0),F4)
can we adjust this formulae to use F4 price if no price in G4

 

[Joe4]

i have another question : =if(f4 > g4,round(g4*0.99,0),f4)
can we adjust this formulae to use f4 price if no price in g4

=if(and(f4 > g4,g4 > 0),round(g4*0.99,0),f4)

 

[chiller]

Perfect : thank you so much

 

[chiller]

Hi there i have taken on board and have started to work in access.
do you have the equivalent expression for

=B2=MIN($B2:$E2)
=IF(F2 > G2,ROUND(G2*0.99,0),F2)
thanks in advance</pre>

 

[Joe4]

Working in Access, your first step is usually to normalize your data tables, to make them easier to work with. There is a link of Relational Databases and Normalization here.

The first issue you would see is that your data table is not normalized. You would not have 4 different "Comp" fields. You would have one Comp Field ID and one Comp Field. So instead of having four comp fields for a record, you would have 4 different records, each with one Comp Field value.

That is important because the MIN function in Access works across multiple records (rows), but only a single field (column). Otherwise, you would need to write a long-winded function comparing each of the four fields with each other (maybe a UDF). Much more painful that it has to be (which is why you want to normalize your data).

The IF function in Access is similar to Excel, except it uses IIF instead of IF.

[COLOR=#574123]IIF([MyPriceField] > [CheapestPriceField],ROUND([/COLOR][COLOR=#574123][CheapestPriceField][/COLOR][COLOR=#574123]*0.99,0),[/COLOR][COLOR=#574123][MyPriceField][/COLOR][COLOR=#574123])[/COLOR]

 

[chiller]

I now have 4 tables each with competitor prices in , what code would i use to pull up data for all 4 tables and then apply the min max function