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

 

[Newbie73]

Thanks once again! Doing the trick, one last thing, is there a way that I could easily find that "blank cell" between the upper_md and lower_notmd? say that I have 30k of entries, would there be a way to find it quickly rather than scroll until finding it?

If you do come with a solution (as you somehow always do!) and it's based on a formula, could you had it to this one as well?

=LET(src,Data!A2:N30000, fsrc,FILTER(src,INDEX(src,,1)<>""), shortID,INDEX(fsrc,,1), dates,INDEX(fsrc,,13), 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,13},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,13},{1,-1}))))

No worries if not and than kyou for everything KRice!

 

[KRice]

You can easily find the blank row between sections. One way is to search for it using the MATCH function mentioned in my last post:

MATCH(TRUE,TAKE(Sheet5!R3#,,1)="",0)

where the results from the formula spill from Sheet5!R3# (adjust that reference to your worksheet). This "takes" the first column of the results and looks for the first instance of a blank. The MATCH function will return the index of that blank. By "index", I mean the function begins counting the first row of the results as 1, even though, in this case, the formula begins producing results at row 3. If you'd prefer the row number rather than the index, then:

=ROW(Sheet5!R3)+MATCH(TRUE,TAKE(Sheet5!R3#,,1)="",0)-1

so that we return the actual worksheet row number of the blank.

Or, if you want this delivered as part of your results table (I've placed it in the first row), you could do something like this, but you'll need to ensure that the width of the columns displaying the text are wide enough:

=LET(src,Data!A2:N30000, fsrc,FILTER(src,INDEX(src,,1)<>""), shortID,INDEX(fsrc,,1), dates,INDEX(fsrc,,13), maxdate,MAX(dates),
uidmd,SORT(UNIQUE(FILTER(shortID,dates=maxdate))), uidnmd,IFERROR(SORT(UNIQUE(VSTACK(shortID,uidmd),,1)),""),
utd,FILTER(fsrc,ISNUMBER(MATCH(shortID,uidmd,0))), nutd,IFERROR(UNIQUE(VSTACK(fsrc,utd),,1),EXPAND("",,COLUMNS(fsrc),"")),
upperhd, HSTACK("Max Date Block",EXPAND("",,COLUMNS(fsrc)-3,""),"lower block at row " & ROW()+ROWS(utd)+1,EXPAND("",,1,"")),
VSTACK(upperhd,SORT(utd,{1,13},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,13},{1,-1}))))

Also note that I've revised the definitions of uidnmd and nutd in this formula to wrap the original expressions with an IFERROR function, and then to return either a single "" or an array of ""'s for the scenario where there are no User ID's that are NOT associated with a maxdate. Without these revisions, the original formula will produce an error.
The end result looks like this:

MrExcel_20240203.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Max Date Block												lower block at row 19
4	3623	KK3623	0	0	0	0	0	0	0	3623	CLEAR	0	14/11/2023	SEVERE
5	3623	YY3623	0	0	0	0	0	0	0	3623	CLEAR	0	06/10/2023	SEVERE
6	3623	HH3623	0	0	0	0	0	0	0	3623	CLEAR	0	11/07/2023	SEVERE
7	3623	U3623	0	0	0	0	0	0	0	3623	CLEAR	0	21/03/2023	SEVERE
8	3623	CCC3623	0	0	0	0	0	0	0	3623	CLEAR	0	06/12/2022	SEVERE
9	3624	EE3624	0	0	0	0	0	0	0	3624	CLEAR	0	14/11/2023	SEVERE
10	3625	WWW3625	0	0	0	0	0	0	0	3625	CLEAR	0	14/11/2023	SEVERE
11	3625	OOO3625	0	0	0	0	0	0	0	3625	CLEAR	0	11/07/2023	SEVERE
12	3625	GG3625	0	0	0	0	0	0	0	3625	CLEAR	0	21/03/2023	SEVERE
13	3625	GG3625	0	0	0	0	0	0	0	3625	CLEAR	0	06/12/2022	SEVERE
14	3625	EE3625	0	0	0	0	0	0	0	3625	CLEAR	0	06/12/2022	SEVERE
15	3626	PP3626	0	0	0	0	0	0	0	3626	CLEAR	0	14/11/2023	SEVERE
16	3626	BBB3626	0	0	0	0	0	0	0	3626	CLEAR	0	11/07/2023	SEVERE
17	3626	JJJ3626	0	0	0	0	0	0	0	3626	CLEAR	0	21/03/2023	SEVERE
18	3626	D3626	0	0	0	0	0	0	0	3626	CLEAR	0	06/12/2022	SEVERE
19
20	3628	II3628	0	0	0	0	0	0	0	3628	CLEAR	0	06/12/2022	SEVERE
Sheet6

Cell Formulas
Range	Formula
B3:O20	B3	=LET(src,Data!A2:N30000, fsrc,FILTER(src,INDEX(src,,1)<>""), shortID,INDEX(fsrc,,1), dates,INDEX(fsrc,,13), maxdate,MAX(dates), uidmd,SORT(UNIQUE(FILTER(shortID,dates=maxdate))), uidnmd,IFERROR(SORT(UNIQUE(VSTACK(shortID,uidmd),,1)),""), utd,FILTER(fsrc,ISNUMBER(MATCH(shortID,uidmd,0))), nutd,IFERROR(UNIQUE(VSTACK(fsrc,utd),,1),EXPAND("",,COLUMNS(fsrc),"")), upperhd, HSTACK("Max Date Block",EXPAND("",,COLUMNS(fsrc)-3,""),"lower block at row " & ROW()+ROWS(utd)+1,EXPAND("",,1,"")), VSTACK(upperhd,SORT(utd,{1,13},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,13},{1,-1}))))
Dynamic array formulas.

 

[Newbie73]

Thank you!! I went down the row option and created a link to it, so that I can get to the blank cell straightaway. Really appreciate it!