Log In
Register
Home
Power Query
July 11, 2016
Offline
Hi,
Is it possible to format rows in a table so that they group rows that have the same value in one of the table columns.
For example in the following table I want to fill all of the rows with the same PO number the same colour and then when it changes to another PO Number use a different fill colour. This is to make it easier for the user to see rows corresponding to the same PO.
[Image Can Not Be Found]
Thanks for any help. I have attached a file with the table above in it. The formatting here was applied manually.
Bax
VIP
Trusted Members
June 25, 2016
Offline
Hi baxbax
See if this helps.
Example 1 uses a helper column and conditional formatting.
Example 2 checks if the PO is odd or even and conditional format accordingly. This is provided your PO number is in a sequence.
Good luck
Sunny Kow
Power Query
July 11, 2016
Offline
Hi Sunny,
Thanks for the response that works well.
I also found a solution myself which is similar but uses the =ISEVEN and =ISODD formula.
First of all I added a helper column to the table. I used the value 1 as the column title. I then entered the formula =IF(A2=A1,F1,F1+1) in to the column. The fact that the column title is 1 means that the formula will apply from cell F2 onwards. The formula will also be automatically copied down for new rows added:
This then gives me a column of values that change based on the PO Number in column A.
I then added a conditional format rule that looks at column F and applies a format if the value is an even number:
I then did the same for odd numbers:
Finally I set the "Applies to" range for the entire table and got the formatting required.
I have attached the finished file for reference.
Thanks
Bax
VIP
Trusted Members
June 25, 2016
Offline
You do not need a helper column to use ISODD() and ISEVEN() .
My 2nd example checks the PO number using ISODD(). Since you need 2 colours then you can just add another check for ISEVEN() to colour the cells.
Power Query
July 11, 2016
Offline
Thanks Sunny. For my purposes I will need the helper column as in the actual report I need to apply this to the PO Number does not necessarily run in sequence. All solutions work well and provide a solution to my initial question.
1 Guest(s)
