vlook up help

[Tonyk1051]

is there a way to customize the vlook up so that when I use it ,not only only does it populate based on keysheet but
If the same user, on the same date, uses the same action type on the same vendor, they all get the number 0 in column H instead except for the line that has the earliest date. that number remains the same. See the drop box link for workbook of how it should look manually. if not a vlook up then a different formula maybe?

single type issue.xlsx

Shared with Dropbox

www.dropbox.com

 

[etaf]

works for me, using formula as posted earlier
BUT the dates have times and so are ALL different
If you want to compare just the date without the time , then i have set a different column using INT()

see here

tester.xlsx
	A	B	C	D	E	F	G	H	I	J
1	user id	date	day of week	action	action type	RO	Vendor	timing	date
2	mroche	11/1/23 11:49 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/1/23	5
3	mroche	11/1/23 5:06 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/1/23	0
4	mroche	11/8/23 4:14 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/8/23	5
5	mroche	11/9/23 11:11 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/9/23	5
6	mroche	11/1/23 11:49 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	463075	a	5	11/1/23	0
7	mroche	11/1/23 5:06 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	463075	a	5	11/1/23	0
8	mroche	11/9/23 11:11 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	463075	a	5	11/9/23	0
9	delaneyp	10/30/23 6:56 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	454907	a	5	10/30/23	5
10	delaneyp	11/30/23 4:26 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	464807	a	5	11/30/23	5
11	delaneyp	10/30/23 6:02 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456595	a	5	10/30/23	0
12	delaneyp	10/30/23 6:02 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	457613	a	5	10/30/23	0
13	delaneyp	10/30/23 6:02 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	458333	a	5	10/30/23	0
14	delaneyp	11/2/23 5:28 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	458333	a	5	11/2/23	5
15	delaneyp	11/3/23 9:59 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	458333	a	5	11/3/23	5
16	mroche	11/3/23 11:46 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	458349	a	5	11/3/23	5
17	mroche	11/3/23 11:46 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	458350	a	5	11/3/23	0
18	delaneyp	11/9/23 11:59 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	455384	a	5	11/9/23	5
19	delaneyp	12/7/23 5:53 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465067	a	5	12/7/23	5
20	delaneyp	12/12/23 6:23 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465067	a	5	12/12/23	5
21	delaneyp	10/30/23 6:05 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	a	5	10/30/23	0
22	delaneyp	11/3/23 9:38 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	a	5	11/3/23	0
data

Cell Formulas
Range	Formula
H2:H22	H2	=VLOOKUP(E2,keysheet!B:C,2,FALSE)
I2:I22	I2	=INT(B2)
J2:J22	J2	=IF(COUNTIFS($A$2:$A2,A2,$E$2:E2,E2,$I$2:I2,I2,$G$2:G2,G2)=1,5,0)

 

[Tonyk1051]

works for me, using formula as posted earlier
BUT the dates have times and so are ALL different
If you want to compare just the date without the time , then i have set a different column using INT()

see here

tester.xlsx
	A	B	C	D	E	F	G	H	I	J
1	user id	date	day of week	action	action type	RO	Vendor	timing	date
2	mroche	11/1/23 11:49 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/1/23	5
3	mroche	11/1/23 5:06 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/1/23	0
4	mroche	11/8/23 4:14 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/8/23	5
5	mroche	11/9/23 11:11 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462567	a	5	11/9/23	5
6	mroche	11/1/23 11:49 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	463075	a	5	11/1/23	0
7	mroche	11/1/23 5:06 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	463075	a	5	11/1/23	0
8	mroche	11/9/23 11:11 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	463075	a	5	11/9/23	0
9	delaneyp	10/30/23 6:56 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	454907	a	5	10/30/23	5
10	delaneyp	11/30/23 4:26 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	464807	a	5	11/30/23	5
11	delaneyp	10/30/23 6:02 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456595	a	5	10/30/23	0
12	delaneyp	10/30/23 6:02 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	457613	a	5	10/30/23	0
13	delaneyp	10/30/23 6:02 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	458333	a	5	10/30/23	0
14	delaneyp	11/2/23 5:28 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	458333	a	5	11/2/23	5
15	delaneyp	11/3/23 9:59 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	458333	a	5	11/3/23	5
16	mroche	11/3/23 11:46 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	458349	a	5	11/3/23	5
17	mroche	11/3/23 11:46 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	458350	a	5	11/3/23	0
18	delaneyp	11/9/23 11:59 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	455384	a	5	11/9/23	5
19	delaneyp	12/7/23 5:53 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465067	a	5	12/7/23	5
20	delaneyp	12/12/23 6:23 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465067	a	5	12/12/23	5
21	delaneyp	10/30/23 6:05 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	a	5	10/30/23	0
22	delaneyp	11/3/23 9:38 AM	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	a	5	11/3/23	0
data

Cell Formulas
Range	Formula
H2:H22	H2	=VLOOKUP(E2,keysheet!B:C,2,FALSE)
I2:I22	I2	=INT(B2)
J2:J22	J2	=IF(COUNTIFS($A$2:$A2,A2,$E$2:E2,E2,$I$2:I2,I2,$G$2:G2,G2)=1,5,0)

Yes the time stamp has to be kept

 

[etaf]

Yes the time stamp has to be kept

best just add the helper column

 

[Tonyk1051]

i had trouble downloading the file - so had to copy and change the dates to UK format
anyway
does
=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$E$2:E2,E2,$G$2:G2,G2)=1,5,0)
work

Book3
	A	B	C	D	E	F	G	H	I	J
1	user id	date	day of week	action	action type	RO	Vendor	timing
2	raymart	3/11/23 9:38	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	AIRSELFIE NA	5	5	5
3	raymart	3/11/23 9:38	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	AIRSELFIE NA	5	0	0
4	raymart	3/11/23 9:38	Friday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	AIRSELFIE NA	5	0	0
5	delaneyp	11/27/23 11:56 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	AIRSELFIE NA	5		5
6	delaneyp	5/12/23 17:44	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	AIRSELFIE NA	5		5
7	delaneyp	11/12/23 12:48	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	AIRSELFIE NA	5		5
8	delaneyp	12/14/23 5:23 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	456338	AIRSELFIE NA	5		5
9	delaneyp	12/12/23 18:32	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	466418	AKSCINE LLC	5		5
10	delaneyp	11/21/23 6:02 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	463328	ALL-WAYS SAFETY INC.	5		5
11	delaneyp	5/12/23 18:14	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465325	ALMO CORPORATION	5		5
12	delaneyp	12/12/23 18:44	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465325	ALMO CORPORATION	5		5
13	mroche	1/12/23 17:17	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465325	ALMO CORPORATION	5	5	5
14	mroche	1/12/23 17:17	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465716	ALMO CORPORATION	5	0	0
15	mroche	1/12/23 17:17	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465716	ALMO CORPORATION	5	0	0
16	mroche	1/12/23 17:17	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	465716	ALMO CORPORATION	5	0	0
17	mroche	1/12/23 17:17	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	469006	ALMO CORPORATION	5	0	0
18	mroche	12/20/23 6:35 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	469007	ALMO CORPORATION	5		5
19	delaneyp	12/21/23 10:38 AM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	469007	ALMO CORPORATION	5		5
20	delaneyp	10/30/23 6:07 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	455747	ALPHATHETA MUSIC AMERICA S INC	5		5
21	delaneyp	10/30/23 6:08 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	457258	ALPHATHETA MUSIC AMERICA S INC	5		5
22	delaneyp	9/11/23 12:24	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	462857	AUTEL ROBOTICS USA LLC	5		5
23	delaneyp	10/30/23 6:57 PM	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	460779	BOGEN COMMUNICATIONS LLC	5		5
24	delaneyp	9/11/23 13:47	weekday	ro message category	COLLECTIONS CONTACTED VENDOR	460779	BOGEN COMMUNICATIONS LLC	5		5
Sheet1

Cell Formulas
Range	Formula
J2:J24	J2	=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$E$2:E2,E2,$G$2:G2,G2)=1,5,0)

This formula works but there’s a little green triangle on the rope left corner of the cell and when I try to sort or filter the file by the zeros, workbook freezes. Granted it’s about 40k lines, is there anyway around that?

 

[etaf]

the green triangle is a message , if a different formula or something in the cell, not always an error - just a message - click on ot and it should tell you somethign

not sure why it would freeze - BUT all the formulas that are volatile - will recalcalculate the entire spreadsheet

The following Excel functions are volatile:

• NOW
• TODAY
• RANDBETWEEN
• OFFSET
• INDIRECT
• INFO (depending on its arguments)
• CELL (depending on its arguments)
• SUMIF (depending on its arguments)

BUT depending wht you have in another function may make it volatile - calculate every time the spreadsheet is updated in some way - like filter()

 

[Tonyk1051]

This formula works but there’s a little green triangle on the rope left corner of the cell and when I try to sort or filter the file by the zeros, workbook freezes. Granted it’s about 40k lines, is there anyway around that?

the green triangle is a message , if a different formula or something in the cell, not always an error - just a message - click on ot and it should tell you somethign

not sure why it would freeze - BUT all the formulas that are volatile - will recalcalculate the entire spreadsheet

The following Excel functions are volatile:

• NOW
• TODAY
• RANDBETWEEN
• OFFSET
• INDIRECT
• INFO (depending on its arguments)
• CELL (depending on its arguments)
• SUMIF (depending on its arguments)

BUT depending wht you have in another function may make it volatile - calculate every time the spreadsheet is updated in some way - like filter()

Hey do I have to make a new post cause I need some modifications to the formula….

 

[etaf]

Hey do I have to make a new post cause I need some modifications to the formula….

i would as may get more people looking at the thread , as this one is solved .