check group status

userman · #1 04-30-2012, 11:29 AM

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!

macropod · #2 05-01-2012, 12:23 AM

Hi userman,

You could do the check with a macro, or possibly even with a formula, though having a variable number of columns per record makes this more difficult. However, it is not clear what you want to do with the results - suppose one record is found to be 'NOT OK', what then?

userman · #3 05-01-2012, 05:33 AM

Quote:

Originally Posted by macropod

Hi userman,

You could do the check with a macro, or possibly even with a formula, though having a variable number of columns per record makes this more difficult. However, it is not clear what you want to do with the results - suppose one record is found to be 'NOT OK', what then?

if one record is 'NOT OK', then the whole group that this record (row) belongs is 'NOT OK'

then, let's have a report, a list of all the groups and their status at a right column

macropod · #4 05-01-2012, 07:47 AM

Here's a macro for processing the data in Excel. If the number of columns is liable to vary as you suggest, the simplest solution would be to have the status data as the second field, with the group IDs as the first field. The macro would need some minor re-coding for that, but that's a lot easier than coding for variable column counts, especially if the number of columns per row varies.

Code:

Sub UpdateGroupFlags()
Application.ScreenUpdating = False
Dim I As Long, LastRow As Long, strGrp As String, strStatus As String
LastRow = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
With Worksheets("Sheet1")
  .Range("A1:C" & LastRow).Sort Key1:=.Columns("A"), Key2:=.Columns("C"), _
    Key2:=.Columns("B"), Order1:=xlAscending, Header:=xlNo
    For I = 2 To LastRow
      If .Range("A" & I).Value <> strGrp Then
        strGrp = .Range("A" & I).Value
        strStatus = .Range("C" & I).Value
      Else
        .Range("C" & I).Value = strStatus
      End If
    Next
End With
Application.ScreenUpdating = True
End Sub

userman · #5 05-01-2012, 07:53 AM

works flawlessly thanks!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Resource Status	BrownHornet	Outlook	0	04-12-2012 10:47 PM
Link word check box to access check box	Mrkieth	Word	4	01-30-2012 06:43 AM
Checking ref # status based on sheet2 ref #.	aligahk06	Excel	1	04-26-2010 11:22 PM
Sending 1 message but status bar says 1 of 4...	baldmancan	Outlook	4	03-06-2009 12:04 PM
Document status changes to read only	rtankersley	Word	0	10-01-2008 05:15 AM