combine or count data

Board Regular
I am trying to write a formula that gives me a 1 if user, ticket, and time(within 10 minutes of each other) are the same. for example the second and third entries one of those would get a 1 the other would get a 0. Any ideas?

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

navic

Active Member
Manually entered expected results would be helpful.
Maybe I didn't understand you well, but here's a try.
In the 'J' column set the 'helper' data.
The formula in the 'J2' cell is below (copy down)
Code:
``=CONCATENATE(\$A2,\$B2,E2)``
The formula below, paste into 'K2' cell (copy down)
Code:
``=COUNTIF(\$J\$2:\$J2,\$J2)-1``
or
Code:
``=COUNTIF(\$J\$2:\$J2,A2&B2&E2)-1``
or reverse counting
Code:
``=COUNTIF(\$J2:\$J\$100,A2&B2&E2)-1``

Attachments

21.2 KB · Views: 3

Board Regular
Sorry in your example rows 9, 10, 11, 12 Since the person, ticket and time are the same I would want column k to show three 0s and 1 for one of them basically counting all 4 as the same thing.

Board Regular
Here is an example of what i am looking for

navic

Active Member

I would want column k to show three 0s and 1 for one of them basically counting all 4 as the same thing
Code:
``=IF(SUMPRODUCT((\$A\$2:\$A2=A2)*(\$E\$2:\$E2=E2))>1,0,1)``

Board Regular
I think that works. I have never used that formula can you explain how it works? Just to help me understand so I can use it again if i need it in the future.

Fluff

MrExcel MVP, Moderator

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Cross posted at: combine or count data
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Board Regular
After doing some checking it is not taking the time into account if you touch a ticket then come back an hour later and do it again each one of those should get a 1.

navic

Active Member
Formula in the helper column 'J2' as follows
Code:
``=CONCATENATE(\$A2,\$E2)``
Formula in the 'K2' as follows
Code:
``=IF(IF(\$J2=0,1,IF(\$J2=\$J1,ROW(A1),1))<2,1,0)``

Attachments

21.5 KB · Views: 2

Board Regular
That is not working the date into the formula either so it is counting some tickets more then once. Rows 16 and 22 are counting even though it is the same ticket touched 3 minutes apart by the same person. If the same ticket is touched by the same person multiple times with in 10 minutes i want that to only count once. You are getting closer then I am so far.
Formula in the helper column 'J2' as follows
Code:
``=CONCATENATE(\$A2,\$E2)``
Formula in the 'K2' as follows
Code:
``=IF(IF(\$J2=0,1,IF(\$J2=\$J1,ROW(A1),1))<2,1,0)``

Replies
1
Views
367
Replies
7
Views
2K

1,147,568
Messages
5,741,874
Members
423,692
Latest member
Bhanu1988

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back