Conditional Format alternative

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi,

I know that Conditional formatting doesn't carry over when using a Vlookup.

I am using a Vlookup to search data on a different sheet and return the value from a different column in a different cell.
Product cost is the returned value.
Code is the user entry.
The formula in Product cost uses vlookup to search a different sheet with a list of 3500 items for the text in the Code column. then returns the value in the Product cost column.

Some of the returned values are formatted on the basis that the original search text is duplicated.

I want to high light the returned value if the original text is duplicated.
Excel.jpg

Any thoughts?
 

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
yes, as you can see the sample data from post #8 above, only the highest cost with same code are high lighted
I don't want it to be highlighted. I want it to display the highest price.

If you look at my sample data here you'll see 2001ORGW i want it to return £8.53 in the product cost box when looking up a 2001ORGW

Is that possible?

Excel.jpg
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
one thing you can do is to set the font colour as the background like this

Book1
ABCDE
1CodeCostCodeCost
2Code11Code11
3Code12Code12
4Code13Code13
5Code14Code14
6Code25Code25
7Code26Code26
8Code17Code17
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=E2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B8Expression=MAXIFS($B$2:$B$8,$A$2:$A$8,A2)<>B2textNO

I don't want it to be highlighted. I want it to display the highest price.

If you look at my sample data here you'll see 2001ORGW i want it to return £8.53 in the product cost box when looking up a 2001ORGW

Is that possible?

View attachment 24473
 

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Thanks but Still not really there.
I'm creating it as a quoting tool. The product code they are selling is typed in, and it shows the price. But when there are duplicate codes it needs to show the highest price.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thanks but Still not really there.
I'm creating it as a quoting tool. The product code they are selling is typed in, and it shows the price. But when there are duplicate codes it needs to show the highest price.
based on the sample data in post #12 above, code 1 has 5 different costs in Col E and the highest is the only one showed on Col B, isn't that what you're after?

If not, would you post a sample date and desired result here?
 

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

2001ORGW£5.50
2001ORGW£6.51
2001ORGW£7.83
2001ORGW£6.12
2001ORGW£7.21
2001ORGW£8.53
2001W£1.93
2001W£2.12
2001W£2.41
2001W£2.20
2001W£2.42
2001W£2.75
2007W£3.30
2007W£3.65
2007W£3.99
2007W£4.40
2102W£2.69
2102W£3.84
2102W£3.45
2102W£4.60
2201W£2.87
2201W£3.57


Heres some data.

CodeSupplierProduct DescriptionColourProduct Cost
2102WBTCAmerican Apparel Womens Fine Jersey Tee
£2.69​

I want to type the code in in the code columm and when it has duplicates in the price i want it to show the highest cost in the product cost box.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
like this?

Book1
ABCDEF
12001ORGW£5.50
22001ORGW£6.51
32001ORGW£7.83
42001ORGW£6.12
52001ORGW£7.21
62001ORGW£8.53
72001W£1.93
82001W£2.12
92001W£2.41
102001W£2.20
112001W£2.42
122001W£2.75
132007W£3.30
142007W£3.65
152007W£3.99
162007W£4.40
172102W£2.69
182102W£3.84
192102W£3.45
202102W£4.60
212201W£2.87
222201W£3.57
23
24
25Heres some data.
26
27CodeSupplierProduct DescriptionColourProduct CostHighest
282102WBTCAmerican Apparel Womens Fine Jersey Tee£2.69£4.60
292001ORGW£6.12£8.53
302007W£3.30£4.40
Sheet1
Cell Formulas
RangeFormula
F28:F30F28=MAXIFS($B$1:$B$22,$A$1:$A$22,A28)
 

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Awesome thats more like it. I'll try it THanks
 

johnmpc

New Member
Joined
Oct 19, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Got so stuck on the Vlookup couldn't see this option. THanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,567
Messages
5,548,822
Members
410,875
Latest member
longstrb
Top