hairball101
New Member
- Joined
- May 23, 2016
- Messages
- 14
Howdy,
I'm attempting to use conditional formatting to highlight every other group of similar rows. I've been using the following formula in conditional formatting and it's been working fine, but I'm trying to adapt it to look at two other columns as well:
=MOD(SUMPRODUCT(--($I$1:$I1<>$I$2:$I2)),2)
This is just looking at the product name column, but I'd like to have it branch out and look at the Age and Gender column. Sometimes a product has the same name but is divided between genders (Male, Female, Both) or ages (Adult, Child, Infant). While they may share a product name, they are interpreted as different products by our system when the age or gender is different. As such, I'd like the spreadsheet to reflect that difference. Product names are all over the place, so using the above works well since each is a unique occurrence in the spreadsheet. This also works well for my needs since it doesn't create an additional column in the already extensive worksheet.
I've been playing with it for a few days now to get it to start looking at columns S:S and T:T (age and gender). I've been able to tweak it to so it generates a unique number for each new set of rows with the following:
=SUMPRODUCT(--($I$1:$I1<>$I$2:$I2))+SUMPRODUCT(--($S$1:$S1<>$S$2:$S2))+SUMPRODUCT(--($T$1:$T1<>$T$2:$T2))
I'd like to wrap that in a mod(XXXX,2) formula to make it alternate 1s and 0s, but the wall that I've faced is that the total sum of all the SUMPRODUCTs isn't always alternating odd and even, so it'll repeat 1 and 0 when it shouldn't.
I have a formula that works as an additional column to the worksheet, but I'd like one that is self-contained within the conditional formatting formula.
Is there any way to potentially convert the unique numbers into alternating 1s and 0s without tying it to a physical column in the worksheet?
Much thanks for any input
I'm attempting to use conditional formatting to highlight every other group of similar rows. I've been using the following formula in conditional formatting and it's been working fine, but I'm trying to adapt it to look at two other columns as well:
=MOD(SUMPRODUCT(--($I$1:$I1<>$I$2:$I2)),2)
This is just looking at the product name column, but I'd like to have it branch out and look at the Age and Gender column. Sometimes a product has the same name but is divided between genders (Male, Female, Both) or ages (Adult, Child, Infant). While they may share a product name, they are interpreted as different products by our system when the age or gender is different. As such, I'd like the spreadsheet to reflect that difference. Product names are all over the place, so using the above works well since each is a unique occurrence in the spreadsheet. This also works well for my needs since it doesn't create an additional column in the already extensive worksheet.
I've been playing with it for a few days now to get it to start looking at columns S:S and T:T (age and gender). I've been able to tweak it to so it generates a unique number for each new set of rows with the following:
=SUMPRODUCT(--($I$1:$I1<>$I$2:$I2))+SUMPRODUCT(--($S$1:$S1<>$S$2:$S2))+SUMPRODUCT(--($T$1:$T1<>$T$2:$T2))
I'd like to wrap that in a mod(XXXX,2) formula to make it alternate 1s and 0s, but the wall that I've faced is that the total sum of all the SUMPRODUCTs isn't always alternating odd and even, so it'll repeat 1 and 0 when it shouldn't.
I have a formula that works as an additional column to the worksheet, but I'd like one that is self-contained within the conditional formatting formula.
Is there any way to potentially convert the unique numbers into alternating 1s and 0s without tying it to a physical column in the worksheet?
Much thanks for any input
