Hi there,
I hope you're good. Got a problem that has been bugging me for a while, and I wondered if anyone could help.
I have a template spreadsheet into which I post a set of data. Each column of the destination spreadsheet has conditional formatting applied to it to detect any issues with the data in that column (ie too many/too few characters, needs to be a number, if it is a duplicate, etc). If there is an issue it gets highlighted red.
I am handling 30+ columns and can have several thousand rows, so there is no way to do it manually. At the end of it, there may only be small % where there is an issue, and I need to be able to quickly find them all (while keeping the remainder of them available for manual checks) so that I can send them to be checked.
At the moment I add an additional 'Issues' column I add at the end and manually click into each filter, checking for the 'Filter: By Colour' option. If there is I select it, then add an indicator into all the cells of the Issues column. I then clear the filter and try the next column filter. After each process I now have a column I can use to filter to all issues. It can be pretty painstaking and I've tripped up a couple of times by not clearing a filter.
What I want to do is automatically identify all rows in which the conditional formatting has highlighted them.
Ideally I'd like a formula to detect if any of the cells in the row of data contain a coloured cell then mark the Issues column with an indicator.
Ideally a formula would do it but I can't find any that seem relevant. I've searched for this in Excel and people are recommending I create a user-defined function, which sounds complicated. I have plenty of experience with using formulas in Excel but have never needed to delve into Macros or functions before.
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]City / County[/TD]
[TD]Postcode[/TD]
[TD]Contact[/TD]
[TD]Issue[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Joe's Shop[/TD]
[TD]57 Belvedere Avenue[/TD]
[TD]Finchley[/TD]
[TD]London[/TD]
[TD]N3 5BQ[/TD]
[TD]Joe Bloggs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DGM Ltd *[/TD]
[TD]Carbon Technology Park[/TD]
[TD]Ossulton Way[/TD]
[TD]North London[/TD]
[TD]N10 5TG[/TD]
[TD]Daniel Figgs[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Red Ads Media[/TD]
[TD]Stamford House, 7th floor, 81 The Mews, Salford, Greater Manchester, M12 4EF *[/TD]
[TD][/TD]
[TD][/TD]
[TD]*[/TD]
[TD]Jordan Price[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DGM Ltd *[/TD]
[TD]54 Churchill Road[/TD]
[TD]Nottingham[/TD]
[TD]East Midlands[/TD]
[TD]L31 4RL[/TD]
[TD]Daniel %26%26%26%2656429348924
Figgs*[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
Crude example of data above. Items with asterix have an issue (DGM Ltd repeated, address too long, Required postcode, HTML detected) and would be highlighted by the conditional formatting. I want to be able to select lines 2, 3 & 4 by automatically adding Y to the issue column to to indicate an issue.
Oh, and the kicker - I'm using Excel for Mac 2011...
Can anyone help me?
Best regards
Ben
I hope you're good. Got a problem that has been bugging me for a while, and I wondered if anyone could help.
I have a template spreadsheet into which I post a set of data. Each column of the destination spreadsheet has conditional formatting applied to it to detect any issues with the data in that column (ie too many/too few characters, needs to be a number, if it is a duplicate, etc). If there is an issue it gets highlighted red.
I am handling 30+ columns and can have several thousand rows, so there is no way to do it manually. At the end of it, there may only be small % where there is an issue, and I need to be able to quickly find them all (while keeping the remainder of them available for manual checks) so that I can send them to be checked.
At the moment I add an additional 'Issues' column I add at the end and manually click into each filter, checking for the 'Filter: By Colour' option. If there is I select it, then add an indicator into all the cells of the Issues column. I then clear the filter and try the next column filter. After each process I now have a column I can use to filter to all issues. It can be pretty painstaking and I've tripped up a couple of times by not clearing a filter.
What I want to do is automatically identify all rows in which the conditional formatting has highlighted them.
Ideally I'd like a formula to detect if any of the cells in the row of data contain a coloured cell then mark the Issues column with an indicator.
Ideally a formula would do it but I can't find any that seem relevant. I've searched for this in Excel and people are recommending I create a user-defined function, which sounds complicated. I have plenty of experience with using formulas in Excel but have never needed to delve into Macros or functions before.
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]City / County[/TD]
[TD]Postcode[/TD]
[TD]Contact[/TD]
[TD]Issue[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Joe's Shop[/TD]
[TD]57 Belvedere Avenue[/TD]
[TD]Finchley[/TD]
[TD]London[/TD]
[TD]N3 5BQ[/TD]
[TD]Joe Bloggs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DGM Ltd *[/TD]
[TD]Carbon Technology Park[/TD]
[TD]Ossulton Way[/TD]
[TD]North London[/TD]
[TD]N10 5TG[/TD]
[TD]Daniel Figgs[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Red Ads Media[/TD]
[TD]Stamford House, 7th floor, 81 The Mews, Salford, Greater Manchester, M12 4EF *[/TD]
[TD][/TD]
[TD][/TD]
[TD]*[/TD]
[TD]Jordan Price[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DGM Ltd *[/TD]
[TD]54 Churchill Road[/TD]
[TD]Nottingham[/TD]
[TD]East Midlands[/TD]
[TD]L31 4RL[/TD]
[TD]Daniel %26%26%26%2656429348924
Figgs*[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
Crude example of data above. Items with asterix have an issue (DGM Ltd repeated, address too long, Required postcode, HTML detected) and would be highlighted by the conditional formatting. I want to be able to select lines 2, 3 & 4 by automatically adding Y to the issue column to to indicate an issue.
Oh, and the kicker - I'm using Excel for Mac 2011...
Can anyone help me?
Best regards
Ben
