Macro for retaining one row of data per person

[Gollum9]

Hi all,

I have a large data set of training information, where there are individual rows per completion. The issue I have is that there can be multiple rows per person as they may have attempted the training before.

What I'm after if possible is some code that will only retain one row of information per person, based on the following rules. The data is in the format of Unique Person ID in column W, the date in column B, and the training status in column T (either passed or failed).

1) Retain only the most recent row for the person that has 'training status' = passed
2) If the person does not have a row with 'training status' = passed, delete all rows other than the most recent row with 'training status' = failed

Thanks for any advice you can provide.

 

[ranman256]

sounds like you need to use a Microsoft Access database.

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Gollum9]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Good thinking, all done now - thanks!

 

[Joe4]

Thanks.

Are you able to post some sample data, so we can see the full size of it? If not, can you at least let us know where exactly this data exists (row and column addresses)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

One other question - is it possible that a user would ever have a "passed" and "failed" record with the same date for each?

 

[Gollum9]

One other question - is it possible that a user would ever have a "passed" and "failed" record with the same date for each?

I'm just trying to get you a sample of the data with nothing secure in there now.

And yes, it's possible they could have a passed and failed entry on the same say (although unlikely)

 

[Gollum9]

I'm just trying to get you a sample of the data with nothing secure in there now.

And yes, it's possible they could have a passed and failed entry on the same say (although unlikely)

consu2096r1 (8).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	eLearning
3	Record ID	Date	Score	Title	First name	Last name	Emp Type	Location	Manager ID	D1	D2	Area	Team	Level	Mgr	Mgr First Name	Mgr Last Name	Mgr E-mail	Role	Status	Offline Date	Offline User	Unique Emp ID
4	1	26-06-2023	100																	Passed			5440505
5	2	26-06-2023	100																	Passed			2935
6	3	26-06-2023	100																	Passed			2935
7	4	26-06-2023	100																	Passed			6306
8	5	26-06-2023	100																	Passed			46525
9	6	26-06-2023	0																	Failed			8403
10	7	26-06-2023	0																	Failed			43003
11	8	26-06-2023	100																	Passed			5317098
12	9	26-06-2023	0																	Failed			18309
13	10	26-06-2023	0																	Failed			89786
14	11	26-06-2023	100																	Passed			5074014
15	12	26-06-2023	100																	Passed			1457
16	13	26-06-2023	0																	Failed			10816
17	14	26-06-2023	0																	Failed			498
18	15	25-06-2023	0																	Failed			5440505
19	16	25-06-2023	100																	Passed			94904
20	17	25-06-2023	100																	Passed			20522
21	18	25-06-2023	100																	Passed			74576
22	19	25-06-2023	100																	Passed			96171
23	20	25-06-2023	100																	Passed			77288
24	21	25-06-2023	100																	Passed			65425
25	22	25-06-2023	0																	Failed			5449003
26	23	25-06-2023	0																	Failed			18048
27	24	25-06-2023	100																	Passed			5205524
28	25	24-06-2023	0																	Failed			5421184
29	26	24-06-2023	100																	Passed			65684
30	27	24-06-2023	0																	Failed			5150329
31	28	24-06-2023	100																	Passed			9896
32	29	24-06-2023	100																	Passed			5368557
33	30	24-06-2023	0																	Failed			97824
34	31	24-06-2023	100																	Passed			80956
35	32	24-06-2023	0																	Failed			96796
36	33	24-06-2023	100																	Passed			8009
37	34	24-06-2023	100																	Passed			5131045
38	35	24-06-2023	0																	Failed			19547
39	36	24-06-2023	100																	Passed			5210245
40	37	24-06-2023	0																	Failed			11064
41	38	24-06-2023	100																	Passed			5421641
42	39	23-06-2023	0																	Failed			46341
43	40	23-06-2023	100																	Passed			16588
44	41	23-06-2023	100																	Passed			5367543
45	42	23-06-2023	0																	Failed			89966
46	43	23-06-2023	0																	Failed			5380689
47	44	23-06-2023	29																	Failed			5456346
48	45	23-06-2023	100																	Passed			5138112
49	46	23-06-2023	100																	Passed			5455097
50	47	23-06-2023	100																	Passed			5437543
51	48	23-06-2023	0																	Failed			80724
52	49	23-06-2023	100																	Passed			5448990
53	50	23-06-2023	100																	Passed			5449096
54	51	23-06-2023	0																	Failed			75584
55	52	23-06-2023	100																	Passed			5435723
56	53	23-06-2023	0																	Failed			5346807
57	54	23-06-2023	0																	Failed			35690
58	55	22-06-2023	0																	Failed			3366
59	56	22-06-2023	100																	Passed			5440475
60	57	22-06-2023	0																	Failed			7406
61	58	22-06-2023	100																	Passed			212232
62	59	22-06-2023	0																	Failed			2701
63	60	22-06-2023	0																	Failed			5375616
64	61	22-06-2023	100																	Passed			5437567
65	62	22-06-2023	0																	Failed			73141
66	63	22-06-2023	100																	Passed			5433989
67	64	22-06-2023	100																	Passed			5448921
68	65	22-06-2023	0																	Failed			64310
69	66	22-06-2023	0																	Failed			94153
70	67	22-06-2023	100																	Passed			5471424
71	68	22-06-2023	100																	Passed			5447801
72	69	22-06-2023	100																	Passed			6961
73	70	22-06-2023	0																	Failed			73036
74	71	22-06-2023	0																	Failed			6324
75	72	21-06-2023	0																	Failed			5261056
76	73	21-06-2023	0																	Failed			35669
77	74	21-06-2023	0																	Failed			35669
78	75	21-06-2023	0																	Failed			5606
79	76	21-06-2023	0																	Failed			5170372
80	77	21-06-2023	0																	Failed			132436
81	78	21-06-2023	58																	Failed			68499
82	79	21-06-2023	0																	Failed			90431
83	80	21-06-2023	100																	Passed			5434917
84	81	21-06-2023	100																	Passed			5458623
85	82	21-06-2023	100																	Passed			5456049
86	83	21-06-2023	0																	Failed			5273431
87	84	21-06-2023	100																	Passed			5462682
88	85	21-06-2023	100																	Passed			4158
89	86	21-06-2023	0																	Failed			12863
90	87	21-06-2023	0																	Failed			6319
91	88	21-06-2023	100																	Passed			20579
92	89	20-06-2023	100																	Passed			6319
93	90	20-06-2023	100																	Passed			5313939
94	91	20-06-2023	0																	Failed			4981
95	92	20-06-2023	100																	Passed			86315
96	93	20-06-2023	100																	Passed			16895
97	94	20-06-2023	100																	Passed			22745
98	95	20-06-2023	0																	Failed			5457909
99	96	20-06-2023	0																	Failed			9923
100	97	20-06-2023	0																	Failed			97823
Report

 

[Gollum9]

Thanks for your help @Joe4. Hopefully this shows enough of the file for you. I could only add 100 rows so there may not be many duplicates to test, but I hope it gives an idea.

 

[Joe4]

OK. Thanks. Give me a little bit to work on it. I have some ideas, but they take a little work.