Formatting VBA Needed

nrguerrieri

New Member
Joined
May 10, 2018
Messages
39
Office Version
  1. 365
Hello,

So i have an excel table with 9 columns and 10,000 rows. I would like every row in column C to be searched and every cell checks the other cells in column C for any other cell with the same 5 starting characters and then each one that is the same as the other. Those cells have their entire row filled in light grey.

Thanks,

Nick
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,281
I am not sure about the performance, but is it ok to use Conditional Formatting in your project?

I selected A1 cell in the worksheet then I clicked on Conditional Formatting->Manage Rules->New Rule, and selected the "Use a formula to determine which cells to format" option. Selected Format, and set the Fill with gray.
Then I entered the following formula in the "Format values where this formula is true" text box. This is basically checking if cell is not blank and has another cell starting with the same first 5 chars.

Excel Formula:
=AND(NOT(ISBLANK($C1)), COUNTIF($C:$C,LEFT($C1,5) & "*")>1)

1605831907635.png


Clicked OK, and the new rule is in the list. I changed the "Applies to" field to be "$A:$I", and clicked OK.
(In fact, if the row count is certainly limited then may be you might want to use $A1:$I10000 instead of entire column, and use $C1:$C10000 in the formula above if it makes any difference for the performance. I actually tested it with 10K rows in 9 columns, and I didn't notice any problem.)

1605832194989.png


And I got all the rows with this criteria is gray.

Is this something that will help you?

Note: I am a VBA person, but if any non-VBA method is possible, then I prefer to go with it as long as it is not bad for the performance. Honestly, the VBA method might be even more performance killer for this question.
 

nrguerrieri

New Member
Joined
May 10, 2018
Messages
39
Office Version
  1. 365
I am not sure about the performance, but is it ok to use Conditional Formatting in your project?

I selected A1 cell in the worksheet then I clicked on Conditional Formatting->Manage Rules->New Rule, and selected the "Use a formula to determine which cells to format" option. Selected Format, and set the Fill with gray.
Then I entered the following formula in the "Format values where this formula is true" text box. This is basically checking if cell is not blank and has another cell starting with the same first 5 chars.

Excel Formula:
=AND(NOT(ISBLANK($C1)), COUNTIF($C:$C,LEFT($C1,5) & "*")>1)

View attachment 26407

Clicked OK, and the new rule is in the list. I changed the "Applies to" field to be "$A:$I", and clicked OK.
(In fact, if the row count is certainly limited then may be you might want to use $A1:$I10000 instead of entire column, and use $C1:$C10000 in the formula above if it makes any difference for the performance. I actually tested it with 10K rows in 9 columns, and I didn't notice any problem.)

View attachment 26409

And I got all the rows with this criteria is gray.

Is this something that will help you?

Note: I am a VBA person, but if any non-VBA method is possible, then I prefer to go with it as long as it is not bad for the performance. Honestly, the VBA method might be even more performance killer for this question.
Hey thanks for the response. Doesnt seem to be working. See image. Looks like some things are being grayed out when their is no duplicate like 125 lounge and some arent greyed out when their is a suplicate like the 24 hour food mart.
 

Attachments

  • Screen Shot 2020-11-19 at 10.22.50 PM.png
    Screen Shot 2020-11-19 at 10.22.50 PM.png
    39.8 KB · Views: 5

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,281
Are you sure that you selected A1 before creating the conditional formatting? It is the most important step.
 

nrguerrieri

New Member
Joined
May 10, 2018
Messages
39
Office Version
  1. 365

ADVERTISEMENT

Are you sure that you selected A1 before creating the conditional formatting? It is the most important step.
That was it! It is now fixed. Thank you sir!

I also have another question. Not sir if its worth doing a new post but I am trying to make a drop down list of that column c but in alphabetical order. Then when that name gets selected. It finds it in the list and strikethroughs the row with that business name.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,281
That was it! It is now fixed. Thank you sir!

You're welcome. Glad to hear it helps.

Regarding the other question, I believe you can still use the Conditional Formatting.
However, I am not sure how you need it.
Are you going to create the drop down separately, or do you need the dropdown to be filled by using the unique values in the column C?
Where are you going to put the dropdown? Somewhere on the worksheet?
Only one business name row will be strikethrough?

If you could answer these questions, then I'll try to help with that part as well since I think it could be still solved by using the same method.

Note: You can create a new question or you can continue here but then you might want to unmark the answer you selected above, so other helpers won't think that this question has been already answered. Just in case if someone else can help faster than I can. However, please just make sure to marking a solution again once you receive an answer which solves your question. In fact, you might even consider revisiting your old questions to mark a solution for the ones you have an answer. Here is the link that you can use to access your question list: Your unsolved questions
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,281

ADVERTISEMENT

@nrguerrieri : I closed the new question that you posted since you decided to unmark the solution in this question, and this thread also has more details about your question.
Please do not create new threads for the same questions - Rule 12 for more details.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,281
Additional question: What is your Office version? (You can set this information in your account to let helpers to know your Office version and platform, so they can provide compatible solutions).

For Office 365, we can use UNIQUE and SORT functions (available in 365) to create a data validation list. Here how I set it up in my worksheet for the same data structure in the original question.

I wrote the following formula in cell K1.
Excel Formula:
=SORT(UNIQUE(OFFSET($C$1,1,,COUNTA($C:$C)-1,1)))

This creates a unique list of business names in the column C as shown below. Since UNIQUE and SORT are spilling functions, the list goes down as much as necessary.
1605898266425.png


Then I selected cell L1, and created a List data validation with the following formula:
Excel Formula:
=$K$1#
1605898398393.png


So, this is the result:
1605898421362.png

You can preferably hide the column K.

Now, the Conditional Formatting step.
I selected A1 cell in the worksheet again, then I clicked on Conditional Formatting->Manage Rules->New Rule, and selected the "Use a formula to determine which cells to format" option. Selected Format, and set the Font as Strikethrough.
Then I entered the following formula in the "Format values where this formula is true" text box. This is basically checking if cell is not blank, and matches the current cell value with the currently selected business name in cell L1. This is also matching the first 5 chars as I assume you still need this here as well. If full match is required, then the formula could be updated by removing the LEFT function.
Excel Formula:
=AND(NOT(ISBLANK($C1)), LEFT($C1,5) = LEFT($L$1,5))

Clicked OK, and the new rule is in the list with the previous rule I created. I changed the "Applies to" field to be "$A:$I", and clicked OK.

1605898761089.png


And I got all the rows matching with the selected business name (first 5 chars) in cell L1 as strikethrough as show below, previous conditional formatting rule is also applied:

1605899009555.png


Hope this helps.
 

nrguerrieri

New Member
Joined
May 10, 2018
Messages
39
Office Version
  1. 365
Additional question: What is your Office version? (You can set this information in your account to let helpers to know your Office version and platform, so they can provide compatible solutions).

For Office 365, we can use UNIQUE and SORT functions (available in 365) to create a data validation list. Here how I set it up in my worksheet for the same data structure in the original question.

I wrote the following formula in cell K1.
Excel Formula:
=SORT(UNIQUE(OFFSET($C$1,1,,COUNTA($C:$C)-1,1)))

This creates a unique list of business names in the column C as shown below. Since UNIQUE and SORT are spilling functions, the list goes down as much as necessary.
View attachment 26456

Then I selected cell L1, and created a List data validation with the following formula:
Excel Formula:
=$K$1#
View attachment 26457

So, this is the result:
View attachment 26458
You can preferably hide the column K.

Now, the Conditional Formatting step.
I selected A1 cell in the worksheet again, then I clicked on Conditional Formatting->Manage Rules->New Rule, and selected the "Use a formula to determine which cells to format" option. Selected Format, and set the Font as Strikethrough.
Then I entered the following formula in the "Format values where this formula is true" text box. This is basically checking if cell is not blank, and matches the current cell value with the currently selected business name in cell L1. This is also matching the first 5 chars as I assume you still need this here as well. If full match is required, then the formula could be updated by removing the LEFT function.
Excel Formula:
=AND(NOT(ISBLANK($C1)), LEFT($C1,5) = LEFT($L$1,5))

Clicked OK, and the new rule is in the list with the previous rule I created. I changed the "Applies to" field to be "$A:$I", and clicked OK.

View attachment 26461

And I got all the rows matching with the selected business name (first 5 chars) in cell L1 as strikethrough as show below, previous conditional formatting rule is also applied:

View attachment 26462

Hope this helps.
Thank you so much for your time.

I just updated my office version on my profile. It is 365.

Also the list you created using the unique function. It is pulling every row which I only want it to show only the visible business names as this is a table where I can filtering certain things.

I think if we fix that. This should work. Also, is there a way to do this without having to creating another column list? Just wondering, worst case I can just hide the cells or column that has this second created list.

Thanks,
 

nrguerrieri

New Member
Joined
May 10, 2018
Messages
39
Office Version
  1. 365
Also, I would like to mak
Thank you so much for your time.

I just updated my office version on my profile. It is 365.

Also the list you created using the unique function. It is pulling every row which I only want it to show only the visible business names as this is a table where I can filtering certain things.

I think if we fix that. This should work. Also, is there a way to do this without having to creating another column list? Just wondering, worst case I can just hide the cells or column that has this second created list.

Thanks,
Additional question: What is your Office version? (You can set this information in your account to let helpers to know your Office version and platform, so they can provide compatible solutions).

For Office 365, we can use UNIQUE and SORT functions (available in 365) to create a data validation list. Here how I set it up in my worksheet for the same data structure in the original question.

I wrote the following formula in cell K1.
Excel Formula:
=SORT(UNIQUE(OFFSET($C$1,1,,COUNTA($C:$C)-1,1)))

This creates a unique list of business names in the column C as shown below. Since UNIQUE and SORT are spilling functions, the list goes down as much as necessary.
View attachment 26456

Then I selected cell L1, and created a List data validation with the following formula:
Excel Formula:
=$K$1#
View attachment 26457

So, this is the result:
View attachment 26458
You can preferably hide the column K.

Now, the Conditional Formatting step.
I selected A1 cell in the worksheet again, then I clicked on Conditional Formatting->Manage Rules->New Rule, and selected the "Use a formula to determine which cells to format" option. Selected Format, and set the Font as Strikethrough.
Then I entered the following formula in the "Format values where this formula is true" text box. This is basically checking if cell is not blank, and matches the current cell value with the currently selected business name in cell L1. This is also matching the first 5 chars as I assume you still need this here as well. If full match is required, then the formula could be updated by removing the LEFT function.
Excel Formula:
=AND(NOT(ISBLANK($C1)), LEFT($C1,5) = LEFT($L$1,5))

Clicked OK, and the new rule is in the list with the previous rule I created. I changed the "Applies to" field to be "$A:$I", and clicked OK.

View attachment 26461

And I got all the rows matching with the selected business name (first 5 chars) in cell L1 as strikethrough as show below, previous conditional formatting rule is also applied:

View attachment 26462

Hope this helps.
Also, instead of a strikethrough. Can we make it so the whole row turns into a green fill?

Then I would like to make a last column that says "On-Boarded'". Then for each row that has green fill, the last column will say "Yes". To show we got that business.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,455
Members
414,239
Latest member
xnanx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top