Conditional Formatting Issues

[cteal200]

Hello All,

First time poster here (but I have been reading this site for years)

Have run into a snag in an Excel project I am doing for work. What I am attempting to do is a little difficult to explain so I do hope I get it right on the first try.

Firstly, the formatting that is done must apply to the entire row, not just the cell with the information we are looking for. Secondly, I cannot in anyway use Visual Basic at work. My job will not allow my machine the rights to write, and save code, so please no replies if VBA is necessary to implement your solution.

Here is the problem:

I am using Columns A:W. Column B is used for "record type," of which there are 3 possible choices (Initial, Update, Close). What I need Excel to do is recognize when a "Close," record is entered, and then look at Column G for the unique identifier; in this case, the identifier is a ticket number. What it needs to do then is search Column G for every other record that contains that same identifier, and highlight that row in the color I choose, in this case yellow.

Currently, I can have the row highlighted when using Column B as the input requirements for what needs to be highlighted, but that still leaves everyother record with the same unique identifier as the "Close," one appearing as "Open," or "In-Process." The reason there will be multiple records with the same unique identifier is to be able to identify updates to the ticket number as I am using this workbook in combination with InfoPath, and Access 2010; I have not yet figured out how to have InfoPath and Access update individual records over and over (possibly up to 5 times), so this seemed like the better solution.

Any suggestions on what I can do here? I understand it may be better to use something other than Conditional Formatting, but I do not know what that something is.

 

[AlphaFrog]

If you can use a helper column (column X for this example) then...

Put this in X1 and copy down
=B1&G1

Use this as the Conditional Formatting formula
=COUNTIF($X:$X,"Close"&$G1)

 

[AlphaFrog]

You may be able to use this conditional formatting formula with the COUNTIFS function and no helper column if you have Excel 2007 or later.

=COUNTIFS($B:$B,"Close", $G:$G, $G1)

 

[Fazza]

What about going back a step to address the multiple records for each unique identifier?

My comments are general, as I don't know the specifics of your situation. It sounds like the data in Excel is from Access or some other database. Presumably this is a query and there will be database field that has a time stamp, or sequential record number, or some means to identify the newest record. Using that data, amend the dataset in Excel so that it only has the newest record.

Such as if you currently have data from the database via SQL like

SELECT some fields
FROM table

Change that to something like

SELECT some fields, MAX(sequential_field) AS [time_identifier]
FROM table
GROUP BY some fields

I may be on the wrong path with these comments, as you mention updating records - but I don't know what is being updated from wherever...