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]

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)

 

[Tonyk1051]

No it doesn’t work as intended, some cells have a 5 where it should be 0 and all the criteria has been met…

 

[etaf]

can you show an example where that happens please

 

[Tonyk1051]

its the yellow

 

[etaf]

i cannot read that - sorry

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

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

 

[Tonyk1051]

i cannot read that - sorry

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

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

no problem, give me sometime as i just woke up, appreciate the help though

 

[etaf]

you are welcome, i'm in UK , so maybe a timezone difference , but will reply...

 

[Tonyk1051]

tester.xlsx

Shared with Dropbox

www.dropbox.com

editing

tester.xlsx

Shared with Dropbox

www.dropbox.com

viewing

try these links, if it works then you can use the file for both this issue and the advance formula issue

 

[etaf]

downloaded the file - not sure where i am supposed to be looking
Data Sheet ?
for some reasomn its not calculating , will have to look further into - works on a seperate spreadsheet OK