Highlighting Row with Duplicate Text

[cameronfoley]

Good morning everyone,

I've had a look through the forum but I can't seem to find a similar case, might have just missed it.

I've created a spreadsheet that contains lots of names of bands and I'd like either a notification or a change of colour if the data is repeated more than 3 times in a row. I'm not looking for one specific value it would be any text to be flagged if it has been repeated in the row.

In the example attached I was hoping that Dynamite would be flagged that it's been repeated, or that it would show that there are entries of data more than 3 times in the row in the end cell but I can't seem to figure it out.

It's probably something simple but i'm still relatively novice when it comes to excel.

Any help would be greatly appreciated,

Thanks everyone!

[etaf]

if you happy to have the same colour you can use a conditional formatting formula

and then use a countif () > 1

but you say row and more than 3
however, "variety" is highlighted and only appears in a row once

can you load a sample spreadsheet here
Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[cameronfoley]

Thank you for your help,

I've attached the document with it's current state.

There is no conditional formatting in place the spreadsheet it just colour coded.

[mstwntd]

I have set up the conditional formatting to give you a red background fill if there are 3 values the same in a row in a row. Were you meaning that if the same band played the same venue 3 times regardless of the time between? If so, then this will not work. I have done the formatting from Col E to J. The layout of the spreadsheet makes doing the rest time consuming. The merged cells for each month means the conditional formula will have to be adjusted for each column after the merged one. Look at how the formula in J is different and has to skip col I.I have also set up a counter and Flag at the bottom that shows if you have the same band multiple venues in the same day. See cell J58. Variety shows more than once (4 times).

I hope this helps and is what you were looking for.

Cheers

[cameronfoley]

Thank you mstwntd.

Yeah the whole principle is to make sure that no band is in the same venue more than 3 times over the year.

The merged month cell can come out that's not an issue.

I can't see anything in J58.

Thank you so much, it's far past my expertise in excel and it is greatly appreciated.

Thanks in advance,

[mstwntd]

Ok, then that makes it easier. Does the output have to be in this matrix? If not a simple spreadsheet can do the whole process much faster. You could have a report showing Bands, venues and counts.

[cameronfoley]

It's part of the planning process to make sure that we don't repeat any bands whilst entering them in.

I'd love for it to be able to generate a sheet from the data that would refer to the date performing, the venue and the band name to be able to send to the bands but I don't think that's something that Excel can do.

[mstwntd]

I have taken a sample of the data and switched it into a clean block. I have forced the last band to have more than 3 at the same venue to show how the flag works. I have added a pivot table to the side to give you a summary.

I hope this helps.

[mstwntd]

The pivot table can do that for you.

You would just have to move the bands to the row section and the venue to the column section. Then double click on the Grand Total on a band line and Excel will give you everything that makes up that total on it's own tab, ready to send out.