check group status

[loade]

hello!

I have a text file (or EXCEL file) of the below format:

1223{TAB}GSDF{TAB}OK
1223{TAB}GSDG{TAB}OK
1436{TAB}DFBB{TAB}OK
1436{TAB}CBDF{TAB}NOT OK
1436{TAB}TREG{TAB}OK
etc

I want to check which group (eg 1223, 1436) is OK or NOT OK
If in a group, there is a single NOT OK, then the whole group is NOT OK. Else, it's OK (ie if all group lines are OK).

please note that in the source text, the group entries will always be together (ie there will not be a 1436 line between two 1223 lines, etc), but I may add more columns (however the group column will always be the first and the status (OK or NOT OK) the last)

thanks!

[JBeaucaire]

1) In the Excel file, we assumed the codes are in column A, the "OK/NOT OK" flags in column C

2) Highlight the first column of group codes and use the Data > Filter > Advanced Filter to make a copy of all the unique codes into another column, let's say column G

3) If the first unique code in in G2, put this formula in H2 to get your answer:

=IF(ISNUMBER(MATCH(G2&"NOT OK", INDEX($A$1:$A$100&$C$1:$C$100, 0), 0)), "NOT OK", "OK")

...copy that down.

The formula uses the first 100 rows, expand it as needed to use a larger range. Don't try to use a whole column.

[ChemistB]

Where your original data is in A1:C5 and your unique groups are in Column G starting in G1

=IF(SUMPRODUCT(--($A$1:$A$5=G1),--($C$1:$C$5="Not OK"))>0,"Not Okay", "Okay")
Does that work for you?

[loade]

okay, but I need it to work in any number of rows, not only in 5 rows
any hint?

[ChemistB]

Jerry's is set up for 100 rows. Just change the 5's in mine to any number and it'll work.

[loade]

Jerry's is set up for 100 rows. Just change the 5's in mine to any number and it'll work.

isn't there a way that it will detect all the rows available and do it? it needs a macro?

[JBeaucaire]

You would normally post macro questions in the Programming Forum, which is why we're struggling to give the simplest formula we can.

Here's a dynamic version of my original formula that will work for any amount of data in A:C, matching a code in G2:

=IF(ISNUMBER(MATCH(G2&"NOT OK", INDEX(OFFSET($A$1,,,COUNTA($A:$A),)&OFFSET($A$1,,2,COUNTA($A:$A),), 0), 0)), "NOT OK", "OK")

[JBeaucaire]

All good?

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

[loade]

yes thanks!!

[loade]

unfortunately it is not handy :/

I need it to automatically select all the non-empty cells (apart from first row, which is header row) and then to calculate the OK, NOT OK status of each group (rows of the same group are those that have the same number at column A), as stated above (ie. if a group has a single NOT OK, then it's NOT OK, otherwise, it's OK)

then, display in columns I, J the group number and the status at right

I tried with macro, but dont know what to search for, does it need RegEx?
any hint??

[JBeaucaire]

I understand "It's not handy". It works for me, takes about 10 seconds to do the Advanced Filter, and another 10 to copy the formula in and down the data set.

Now that you have the working formulas, you could record yourself applying the Adv Filter and entering the formulas to get the base code. A few tweaks on that would give you a reusable macro that does the same thing.