Lookup a person to see if they have had a ticket raised in last 12 months

[BatemanLDN]

Hi have a list of users and a list of tickets that have been raised in the last 12 months.
I am trying to put together a formula that looks up the user in the list of tickets to check if they have had a ticket raised for them.
The last 12 months doesnt need to be in the formula as my list of tickets is only the last 12 months worth so it will be assumed that if there is no match then they have had no tickets raised for them in the last 12 months. Thinking ahead, it would be an added bonus if there was something that could be put into the formula to control how many months back it looks, i.e. last 6 months (instead of having to remove the data).
The name list of users is in alphabetical order and the list of tickets is in chrnological order.
The data is on 2 different sheets in 1 workbook, hopefully that doesnt cause issues.
If any more information is needed please let me know.
Thank you in advance

 

[etaf]

not sure how you data is laid out
but a lookup
index/match should work - looking up the user name - but will return only 1 entry

index(cell range with ticket numbers , match ( cell with user name , range of users ,0 ))

also can add a lookup for date

are you still usng 2016 version ?

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[BatemanLDN]

Thanks for the quick reply.
Yes im on 2016 on work laptop which has quite a lot of restrictions but 2019 on personal laptop.

Hopefully these min-sheets work!

Person lookup if ticket logged.xlsx
	C
20
All Users

Person lookup if ticket logged.xlsx
	D
12
Requests Nov 2022 Oct 2023

 

[BatemanLDN]

Person lookup if ticket logged.xlsx
	A	B	C	D	E
1	NAME	EMAIL	UPN	Ticket logged for User in Last 12 months?	Answer should be:
2	name1	email1	username1	?	Yes
3	name2	email2	username2	?	Yes
4	name3	email3	username3	?	Yes
5	name4	email4	username4	?	Yes
6	name5	email5	username5	?	No
7	name6	email6	username6	?	No
8	name7	email7	username7	?	No
9	name8	email8	username8	?	No
10	name9	email9	username9	?	No
11	name10	email10	username10	?	No
All Users

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Cell Value	duplicates	text	NO

Person lookup if ticket logged.xlsx
	A	B	C	D
1	Requested for Name	UPN	Request Id	Created time
2	Name1	Username1	001	December 14, 2022 03:23:41 PM
3	Name2	Username2	002	April 19, 2023 11:39:54 AM
4	Name3	Username3	003	April 19, 2023 11:40:47 AM
5	Name4	Username4	004	July 27, 2023 12:32:42 PM
6	Name11	Username11	005	July 27, 2023 11:52:31 AM
7	Name12	Username12	006	December 13, 2022 01:13:12 PM
8	Name13	Username13	007	January 11, 2023 09:45:39 AM
9	Name14	Username14	008	January 11, 2023 09:46:45 AM
10	Name15	Username15	009	April 12, 2023 08:19:02 AM
11	Name16	Username16	010	July 11, 2023 12:52:36 PM
Requests Nov 2022 Oct 2023

 

[etaf]

=IF(COUNTIF(Sheet2!$A$2:$A$11,Sheet1!A2),"Yes","No")

using the NAME column, - however could use USERNAME if unique

countifs() to include a date

are the dates in your file real dates or text
click on a date - right click , format > general - should return a number

Book4
	A	B	C	D	E	F
1	NAME	EMAIL	UPN	Ticket logged for User in Last 12 months?	Answer should be:
2	name1	email1	username1	?	Yes	Yes
3	name2	email2	username2	?	Yes	Yes
4	name3	email3	username3	?	Yes	Yes
5	name4	email4	username4	?	Yes	Yes
6	name5	email5	username5	?	No	No
7	name6	email6	username6	?	No	No
8	name7	email7	username7	?	No	No
9	name8	email8	username8	?	No	No
10	name9	email9	username9	?	No	No
11	name10	email10	username10	?	No	No
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=IF(COUNTIF(Sheet2!$A$2:$A$11,Sheet1!A2),"Yes","No")

Book4
	A	B	C	D
1	Requested for Name	UPN	Request Id	Created time
2	Name1	Username1	1	44909.6414
3	Name2	Username2	2	45035.486
4	Name3	Username3	3	45035.4867
5	Name4	Username4	4	45134.5227
6	Name11	Username11	5	45134.4948
7	Name12	Username12	6	44908.5508
8	Name13	Username13	7	44937.4067
9	Name14	Username14	8	44937.4075
10	Name15	Username15	9	45028.3465
11	Name16	Username16	10	45118.5365
Sheet2

 

[BatemanLDN]

Thanks! THat formula worked great.
Yep, the dates are actual dates.

If i was to introduce another IF statement to this formula would you recommend it or do you think it should be separate?
In addition to the formula already done i also need to know if the user has logged on in the last 12 months. The field for this information is Sheet1E:E. This field/column is also an actual date field.

 

[BatemanLDN]

Thanks! THat formula worked great.
Yep, the dates are actual dates.

If i was to introduce another IF statement to this formula would you recommend it or do you think it should be separate?
In addition to the formula already done i also need to know if the user has logged on in the last 12 months. The field for this information is Sheet1E:E. This field/column is also an actual date field.

It would be an OR too if this makes any difference?

Question the folmula is answering - Has the user had a ticket logged for them in the last 12 months OR have they logged on in the last 12 months.

Scenarios:
1. A user could have logged-in but not have a ticket logged for them
2. A user could have not logged-in but had a ticket logged for them
3. Both logged-in and ticket logged for them
4. Both not logged-in and no ticket logged for them.

Thanks again

 

[etaf]

you could add an OR to the existing IF
at the moment you can use
COUNTIF(Sheet2!$A$2:$A$11,Sheet1!A2)>0
for 12mths
then use
COUNTIF(Sheet1!$E$2:$E$11,Sheet1!A2)>0
if its on a different sheet and range

you can also extend the range to more rows

OR( COUNTIF(Sheet2!$A$2:$A$100,Sheet1!A2)>0 , COUNTIF(Sheet1!$E$2:$E$100,Sheet1!A2)>0 )

IF ( OR( COUNTIF(Sheet2!$A$2:$A$100,Sheet1!A2)>0 , COUNTIF(Sheet1!$E$2:$E$100,Sheet1!A2)>0 ) , "Yes", "No")

 

[BatemanLDN]

Thanks for your help with this one - much appreciated!

 

[etaf]

you are welcome