Google Sheets - Conditional Formatting (IF value in col A is repeated AND is not the MAX (evaluating only the rows with repeats) of col B
up vote
0
down vote
favorite
I have a spreadsheet with many columns paired together, A string and a value. I have been able to conditionally highlight repeated strings, which is half of what I am trying to do.
The other half is trying to highlight the value column of repeated strings if they are not the MAX value of the respective repeats. I have tried many things and have gotten close, but currently am not able to get the data in the value columns to consider all value columns.
I will link to a demo sheet using RPG style class/rank relationships as an example. Turning off the green conditional highlighting (which is wrong) will reveal how it should be working (non-conditional formatting).
Thanks in advance for any help.
https://docs.google.com/spreadsheets/d/1LXGoctsHomZktwRvJM8FQGKR4CXi8_TyeTnbnLsn_x4/edit?usp=sharing
P.S. there is a lot of junk on the bottom: Various things I was trying.
google-sheets spreadsheet conditional-formatting
edited Nov 10 at 20:26
Tim Williams
84.8k96684
asked Nov 10 at 19:36
Ognar
11
add a comment |
up vote
0
down vote
favorite
I have a spreadsheet with many columns paired together, A string and a value. I have been able to conditionally highlight repeated strings, which is half of what I am trying to do.
The other half is trying to highlight the value column of repeated strings if they are not the MAX value of the respective repeats. I have tried many things and have gotten close, but currently am not able to get the data in the value columns to consider all value columns.
I will link to a demo sheet using RPG style class/rank relationships as an example. Turning off the green conditional highlighting (which is wrong) will reveal how it should be working (non-conditional formatting).
Thanks in advance for any help.
https://docs.google.com/spreadsheets/d/1LXGoctsHomZktwRvJM8FQGKR4CXi8_TyeTnbnLsn_x4/edit?usp=sharing
P.S. there is a lot of junk on the bottom: Various things I was trying.
google-sheets spreadsheet conditional-formatting
edited Nov 10 at 20:26
Tim Williams
84.8k96684
asked Nov 10 at 19:36
Ognar
11
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a spreadsheet with many columns paired together, A string and a value. I have been able to conditionally highlight repeated strings, which is half of what I am trying to do.
The other half is trying to highlight the value column of repeated strings if they are not the MAX value of the respective repeats. I have tried many things and have gotten close, but currently am not able to get the data in the value columns to consider all value columns.
I will link to a demo sheet using RPG style class/rank relationships as an example. Turning off the green conditional highlighting (which is wrong) will reveal how it should be working (non-conditional formatting).
Thanks in advance for any help.
https://docs.google.com/spreadsheets/d/1LXGoctsHomZktwRvJM8FQGKR4CXi8_TyeTnbnLsn_x4/edit?usp=sharing
P.S. there is a lot of junk on the bottom: Various things I was trying.
google-sheets spreadsheet conditional-formatting
edited Nov 10 at 20:26
Tim Williams
84.8k96684
asked Nov 10 at 19:36
Ognar
11
I have a spreadsheet with many columns paired together, A string and a value. I have been able to conditionally highlight repeated strings, which is half of what I am trying to do.
The other half is trying to highlight the value column of repeated strings if they are not the MAX value of the respective repeats. I have tried many things and have gotten close, but currently am not able to get the data in the value columns to consider all value columns.
I will link to a demo sheet using RPG style class/rank relationships as an example. Turning off the green conditional highlighting (which is wrong) will reveal how it should be working (non-conditional formatting).
Thanks in advance for any help.
https://docs.google.com/spreadsheets/d/1LXGoctsHomZktwRvJM8FQGKR4CXi8_TyeTnbnLsn_x4/edit?usp=sharing
P.S. there is a lot of junk on the bottom: Various things I was trying.
google-sheets spreadsheet conditional-formatting
google-sheets spreadsheet conditional-formatting
edited Nov 10 at 20:26
Tim Williams
84.8k96684
asked Nov 10 at 19:36
Ognar
11
edited Nov 10 at 20:26
Tim Williams
84.8k96684
asked Nov 10 at 19:36
Ognar
11
edited Nov 10 at 20:26
Tim Williams
84.8k96684
edited Nov 10 at 20:26
Tim Williams
84.8k96684
edited Nov 10 at 20:26
Tim Williams
84.8k96684
84.8k96684
asked Nov 10 at 19:36
Ognar
11
asked Nov 10 at 19:36
Ognar
11
asked Nov 10 at 19:36
Ognar
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
For Group 1 for example you can use something like the following as your conditional formatting rule:
=and(countif($A$3:$A$8,A3)>1,B3<> MAXIFS($B$3:$B$8,$A$3:$A$8,A3))
answered Nov 10 at 19:46
QHarr
28.5k81839
That gets me part of the way there, but I need it to consider both groups together. There will actually be 5 groups when completed. Is this possible or will I need to look into attaching a script? - thanks again.
– Ognar
Nov 11 at 23:24
What do you mean?
– QHarr
Nov 12 at 20:08
If there is only one Monk in group 1 and one Monk in group 2, both strings (monk) should highlight and the lower corresponding value (rank) should highlight. In other words, highlighting should occur across groups.
– Ognar
Nov 13 at 23:04
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53242704%2fgoogle-sheets-conditional-formatting-if-value-in-col-a-is-repeated-and-is-not%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
For Group 1 for example you can use something like the following as your conditional formatting rule:
=and(countif($A$3:$A$8,A3)>1,B3<> MAXIFS($B$3:$B$8,$A$3:$A$8,A3))
answered Nov 10 at 19:46
QHarr
28.5k81839
That gets me part of the way there, but I need it to consider both groups together. There will actually be 5 groups when completed. Is this possible or will I need to look into attaching a script? - thanks again.
– Ognar
Nov 11 at 23:24
What do you mean?
– QHarr
Nov 12 at 20:08
If there is only one Monk in group 1 and one Monk in group 2, both strings (monk) should highlight and the lower corresponding value (rank) should highlight. In other words, highlighting should occur across groups.
– Ognar
Nov 13 at 23:04
add a comment |
up vote
0
down vote
For Group 1 for example you can use something like the following as your conditional formatting rule:
=and(countif($A$3:$A$8,A3)>1,B3<> MAXIFS($B$3:$B$8,$A$3:$A$8,A3))
answered Nov 10 at 19:46
QHarr
28.5k81839
That gets me part of the way there, but I need it to consider both groups together. There will actually be 5 groups when completed. Is this possible or will I need to look into attaching a script? - thanks again.
– Ognar
Nov 11 at 23:24
What do you mean?
– QHarr
Nov 12 at 20:08
If there is only one Monk in group 1 and one Monk in group 2, both strings (monk) should highlight and the lower corresponding value (rank) should highlight. In other words, highlighting should occur across groups.
– Ognar
Nov 13 at 23:04
add a comment |
up vote
0
down vote
up vote
0
down vote
For Group 1 for example you can use something like the following as your conditional formatting rule:
=and(countif($A$3:$A$8,A3)>1,B3<> MAXIFS($B$3:$B$8,$A$3:$A$8,A3))
answered Nov 10 at 19:46
QHarr
28.5k81839
For Group 1 for example you can use something like the following as your conditional formatting rule:
=and(countif($A$3:$A$8,A3)>1,B3<> MAXIFS($B$3:$B$8,$A$3:$A$8,A3))
answered Nov 10 at 19:46
QHarr
28.5k81839
answered Nov 10 at 19:46
QHarr
28.5k81839
answered Nov 10 at 19:46
QHarr
28.5k81839
answered Nov 10 at 19:46
QHarr
28.5k81839
28.5k81839
That gets me part of the way there, but I need it to consider both groups together. There will actually be 5 groups when completed. Is this possible or will I need to look into attaching a script? - thanks again.
– Ognar
Nov 11 at 23:24
What do you mean?
– QHarr
Nov 12 at 20:08
If there is only one Monk in group 1 and one Monk in group 2, both strings (monk) should highlight and the lower corresponding value (rank) should highlight. In other words, highlighting should occur across groups.
– Ognar
Nov 13 at 23:04
add a comment |
That gets me part of the way there, but I need it to consider both groups together. There will actually be 5 groups when completed. Is this possible or will I need to look into attaching a script? - thanks again.
– Ognar
Nov 11 at 23:24
What do you mean?
– QHarr
Nov 12 at 20:08
If there is only one Monk in group 1 and one Monk in group 2, both strings (monk) should highlight and the lower corresponding value (rank) should highlight. In other words, highlighting should occur across groups.
– Ognar
Nov 13 at 23:04
That gets me part of the way there, but I need it to consider both groups together. There will actually be 5 groups when completed. Is this possible or will I need to look into attaching a script? - thanks again.
– Ognar
Nov 11 at 23:24
That gets me part of the way there, but I need it to consider both groups together. There will actually be 5 groups when completed. Is this possible or will I need to look into attaching a script? - thanks again.
– Ognar
Nov 11 at 23:24
What do you mean?
– QHarr
Nov 12 at 20:08
What do you mean?
– QHarr
Nov 12 at 20:08
If there is only one Monk in group 1 and one Monk in group 2, both strings (monk) should highlight and the lower corresponding value (rank) should highlight. In other words, highlighting should occur across groups.
– Ognar
Nov 13 at 23:04
If there is only one Monk in group 1 and one Monk in group 2, both strings (monk) should highlight and the lower corresponding value (rank) should highlight. In other words, highlighting should occur across groups.
– Ognar
Nov 13 at 23:04
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53242704%2fgoogle-sheets-conditional-formatting-if-value-in-col-a-is-repeated-and-is-not%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
