Spreadsheet: 92466d1292098087-conditional-formatting-condionalformatting_based-on-journal-entry-numbernew
Sheet: Sheet1

condtion 1 | When they are mutilpe User ids at the Entry levelwithin the same Journal Entry Number thenthe parent as well as childs(line level User is and Grline leve User ids)should be in black color.
condtion 2 | When there is only One User id at The Entry level within the same Journal Entry Number then the childs also will have same user id compulsory then all the childs should be in white color and only parent should be in black color
 |  |  | Input:
 |  |  | Grouping Level | Type of Entry | No. of Lines | Journal Entry Number | Account Number / Journal Entry Description | Journal Line Description | Account Name |  |  | Debit | Credit | User ID
 |  |  | Entry | NST | 2 | JE1146 | THE WORST JE EVER!! I BET THERE IS FRAUD IN HERE!! |  |  |  |  | 1000000 | 1000000 | User 5
 |  |  | Line | NST | 1 | JE1146 | A0801 |  | Unexpected Income |  |  | 0 | 1000000 | User 5
 |  |  | Entry | NST | 2 | JE1171 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 400000 | 400000 | User 13,User 5
 |  |  | Line | NST | 1 | JE1171 | A0998 |  | Test Expense 1 |  |  | 400000 | 0 | User 13
 |  |  | Line | NST | 1 | JE1171 | A0999 |  | MISC Expense |  |  | 0 | 400000 | User 5
 |  |  | Entry | NST | 2 | JE1172 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 333187 | 333187 | User 15,User 5,User 3
 |  |  | Line | NST | 1 | JE1172 | A0998 |  | Test Expense 1 |  |  | 333187 | 0 | User 5
 |  |  | GrLine | NST | 1 | JE1172 | A0999 |  | MISC Expense |  |  | 0 | 333187 | User 3
 |  |  | Entry | NST | 2 | JE1169 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 150 | 150 | User 11
 |  |  | Line | NST | 1 | JE1169 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 11
 |  |  | GrLine | NST | 1 | JE1169 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 11
 |  |  | Entry | NST | 2 | JE1170 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5
 |  |  | Line | NST | 1 | JE1170 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5
 |  |  | Line | NST | 1 | JE1170 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5
 |  |  | Entry | NST | 2 | JE1179 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5
 |  |  | Line | NST | 1 | JE1179 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5
 |  |  | Line | NST | 1 | JE1179 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5
 |  |  | Entry | NST | 2 | JE1156 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5
 |  |  | Line | NST | 1 | JE1156 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5
 |  |  | Line | NST | 1 | JE1156 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5
 |  |  | Entry | NST | 2 | JE1160 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 6,User 10,User 20
 |  |  | Line | NST | 1 | JE1160 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 10
 |  |  | Line | NST | 1 | JE1160 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 20
 |  |  | Output:Showing which color and why
 |  |  | Grouping Level | Type of Entry | No. of Lines | Journal Entry Number | Account Number / Journal Entry Description | Journal Line Description | Account Name |  |  | Debit | Credit | User ID
 |  |  | Entry | NST | 2 | JE1146 | THE WORST JE EVER!! I BET THERE IS FRAUD IN HERE!! |  |  |  |  | 1000000 | 1000000 | User 5 | black in color as it parent and having only One User id
 |  |  | Line | NST | 1 | JE1146 | A0801 |  | Unexpected Income |  |  | 0 | 1000000 | User 5 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Entry | NST | 2 | JE1171 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 400000 | 400000 | User 13,User 5 | black in color as the parent Entry level is having Multiple User ids for the same Journal Entry Number
 |  |  | Line | NST | 1 | JE1171 | A0998 |  | Test Expense 1 |  |  | 400000 | 0 | User 13 | black in color as the entry level is having mulitple user ids and the childs also should be in black color
 |  |  | Line | NST | 1 | JE1171 | A0999 |  | MISC Expense |  |  | 0 | 400000 | User 5 | black in color as the entry level is having mulitple user ids and the childs also should be in black color
 |  |  | Entry | NST | 2 | JE1172 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 333187 | 333187 | User 15,User 5,User 3 | black in color as the parent Entry level is having Multiple User ids for the same Journal Entry Number
 |  |  | Line | NST | 1 | JE1172 | A0998 |  | Test Expense 1 |  |  | 333187 | 0 | User 5 | black in color as the entry level is having mulitple user ids and the childs also should be in black color
 |  |  | GrLine | NST | 1 | JE1172 | A0999 |  | MISC Expense |  |  | 0 | 333187 | User 3 | black in color as the entry level is having mulitple user ids and the childs also should be in black color
 |  |  | Entry | NST | 2 | JE1169 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 150 | 150 | User 11 | black in color as it parent and having only One User id
 |  |  | Line | NST | 1 | JE1169 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 11 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | GrLine | NST | 1 | JE1169 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 11 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Entry | NST | 2 | JE1170 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5 | black in color as it parent and having only One User id
 |  |  | Line | NST | 1 | JE1170 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Line | NST | 1 | JE1170 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Entry | NST | 2 | JE1179 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5 | black in color as it parent and having only One User id
 |  |  | Line | NST | 1 | JE1179 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Line | NST | 1 | JE1179 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Entry | NST | 2 | JE1156 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5 | black in color as it parent and having only One User id
 |  |  | Line | NST | 1 | JE1156 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Line | NST | 1 | JE1156 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5 | white in color as it is child and having same User id as that of the Entry Level
 |  |  | Entry | NST | 2 | JE1160 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 6,User 10,User 20 | black in color as the parent Entry level is having Multiple User ids for the same Journal Entry Number
 |  |  | Line | NST | 1 | JE1160 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 10 | black in color as the entry level is having mulitple user ids and the childs also should be in black color
 |  |  | Line | NST | 1 | JE1160 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 20 | black in color as the entry level is having mulitple user ids and the childs also should be in black color
 |  |  | Output:having the coloring and the required output that I want the Conditional Format in.
 |  |  | Grouping Level | Type of Entry | No. of Lines | Journal Entry Number | Account Number / Journal Entry Description | Journal Line Description | Account Name |  |  | Debit | Credit | User ID
 |  |  | Entry | NST | 2 | JE1146 | THE WORST JE EVER!! I BET THERE IS FRAUD IN HERE!! |  |  |  |  | 1000000 | 1000000 | User 5
 |  |  | Line | NST | 1 | JE1146 | A0801 |  | Unexpected Income |  |  | 0 | 1000000 | User 5
 |  |  | Entry | NST | 2 | JE1171 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 400000 | 400000 | User 13,User 5
 |  |  | Line | NST | 1 | JE1171 | A0998 |  | Test Expense 1 |  |  | 400000 | 0 | User 13
 |  |  | Line | NST | 1 | JE1171 | A0999 |  | MISC Expense |  |  | 0 | 400000 | User 5
 |  |  | Entry | NST | 2 | JE1172 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 333187 | 333187 | User 15,User 5,User 3
 |  |  | Line | NST | 1 | JE1172 | A0998 |  | Test Expense 1 |  |  | 333187 | 0 | User 5
 |  |  | GrLine | NST | 1 | JE1172 | A0999 |  | MISC Expense |  |  | 0 | 333187 | User 3
 |  |  | Entry | NST | 2 | JE1169 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 150 | 150 | User 11
 |  |  | Line | NST | 1 | JE1169 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 11
 |  |  | GrLine | NST | 1 | JE1169 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 11
 |  |  | Entry | NST | 2 | JE1170 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5
 |  |  | Line | NST | 1 | JE1170 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5
 |  |  | Line | NST | 1 | JE1170 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5
 |  |  | Entry | NST | 2 | JE1179 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5
 |  |  | Line | NST | 1 | JE1179 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5
 |  |  | Line | NST | 1 | JE1179 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5
 |  |  | Entry | NST | 2 | JE1156 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 5
 |  |  | Line | NST | 1 | JE1156 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 5
 |  |  | Line | NST | 1 | JE1156 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 5
 |  |  | Entry | NST | 2 | JE1160 | 02. ENTRIES MADE TO UNUSUAL ACCOUNTS |  |  |  |  | 275 | 275 | User 6,User 10,User 20
 |  |  | Line | NST | 1 | JE1160 | A0998 |  | Test Expense 1 |  |  | 150 | 0 | User 10
 |  |  | Line | NST | 1 | JE1160 | A0999 |  | MISC Expense |  |  | 0 | 150 | User 20