I’m pretty decent with Excel, having written routines in VB to create custom menus, perform calculations dependent on user input, even do some simple graphics work. But this one has me confused on how to go
Say I have a data set in a spreadsheet like this
…A…B…C…D
1…Name…Item…Size…Color
2…Bill…Ball…Sm…Red
3…Jake…Card…Lg…Blue
4…Mary…Ring…Med…Green
(I had to use periods as spaces to get things to line up correctly. Ignore them)
I need some way so that if someone enters a new line in row 5 that is not exactly the same as one of the ones above, it will turn red. That is, if someone enters:
Bill Ball Lg Red
it would turn red, alerting the operator that this combination does not exist.
Of course, in real life, I have 14 columns that contain both text and numbers and several hundred (if not a thousand) rows to deal with.
I’d like to use conditional formatting, since it is easy to manage, but I am not adverse to hacking out some VB code if needed. I just need a way to attack the problem.
I have tried Array functions, say enter “=A5:d5=A1:d4” (I had to use lower-case d to prevent the emoticon from appearing)then do the S-C-E (Shift-Control-Enter) thing and it returns “True”, near as I can tell because “Bill” exists in the list.
Just brute force and not very elegant but an added column (hidden if you like) that just concatenates those 14 columns and then conditional format if a vlookup returns N/A.
If you put the concatenation in column O then your lookup formula in row 99 would be
=vlookup(O99,$O$2:O98,1,false)
That will return #N/A if the combo in row 99 doesn’t exist in rows 2 thru 98. The combination of absolute and relative in the array part allows it to be copied and always check from row 2 to “the one just above me”. If multiple duplications exist, all except the first once will get highlighted.
If the total length of the data gets too long or you have a lot of rows that vlookup can get slow on updates.
The vlookup can go in the formula part of the conditional formatting but I would just put it in column P for ease of debugging and the condition format the row based on P.
I like K364’s COUNTIF() solution better than the one I posted because it doesn’t require the mix of absolute and relative and you don’t have to assign meaning to an error.
In that solution all of the duplicates will be highlighted, and mine will not highlight the first. If you want the first unhighlighted, I’d use a mix of the answers with a relative/absolute on the COUNTIF and compare to zero.
I would note that in my mind concatenate (lower case) meant the string of ampersands or the CONCATENATE() function as equivalent implementations.
These are great, just what I had been trying to do. Last night, I worked out some VB code that treated the A1:d4 as a 2-D input array and then compare A5:d5 to sub-arrays. I finally got it to work, but I ended up having to do exactly the same, concatenate the arrays and compare them. I like this way better; not only does it not depend on VB code (which will have to be maintained in the future), but will also lend itself to conditional formatting some more. The only issue I have to deal with is the real data is a mixture of text and numbers, and this method could give a false match if two adjacent cells had 51 and 5 and another line had 5 and 15 in those cells. But, I am much farther along.
You can get around the adjacent columns with similar numbers by forcing the number of digits using the text command. If you want 4 digit numbers, use text(a15,“0000”) in the concatenation.
emoticon from appearing)then do the S-C-E (Shift-Control-Enter) thing and it returns “True”, near as I can tell because “Bill” exists in the list.