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.
<tbody>
</tbody>
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.
| ID | Name | Address 1 | Address 2 | City / County | Postcode | Contact | Issue |
| 1 | Joe's Shop | 57 Belvedere Avenue | Finchley | London | N3 5BQ | Joe Bloggs | |
| 2 | DGM Ltd * | Carbon Technology Park | Ossulton Way | North London | N10 5TG | Daniel Figgs | Y |
| 3 | Red Ads Media | Stamford House, 7th floor, 81 The Mews, Salford, Greater Manchester, M12 4EF * | * | Jordan Price | Y | ||
| 4 | DGM Ltd * | 54 Churchill Road | Nottingham | East Midlands | L31 4RL | Daniel %26%26%26%2656429348924 Figgs* | Y |
<tbody>
</tbody>
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
