Task In Excel

[Ayush_Jadli]

I have a task in Excel.

I have to highlight some specific data, the task is:

There are some rows which are:
1. Upper Case
2. Colored Light Gold (as can be seen in the image below)

So I want to perform the task on those rows only.

Check above and below that Row (Upper cased row) until the next Row (Upper cased row) that if the Bank Name (Column A) are similar to that Row (Upper cased row), if it is similar then highlight those rows with Light Green color which are similar (they can be multiple).

And if the Bank (Column A) is not exact similar, but a little similar then move on to check other columns like: Address (Column B), City (Column C), ST (Column D), Zip (Column E) only till column E, so if besides Bank (Column A) any of these matches too, so this should also be highlighted.

If Bank (Column A) matches but other Columns doesn't then don't highlight.

Have a look at the images below for better understanding.

Example of how the task should be done:

[#2]

@Ayush_Jadli 

To achieve the task you have described in Excel 365, you can use conditional formatting with a formula that checks for similarity between rows and highlights them accordingly. You will need to use a combination of Excel functions to compare the values in the specified columns (Bank, Address, City, ST, Zip) for similarity.

Here are the steps to set up this conditional formatting:

1. Select the Range: Select the range of cells where you want to apply this conditional formatting. It seems you want to apply this to the entire worksheet.
2. Conditional Formatting: Go to the "Home" tab in Excel, and then click on "Conditional Formatting" in the ribbon.
3. New Rule: Select "New Rule" from the dropdown menu.
4. Use a Formula: In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
5. Enter the Formula: In the formula bar, enter the following formula:

=AND(ROW()>1, EXACT($A2, $A1), OR(EXACT($B2, $B1), EXACT($C2, $C1), EXACT($D2, $D1), EXACT($E2, $E1)))

This formula checks if:

• The row is not the first row (ROW() > 1).
• The Bank name in the current row (Column A) is exactly the same as the Bank name in the previous row (Column A).
• Either the Address, City, ST, or Zip in the current row matches the corresponding value in the previous row.
1. Format: Click the "Format" button to specify the formatting you want for matching rows. You can choose the "Light Green" fill color.
2. Apply: Click "OK" to apply the formatting.
3. Done: Click "OK" again in the "New Formatting Rule" dialog box.

Now, Excel will highlight rows that match your criteria. Make sure to adjust the range you selected in step 1 based on your actual data. This conditional formatting rule will check for similarity in the specified columns and apply the formatting accordingly.

Remember to adjust the formula and rule as needed if you have additional columns or specific requirements for similarity matching.The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you

Was the answer useful? Mark them as helpful!

This will help all forum participants.

[#3]

I want to apply the formula only where the value is in upper case

[#4]

@Ayush_Jadli 

To apply the conditional formatting formula only to rows where the value is in uppercase, you can modify the formula to check the case of the text in Column A before applying the formatting. Here are the steps:

Step 1: Conditional Formatting for Bank Name (Column A):

1. Select the range of rows you want to apply formatting to, starting from the first row with uppercase text and light gold fill color.
2. Go to the "Home" tab, click on "Conditional Formatting," and choose "New Rule."
3. Choose "Use a formula to determine which cells to format."
4. In the formula bar, enter the following formula to check if the Bank Name in the current row (Column A) matches the Bank Name in the first row (let's say the first row is row 2) and if the text in Column A is in uppercase:

=AND($A2=$A$2, EXACT($A2, UPPER($A2)))

This formula checks both if the Bank Name matches the first row and if it's in uppercase.

1. Click on the "Format" button and set the formatting style you want (e.g., Light Green background). Click "OK" to confirm.

This modified formula will apply formatting only to rows where the Bank Name in Column A matches and is in uppercase. It ensures that the formatting is applied selectively based on your criteria. You can then create additional rules for other columns, if needed, following a similar approach.