Not sure if this is possible but if you don't ask?

[swaink]

I am attempting to check some data to see if everything an agent dealt with was picked at the same time and am comparing the agent code with the destination postcode and pick time.

In the example below there are the three columns Agent, Postcode, Time.

In a further column I want to put a result such as "Grouped" or "Ungrouped".

Where agent = GG11 the postcodes are all the same and the times are all the same therefore the result would be "Grouped"

Where agent = BR06 the postcodes are the same but the times are different therefore the result should be "UnGrouped"

All help appreciated

Regards

Kevin

Colorama Check.xls
	AC	AD	AE	AF
5	Agent	Postcode	Time
6	CD07	CR02TA	09:27:01
7	GG11	G332QZ	11:39:01
8	GG11	G332QZ	11:39:01
9	GG11	G332QZ	11:39:01
10	GG11	G332QZ	11:39:01
11	GG11	G332QZ	11:39:01
12	GG11	G332QZ	11:39:01
13	NA09	CF729EE	11:59:01
14	BR06	BS374NG	08:47:01
15	BR06	BS374NG	08:47:01
16	BR06	BS374NG	10:06:01
17	BR06	BS374NG	13:28:01
18	CF21	CM61AQ	11:02:01
19	CF21	CM61AQ	11:02:01
20	YV34	DT40LX	08:49:01
21	YV18	DT49PQ	10:33:01
22	PS09	BB67QL	08:58:01
Sheet1

 

[bbrnx19]

swaink
try this user function

Function test2(agent, postcode, time)
Dim c As Integer, r As Integer
Dim current As String, after As String, before As String
c = agent.Column
r = agent.Row
current = agent & postcode & time
With Worksheets(agent.Parent.CodeName)
    after = .Cells(r - 1, c) & .Cells(r - 1, c + 1) & .Cells(r - 1, c + 2)
    before = .Cells(r + 1, c) & .Cells(r + 1, c + 1) & .Cells(r + 1, c + 2)
End With
If current = before Or current = after Then
    test2 = "GROUP"
Else
    test2 = "UNGROUP"
End If
End Function

 

[swaink]

Hi bbrnx19

Many thanks for your response I did as you suggested but it only returns Ungrouped

If i apply the formula to where I have blank cells I then get grouped

Kev

 

[bbrnx19]

hi swaink
could be the time values are not a complete match
11:39:01 = 0.485429
11:39:01 = 0.485428
11:39:01 = 0.485427
11:39:01 = 0.485426
if so
try this

Function test2(agent, postcode, time)
Dim c As Integer, r As Integer
Dim current As String, after As String, before As String
c = agent.Column
r = agent.Row
current = agent & postcode & FormatDateTime(time, vbShortTime)
With Worksheets(agent.Parent.CodeName)
 If IsNumeric(.Cells(r - 1, c + 2)) = False Then
 a = ""
 Else
 a = FormatDateTime(.Cells(r - 1, c + 2), vbShortTime)
 End If
  If IsNumeric(.Cells(r + 1, c + 2)) = False Then
 b = ""
 Else
 b = FormatDateTime(.Cells(r + 1, c + 2), vbShortTime)
End If

    after = .Cells(r - 1, c) & .Cells(r - 1, c + 1) & a
    before = .Cells(r + 1, c) & .Cells(r + 1, c + 1) & b
End With
If current = before Or current = after Then
    test2 = "GROUP"
Else
    test2 = "UNGROUP"
End If
End Function

 

[swaink]

Hi bbrnx19

Done that but still got the same result unfortunately.

I re-entered the times etc in a test area and reformated the time fields too but that didn't help.

I'm able to follow your logic with the function, but can't see why it doesn't work.

Really appreciate your time

Kev

 

[Peter_SSs]

swaink

The requirement is not perfectly clear to me. For example for agent BR06 some of the postcodes and times appear the same (rows 14, 15) but others are different (rows 16, 17). Not sure what is required there.

What about manually doing the results for your sample data and posting that for us to have a look at?

 

[swaink]

Hi There

Yes in the case of that agent the postcodes are the same but the times are different therefore I would class this set of data as ungrouped purely because the times were different.

If the Agent has postcodes that are the same and all the times are the same then this would equate to Grouped

Kev

 

[Peter_SSs]

Kev

I am not sure if this does what you want (or if it is the simplest way) but have alook to see if it is headed in the right direction.

Formula in AF6 (copied down) is:

=IF(COUNTIF(AC$6:AC$22,AC6)=SUMPRODUCT(--(AC$6:AC$22=AC6),--(AD$6:AD$22=VLOOKUP(AC6,AC$6:AE$22,2,0)),--(AE$6:AE$22=VLOOKUP(AC6,AC$6:AE$22,3,0))),"Grouped","Ungrouped")

Mr Excel.xls
	AC	AD	AE	AF
5	Agent	Postcode	Time
6	CD07	CR02TA	9:27:01	Grouped
7	GG11	G332QZ	11:39:01	Grouped
8	GG11	G332QZ	11:39:01	Grouped
9	GG11	G332QZ	11:39:01	Grouped
10	GG11	G332QZ	11:39:01	Grouped
11	GG11	G332QZ	11:39:01	Grouped
12	GG11	G332QZ	11:39:01	Grouped
13	NA09	CF729EE	11:59:01	Grouped
14	BR06	BS374NG	8:47:01	Ungrouped
15	BR06	BS374NG	8:47:01	Ungrouped
16	BR06	BS374NG	10:06:01	Ungrouped
17	BR06	BS374NG	13:28:01	Ungrouped
18	CF21	CM61AQ	11:02:01	Grouped
19	CF21	CM61AQ	11:02:01	Grouped
20	YV34	DT40LX	8:49:01	Grouped
21	YV18	DT49PQ	10:33:01	Grouped
22	PS09	BB67QL	8:58:01	Grouped
Groups

 

[swaink]

Hi Peter ray:

Yes thats done the trick, I knew this one was a big ask and was really pleased when you guys responded at least now I know that a silly idea actually worked.

Couldn't have achieved this without your help it really is appreciated.

Thanks to bbrnx19 too

All the every Best and thanks again

Kev

 

[Peter_SSs]

Kev

Although that worked, I didn't really like the look of the formula. Here is what I believe is a better one. AF6 (copied down):

=IF(COUNTIF(AC$6:AC$22,AC6)=SUMPRODUCT(--(AC$6:AC$22&AD$6:AD$22&AE$6:AE$22=AC6&AD6&AE6)),"Grouped","Ungrouped")

Mr Excel 06 10 29.xls
	AC	AD	AE	AF
5	Agent	Postcode	Time
6	CD07	CR02TA	9:27:01	Grouped
7	GG11	G332QZ	11:39:01	Grouped
8	GG11	G332QZ	11:39:01	Grouped
9	GG11	G332QZ	11:39:01	Grouped
10	GG11	G332QZ	11:39:01	Grouped
11	GG11	G332QZ	11:39:01	Grouped
12	GG11	G332QZ	11:39:01	Grouped
13	NA09	CF729EE	11:59:01	Grouped
14	BR06	BS374NG	8:47:01	Ungrouped
15	BR06	BS374NG	8:47:01	Ungrouped
16	BR06	BS374NG	10:06:01	Ungrouped
17	BR06	BS374NG	13:28:01	Ungrouped
18	CF21	CM61AQ	11:02:01	Grouped
19	CF21	CM61AQ	11:02:01	Grouped
20	YV34	DT40LX	8:49:01	Grouped
21	YV18	DT49PQ	10:33:01	Grouped
22	PS09	BB67QL	8:58:01	Grouped
Groups