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

swaink

Active Member
Joined
Feb 15, 2002
Messages
432
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 (y)
Colorama Check.xls
ACADAEAF
5AgentPostcodeTime
6CD07CR02TA09:27:01
7GG11G332QZ11:39:01
8GG11G332QZ11:39:01
9GG11G332QZ11:39:01
10GG11G332QZ11:39:01
11GG11G332QZ11:39:01
12GG11G332QZ11:39:01
13NA09CF729EE11:59:01
14BR06BS374NG08:47:01
15BR06BS374NG08:47:01
16BR06BS374NG10:06:01
17BR06BS374NG13:28:01
18CF21CM61AQ11:02:01
19CF21CM61AQ11:02:01
20YV34DT40LX08:49:01
21YV18DT49PQ10:33:01
22PS09BB67QL08:58:01
Sheet1
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

bbrnx19

Board Regular
Joined
Oct 25, 2006
Messages
148
swaink
try this user function
Code:
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

Active Member
Joined
Feb 15, 2002
Messages
432
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

Board Regular
Joined
Oct 25, 2006
Messages
148
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
Code:
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

Active Member
Joined
Feb 15, 2002
Messages
432

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 15, 2002
Messages
432

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=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
ACADAEAF
5AgentPostcodeTime
6CD07CR02TA9:27:01Grouped
7GG11G332QZ11:39:01Grouped
8GG11G332QZ11:39:01Grouped
9GG11G332QZ11:39:01Grouped
10GG11G332QZ11:39:01Grouped
11GG11G332QZ11:39:01Grouped
12GG11G332QZ11:39:01Grouped
13NA09CF729EE11:59:01Grouped
14BR06BS374NG8:47:01Ungrouped
15BR06BS374NG8:47:01Ungrouped
16BR06BS374NG10:06:01Ungrouped
17BR06BS374NG13:28:01Ungrouped
18CF21CM61AQ11:02:01Grouped
19CF21CM61AQ11:02:01Grouped
20YV34DT40LX8:49:01Grouped
21YV18DT49PQ10:33:01Grouped
22PS09BB67QL8:58:01Grouped
Groups
 

swaink

Active Member
Joined
Feb 15, 2002
Messages
432
Hi Peter :pray:

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 :biggrin:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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):
Code:
=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
ACADAEAF
5AgentPostcodeTime
6CD07CR02TA9:27:01Grouped
7GG11G332QZ11:39:01Grouped
8GG11G332QZ11:39:01Grouped
9GG11G332QZ11:39:01Grouped
10GG11G332QZ11:39:01Grouped
11GG11G332QZ11:39:01Grouped
12GG11G332QZ11:39:01Grouped
13NA09CF729EE11:59:01Grouped
14BR06BS374NG8:47:01Ungrouped
15BR06BS374NG8:47:01Ungrouped
16BR06BS374NG10:06:01Ungrouped
17BR06BS374NG13:28:01Ungrouped
18CF21CM61AQ11:02:01Grouped
19CF21CM61AQ11:02:01Grouped
20YV34DT40LX8:49:01Grouped
21YV18DT49PQ10:33:01Grouped
22PS09BB67QL8:58:01Grouped
Groups
 

Watch MrExcel Video

Forum statistics

Threads
1,113,993
Messages
5,545,378
Members
410,679
Latest member
rolandbianco
Top