Help for Data Analyses

[Newbie73]

Hello,

I'm a bit of a newbie when it comes to Excel. In this last month I've tried to work on some data for a study that we're doing at my work.

The data is related to tests that certain individuals are required to do each x amount of times. First colum is the individual ID and Third colum the date of the tests.

14/11/2023 is the latest test, which the individual 3623, 3625 and 3626 have, meaning that all their data is still relevant.

3624 however doesn't have the last test, so I would like to flag it automatically with a formula and ideally in a different colum, so that then I could sort all the individuals without the lastest test to the bottom of the spreadsheet keep the "active" individuals and all their test results (including previous tests) on the top.

After days of failling to have an automatic formula to do this (even with helper colums), here I am. My Data has more colums but irrelevant to the example. It's over 10k rows with individuals having a varying number of tests, but my main thing is to sort the active ones from inactives (everything that doesn't have a test on 14/11/2023 is consider inactive). I've done this manual in another spreadsheet by having another colum and filling with a colour all the entries of all the individuals no longer active, and then sorting by no fill colour first.

Link to this example of spreadsheet:

minisheet.xlsx

1drv.ms

Thank you so much in advance! Do let me know if you need any more detail or information, or if I wasn't clear enough.

3623	CLEAR	14/11/2023	SEVERE		TRUE
3623	CLEAR	11/07/2023	SEVERE		TRUE
3623	CLEAR	21/03/2023	SEVERE		TRUE
3623	CLEAR	06/12/2022	SEVERE		TRUE
3624	CLEAR	06/12/2022	SEVERE		FALSE
3625	CLEAR	14/11/2023	SEVERE		TRUE
3625	CLEAR	11/07/2023	SEVERE		TRUE
3625	CLEAR	21/03/2023	SEVERE		TRUE
3625	CLEAR	06/12/2022	SEVERE		TRUE
3626	CLEAR	14/11/2023	SEVERE		FALSE
3626	CLEAR	11/07/2023	SEVERE		FALSE
3626	CLEAR	21/03/2023	SEVERE		FALSE
3626	CLEAR	06/12/2022	SEVERE		FALSE

 

[KRice]

Welcome to the MrExcel board! Have a look at this and see if it does what you want:

MrExcel_20240203.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
3	3623	CLEAR	14/11/2023	SEVERE		TRUE		3623	CLEAR	14/11/2023	SEVERE	0	TRUE
4	3623	CLEAR	11/7/2023	SEVERE		TRUE		3623	CLEAR	6/10/2023	SEVERE	0	TRUE
5	3623	CLEAR	21/3/2023	SEVERE		TRUE		3623	CLEAR	11/7/2023	SEVERE	0	TRUE
6	3623	CLEAR	6/12/2022	SEVERE		TRUE		3623	CLEAR	21/3/2023	SEVERE	0	TRUE
7	3624	CLEAR	6/12/2022	SEVERE		FALSE		3623	CLEAR	6/12/2022	SEVERE	0	TRUE
8	3625	CLEAR	14/11/2023	SEVERE		TRUE		3625	CLEAR	14/11/2023	SEVERE	0	TRUE
9	3625	CLEAR	11/7/2023	SEVERE		TRUE		3625	CLEAR	11/7/2023	SEVERE	0	TRUE
10	3625	CLEAR	21/3/2023	SEVERE		TRUE		3625	CLEAR	21/4/2023	SEVERE	0	TRUE
11	3625	CLEAR	6/12/2022	SEVERE		TRUE		3625	CLEAR	21/3/2023	SEVERE	0	TRUE
12	3626	CLEAR	14/11/2023	SEVERE		FALSE		3625	CLEAR	6/12/2022	SEVERE	0	TRUE
13	3626	CLEAR	11/7/2023	SEVERE		FALSE		3626	CLEAR	14/11/2023	SEVERE	0	FALSE
14	3626	CLEAR	21/3/2023	SEVERE		FALSE		3626	CLEAR	11/7/2023	SEVERE	0	FALSE
15	3626	CLEAR	6/12/2022	SEVERE		FALSE		3626	CLEAR	21/3/2023	SEVERE	0	FALSE
16								3626	CLEAR	6/12/2022	SEVERE	0	FALSE
17	3623	CLEAR	6/10/2023	SEVERE		TRUE
18	3628	CLEAR	8/8/2023	SEVERE		FALSE		3624	CLEAR	6/12/2022	SEVERE	0	FALSE
19								3628	CLEAR	8/8/2023	SEVERE	0	FALSE
20	3625	CLEAR	21/4/2023	SEVERE		TRUE
Sheet4

Cell Formulas
Range	Formula
J3:O19	J3	=LET(src,C3:H20,fsrc,FILTER(src,INDEX(src,,1)<>""),maxdate,MAX(INDEX(fsrc,,3)),uidmd,SORT(UNIQUE(FILTER(INDEX(fsrc,,1),INDEX(fsrc,,3)=maxdate))),uidnmd,SORT(UNIQUE(VSTACK(INDEX(fsrc,,1),uidmd),,1)),utd,FILTER(fsrc,ISNUMBER(MATCH(INDEX(fsrc,,1),uidmd,0))),nutd,FILTER(fsrc,NOT(ISNUMBER(MATCH(INDEX(fsrc,,1),uidmd,0)))),VSTACK(SORT(utd,{1,3},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,3},{1,-1}))))
Dynamic array formulas.

 

[etaf]

why 14/11/23 - will that change

how about
=(COUNTIFS($A$2:$A$14,A2,$C$2:$C$14,$H$2)=1)
where H2 is the date

Book2
	A	B	C	D	E	F	G	H	I
1
2	3623	CLEAR	11/14/23	SEVERE		TRUE		11/14/23	TRUE
3	3623	CLEAR	7/11/23	SEVERE		TRUE			TRUE
4	3623	CLEAR	3/21/23	SEVERE		TRUE			TRUE
5	3623	CLEAR	12/6/22	SEVERE		TRUE			TRUE
6	3624	CLEAR	12/6/22	SEVERE		FALSE			FALSE
7	3625	CLEAR	11/14/23	SEVERE		TRUE			TRUE
8	3625	CLEAR	7/11/23	SEVERE		TRUE			TRUE
9	3625	CLEAR	3/21/23	SEVERE		TRUE			TRUE
10	3625	CLEAR	12/6/22	SEVERE		TRUE			TRUE
11	3626	CLEAR	11/14/23	SEVERE		FALSE			TRUE
12	3626	CLEAR	7/11/23	SEVERE		FALSE			TRUE
13	3626	CLEAR	3/21/23	SEVERE		FALSE			TRUE
14	3626	CLEAR	12/6/22	SEVERE		FALSE			TRUE
15									FALSE
Sheet1

Cell Formulas
Range	Formula
I2:I15	I2	=(COUNTIFS($A$2:$A$14,A2,$C$2:$C$14,$H$2)=1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E14	Expression	=$B2="R"	text	NO
A2:E14	Expression	=$B2="IR"	text	NO
A2:E14	Expression	=COUNTIF(#REF!, #REF!)>0	text	NO
A2:E14	Expression	=$F2	text	NO

 

[Newbie73]

Thank you for the replies!

Krice, your answer blowed my mind, and despite working on my example it didn't work on the main spreadsheet. Also perhaps if it was possible that I could edit small portions of the data plus, as etaf mentioned, it's likelly that more tests will be done and more data will be added, making other individuals inactive, don;t know if that's possible with your formula? I've created a more complete example from the main spreadsheet.

Etaf, I'm using a similar conditional format to let me know when it's a new individual as you will see in the new example, simply to make it easy to know while scrolling through the data when it's still one individual or a different one. Thing is with your format I don't think I would be able to sort it and "throw" the inactive individuals to the bottom?

The ideal is to scroll through the date and see case by case, removing the inactive individuals would make the process less paniful (as it would remove thousands of entries from the middle of the data, straight to the bottom)

Link:

test2.xlsx

1drv.ms

Thanks so far, really appreciate it!!

 

[KRice]

I'm curious about why the formula did not work with your real-life worksheet. The only "hooks" into the actual data are here, in this part of the expression: src,C3:H20. Did you change the range reference to match your actual data?...and are your actual data in the same order as shown, with the 1st and 3rd columns of the range reference containing the user ID and Dates, respectively?

 

[Newbie73]

I'm curious about why the formula did not work with your real-life worksheet. The only "hooks" into the actual data are here, in this part of the expression: src,C3:H20. Did you change the range reference to match your actual data?...and are your actual data in the same order as shown, with the 1st and 3rd columns of the range reference containing the user ID and Dates, respectively?

Thanks for the reply again KRice, I've played a bit more with your formula and figured out why it wasn't working. Due to the ID Have numbers, a few digits and only the 4 for digits in order of sequence to sort them (for example UDH823494431 and UDH823494432 I had on A2 the formula =RIGHT(B2,4). Changing your formula to ignore the first colum worked, but I would still need to order them by the last four digits of the ID and date newest to oldest to analyse and see patterns, which I don't seem to be able to do in the array :/ or am I missing something? Sorry about this, doing my best to learn! My second link has a better example of the real spreadsheet, include formulas and conditional formatting using on the real one

 

[Newbie73]

Thanks for the reply again KRice, I've played a bit more with your formula and figured out why it wasn't working. Due to the ID Have numbers, a few digits and only the 4 for digits in order of sequence to sort them (for example UDH823494431 and UDH823494432 I had on A2 the formula =RIGHT(B2,4). Changing your formula to ignore the first colum worked, but I would still need to order them by the last four digits of the ID and date newest to oldest to analyse and see patterns, which I don't seem to be able to do in the array :/ or am I missing something? Sorry about this, doing my best to learn! My second link has a better example of the real spreadsheet, include formulas and conditional formatting using on the real one

UDH823494431 and NL8535604432

Correction in the example, meant to change the other numbers and maintaining an order in the last 4

 

[KRice]

I don't see any issues adapting the formula to your worksheet. To make things clearer, I've introduced two more variables. The full data range is specified as "src". Then we filter "src" to eliminate any rows where blanks appear in the 1st column, and we call that consolidated array "fsrc" (for filtered source). Next, we declare which columns in "fsrc" hold the "shortID" and the "dates"...in your real-life version, those are columns 1 and 12. Next we determine what the maximum (most recent) date is, and call it "maxdate". Then we determine a unique list of user ID's with an entry whose date is the maxdate (that list is found in the array named "uidmd"...user id with max date). We do something similar and find the list of user IDs that do not have an entry with the max date, and that list is found in the array "uidnmd" (user ID Not max date). Then to construct the upper part of the results table, we filter the full filtered source data "fsrc" by including only those rows where the shortID (the 4-digit code) appears in the list of "uidmd" (meaning one of that person's entries has the max date)...and that array is called "utd". A similar list is created for "fsrc" rows whose shortIDs are not associated with a max date...and that array is called "nutd". Finally, we sort both of these arrays (utd and nutd) and sort by the shortID and the data, ascending order for shortID followed by descending data order, and these are stacked together, separated by a blank row.

MrExcel_20240203.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3	3623	KK3623								3623	CLEAR	14/11/2023	SEVERE		TRUE			3623	KK3623	0	0	0	0	0	0	0	3623	CLEAR	14/11/2023	SEVERE	0	TRUE
4	3623	HH3623								3623	CLEAR	11/7/2023	SEVERE		TRUE			3623	YY3623	0	0	0	0	0	0	0	3623	CLEAR	6/10/2023	SEVERE	0	TRUE
5	3623	U3623								3623	CLEAR	21/3/2023	SEVERE		TRUE			3623	HH3623	0	0	0	0	0	0	0	3623	CLEAR	11/7/2023	SEVERE	0	TRUE
6	3623	CCC3623								3623	CLEAR	6/12/2022	SEVERE		TRUE			3623	U3623	0	0	0	0	0	0	0	3623	CLEAR	21/3/2023	SEVERE	0	TRUE
7	3624	EE3624								3624	CLEAR	6/12/2022	SEVERE		FALSE			3623	CCC3623	0	0	0	0	0	0	0	3623	CLEAR	6/12/2022	SEVERE	0	TRUE
8	3625	WWW3625								3625	CLEAR	14/11/2023	SEVERE		TRUE			3625	WWW3625	0	0	0	0	0	0	0	3625	CLEAR	14/11/2023	SEVERE	0	TRUE
9	3625	OOO3625								3625	CLEAR	11/7/2023	SEVERE		TRUE			3625	OOO3625	0	0	0	0	0	0	0	3625	CLEAR	11/7/2023	SEVERE	0	TRUE
10	3625	GG3625								3625	CLEAR	21/3/2023	SEVERE		TRUE			3625	EE3625	0	0	0	0	0	0	0	3625	CLEAR	21/4/2023	SEVERE	0	TRUE
11	3625	GG3625								3625	CLEAR	6/12/2022	SEVERE		TRUE			3625	GG3625	0	0	0	0	0	0	0	3625	CLEAR	21/3/2023	SEVERE	0	TRUE
12	3626	PP3626								3626	CLEAR	14/11/2023	SEVERE		FALSE			3625	GG3625	0	0	0	0	0	0	0	3625	CLEAR	6/12/2022	SEVERE	0	TRUE
13	3626	BBB3626								3626	CLEAR	11/7/2023	SEVERE		FALSE			3626	PP3626	0	0	0	0	0	0	0	3626	CLEAR	14/11/2023	SEVERE	0	FALSE
14	3626	JJJ3626								3626	CLEAR	21/3/2023	SEVERE		FALSE			3626	BBB3626	0	0	0	0	0	0	0	3626	CLEAR	11/7/2023	SEVERE	0	FALSE
15	3626	D3626								3626	CLEAR	6/12/2022	SEVERE		FALSE			3626	JJJ3626	0	0	0	0	0	0	0	3626	CLEAR	21/3/2023	SEVERE	0	FALSE
16																		3626	D3626	0	0	0	0	0	0	0	3626	CLEAR	6/12/2022	SEVERE	0	FALSE
17	3623	YY3623								3623	CLEAR	6/10/2023	SEVERE		TRUE
18	3628	II3628								3628	CLEAR	8/8/2023	SEVERE		FALSE			3624	EE3624	0	0	0	0	0	0	0	3624	CLEAR	6/12/2022	SEVERE	0	FALSE
19																		3628	II3628	0	0	0	0	0	0	0	3628	CLEAR	8/8/2023	SEVERE	0	FALSE
20	3625	EE3625								3625	CLEAR	21/4/2023	SEVERE		TRUE
Sheet4

Cell Formulas
Range	Formula
R3:AF19	R3	=LET(src,A3:O20, fsrc,FILTER(src,INDEX(src,,1)<>""), shortID,INDEX(fsrc,,1), dates,INDEX(fsrc,,12), maxdate,MAX(dates), uidmd,SORT(UNIQUE(FILTER(shortID,dates=maxdate))), uidnmd,SORT(UNIQUE(VSTACK(shortID,uidmd),,1)), utd,FILTER(fsrc,ISNUMBER(MATCH(shortID,uidmd,0))), nutd,FILTER(fsrc,NOT(ISNUMBER(MATCH(shortID,uidmd,0)))), VSTACK(SORT(utd,{1,12},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,12},{1,-1}))))
A3:A15,A20,A17:A18	A3	=RIGHT(B3,4)
Dynamic array formulas.

 

[Newbie73]

I don't see any issues adapting the formula to your worksheet. To make things clearer, I've introduced two more variables. The full data range is specified as "src". Then we filter "src" to eliminate any rows where blanks appear in the 1st column, and we call that consolidated array "fsrc" (for filtered source). Next, we declare which columns in "fsrc" hold the "shortID" and the "dates"...in your real-life version, those are columns 1 and 12. Next we determine what the maximum (most recent) date is, and call it "maxdate". Then we determine a unique list of user ID's with an entry whose date is the maxdate (that list is found in the array named "uidmd"...user id with max date). We do something similar and find the list of user IDs that do not have an entry with the max date, and that list is found in the array "uidnmd" (user ID Not max date). Then to construct the upper part of the results table, we filter the full filtered source data "fsrc" by including only those rows where the shortID (the 4-digit code) appears in the list of "uidmd" (meaning one of that person's entries has the max date)...and that array is called "utd". A similar list is created for "fsrc" rows whose shortIDs are not associated with a max date...and that array is called "nutd". Finally, we sort both of these arrays (utd and nutd) and sort by the shortID and the data, ascending order for shortID followed by descending data order, and these are stacked together, separated by a blank row.

MrExcel_20240203.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3	3623	KK3623								3623	CLEAR	14/11/2023	SEVERE		TRUE			3623	KK3623	0	0	0	0	0	0	0	3623	CLEAR	14/11/2023	SEVERE	0	TRUE
4	3623	HH3623								3623	CLEAR	11/7/2023	SEVERE		TRUE			3623	YY3623	0	0	0	0	0	0	0	3623	CLEAR	6/10/2023	SEVERE	0	TRUE
5	3623	U3623								3623	CLEAR	21/3/2023	SEVERE		TRUE			3623	HH3623	0	0	0	0	0	0	0	3623	CLEAR	11/7/2023	SEVERE	0	TRUE
6	3623	CCC3623								3623	CLEAR	6/12/2022	SEVERE		TRUE			3623	U3623	0	0	0	0	0	0	0	3623	CLEAR	21/3/2023	SEVERE	0	TRUE
7	3624	EE3624								3624	CLEAR	6/12/2022	SEVERE		FALSE			3623	CCC3623	0	0	0	0	0	0	0	3623	CLEAR	6/12/2022	SEVERE	0	TRUE
8	3625	WWW3625								3625	CLEAR	14/11/2023	SEVERE		TRUE			3625	WWW3625	0	0	0	0	0	0	0	3625	CLEAR	14/11/2023	SEVERE	0	TRUE
9	3625	OOO3625								3625	CLEAR	11/7/2023	SEVERE		TRUE			3625	OOO3625	0	0	0	0	0	0	0	3625	CLEAR	11/7/2023	SEVERE	0	TRUE
10	3625	GG3625								3625	CLEAR	21/3/2023	SEVERE		TRUE			3625	EE3625	0	0	0	0	0	0	0	3625	CLEAR	21/4/2023	SEVERE	0	TRUE
11	3625	GG3625								3625	CLEAR	6/12/2022	SEVERE		TRUE			3625	GG3625	0	0	0	0	0	0	0	3625	CLEAR	21/3/2023	SEVERE	0	TRUE
12	3626	PP3626								3626	CLEAR	14/11/2023	SEVERE		FALSE			3625	GG3625	0	0	0	0	0	0	0	3625	CLEAR	6/12/2022	SEVERE	0	TRUE
13	3626	BBB3626								3626	CLEAR	11/7/2023	SEVERE		FALSE			3626	PP3626	0	0	0	0	0	0	0	3626	CLEAR	14/11/2023	SEVERE	0	FALSE
14	3626	JJJ3626								3626	CLEAR	21/3/2023	SEVERE		FALSE			3626	BBB3626	0	0	0	0	0	0	0	3626	CLEAR	11/7/2023	SEVERE	0	FALSE
15	3626	D3626								3626	CLEAR	6/12/2022	SEVERE		FALSE			3626	JJJ3626	0	0	0	0	0	0	0	3626	CLEAR	21/3/2023	SEVERE	0	FALSE
16																		3626	D3626	0	0	0	0	0	0	0	3626	CLEAR	6/12/2022	SEVERE	0	FALSE
17	3623	YY3623								3623	CLEAR	6/10/2023	SEVERE		TRUE
18	3628	II3628								3628	CLEAR	8/8/2023	SEVERE		FALSE			3624	EE3624	0	0	0	0	0	0	0	3624	CLEAR	6/12/2022	SEVERE	0	FALSE
19																		3628	II3628	0	0	0	0	0	0	0	3628	CLEAR	8/8/2023	SEVERE	0	FALSE
20	3625	EE3625								3625	CLEAR	21/4/2023	SEVERE		TRUE
Sheet4

Cell Formulas
Range	Formula
R3:AF19	R3	=LET(src,A3:O20, fsrc,FILTER(src,INDEX(src,,1)<>""), shortID,INDEX(fsrc,,1), dates,INDEX(fsrc,,12), maxdate,MAX(dates), uidmd,SORT(UNIQUE(FILTER(shortID,dates=maxdate))), uidnmd,SORT(UNIQUE(VSTACK(shortID,uidmd),,1)), utd,FILTER(fsrc,ISNUMBER(MATCH(shortID,uidmd,0))), nutd,FILTER(fsrc,NOT(ISNUMBER(MATCH(shortID,uidmd,0)))), VSTACK(SORT(utd,{1,12},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,12},{1,-1}))))
A3:A15,A20,A17:A18	A3	=RIGHT(B3,4)
Dynamic array formulas.

Thanks for the explanation and that did the trick!! There's only one little detail that perhaps you help with. Due to being a big data base and having to analyse manually, it's handy to have a limitations separating between different individuals to know visusally when it's a different one. I was using =IF(B2=B1,O1,NOT(O1)) with FALSE on O1, this would give me a colum of false and true changing whenever was a different individual and I had it highlithed through conditional formatting like on my second link. Once I apply your formula down until O9630 that is lost, and I have many TRUE or FALSES straight after another even if it's already a different individual. Or maybe there is a easier way to implement that?

As in:

2233 TRUE
2233 TRUE
2233 TRUE
3579 TRUE
3579 TRUE
3579 TRUE
4389 TRUE

Instead of:

2233 TRUE
2233 TRUE
2233 TRUE
3579 FALSE
3579 FALSE
3579 FALSE
4389 TRUE
4389 TRUE

And if I may ask, only now I've started reading about uidmd and src and what's the easiest way to generate it? Just so that I can maintain that formula for future tests (as in MAXDATE changes)

Thanks a lot KRice, this is already a big help considering I've sorted the last 10k of data manually on this subject

 

[Newbie73]

Never mind my previous question! Adding that formula on a new colum will still read the values from the array table. Sorry about the stupid question!! If only you could tell me now how did you get/generate those src and uidmd so that I could maintain the formula in the future with more data and different dates. You're a Legend!!