Help with listing please

[Noxqss38242]

Here is the data:

Inactive Trailers Report (TRLPOD2).xlsx
	A	B	C								K	L	M	N			Q	R	S
1210	IL-CHI	James Bond	IL								195922	9/11	1	1			AVAILABLE	3MDKLB	FALSE
1211	IL-CHI	James Bond	IL								634284	9/11	1	1			AVAILABLE	3MDKLB	FALSE
1212	IL-CHI	James Bond	IL								175228	8/21	22	22			O/S ON THE	ABDP	FALSE
1213	IL-CHI	James Bond	IL								237337	8/30	13	13			AVAILABLE	ABDP	FALSE
1214	IL-CHI	James Bond	IL								634381	8/25	18	18			AVAILABLE	CEDLW	FALSE
1215	IL-CHI	James Bond	IL								634563	8/31	12	12			AVAILABLE	CEDLW	FALSE
1216	IL-CHI	James Bond	IL								634528	9/01	11	11			AVAILABLE	CEDLW	FALSE
1217	IL-CHI	James Bond	IL								195853	8/21	22	22			AVAILABLE	CHWLIL	FALSE
1218	IL-CHI	James Bond	IL								175369	9/05	7	7			O/S ON THE	CHWLIL	FALSE
1219	IL-CHI	James Bond	IL								175362	9/08	4	4			AVAILABLE	DTJO	FALSE
1220	IL-CHI	James Bond	IL								185431	9/08	4	4			DISPATCHED	20669	TRUE
1221	IL-CHI	James Bond	IL								635064	9/08	4	4			AVAILABLE	DTJO	FALSE
1222	IL-CHI	James Bond	IL								634470	9/12	0	0			AVAILABLE	DTJO	FALSE
Inactive Trailers Report (TRLPO

Cell Formulas
Range	Formula
Q1210:Q1222	Q1210	=VLOOKUP(K1210,Sheet1!$A:$I,2,0)
R1210:R1222	R1210	=VLOOKUP(K1210,Sheet1!$A:$I,9,0)
S1210:S1222	S1210	=ISNUMBER(R1210)

Here is a sample of a past report:

Inactive Trailers Report (TRLPOD2).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Inactive Trailer Report for:	James Bond			9/13/2023
2
3
4	# Trailers over 10 days (10-20)		27
5
6	# Trailers over 21 days (21+)		5
7
8	Total:		32
9
10
11	Empty Trailers over 20 days:	Customer/Shop	City/State	Loc. Code	Reason for Idle		Loaded Trailers over 20 days:
12	633854	Shop	CR IA	STTRCR	Still in shop as shop is backed logged		Trailer#	Where (customer)	City/State	Customer Code	Explaination from customer
13	175126	Shop	CALWI	MFMILW	In shop		216771	Customer	ROMMI	RETROM	still loaded.
14	634219	Customer	CINOH	PCCIOH	Tmove set to drop in Indy for retirment
15	634577	Shop	LIMOH	KNLLIM	In shop
16
Sheet2

Cell Formulas
Range	Formula
E1	E1	=TODAY()
C8	C8	=SUM(C4,C6)

Here is what I want to accomplish:

I want cells A12:A30 to have a formula that automatically pulls the "trailer numbers" located on the Inactive tab in column K that matches the criteria: S = False, M =>20, B="James Bond", Q = (everything except "LOADED")
I want cells G13:G30 to have a formula that automatically pulls the "trailer numbers" located on the Inactive tab in column K that matches the same criteria as before but Q = "LOADED"

From there I can just use VLOOKUP for the rest of the info I need.


Any help would be much appreciated.

 

[RobP]

Hi,

looking at your data, it would appear that you have over 1000 trailers , right (as you are listing them in rows 1200+).
Can I ask, why would you only expect to have a maximum of 18 trailers inactive at any time ? (eg. Row30 - Row 12 = 18 rows only) ?
Also, are you still on Excel version 2016, or did you upgrade at all (s more formulas available in newer versions for people to try and use...)

thanks
Rob

 

[Noxqss38242]

Hi,

looking at your data, it would appear that you have over 1000 trailers , right (as you are listing them in rows 1200+).
Can I ask, why would you only expect to have a maximum of 18 trailers inactive at any time ? (eg. Row30 - Row 12 = 18 rows only) ?
Also, are you still on Excel version 2016, or did you upgrade at all (s more formulas available in newer versions for people to try and use...)

thanks
Rob

Good questions.
Yes, still using version 2016
As for trailers, there are many different "planners" name was changed to "James Bond"
So each planner may only be responsible at any given time to X amount of trailers.
That and the trailers in question are ones that are "sitting" too long and not being utilized. (dead weight)
So the goal of it is trying to assign the trailers sitting too long as when they tend to sit, they get stolen, damaged, vandalized, etc...

So there is a warning period of 10 to 20 days on just the "count" which I can do with a simple formula

Then there is the problem trailers of ones that have been idle for 21+ days which are the only ones needing listed.

Once I have a working formula I will create each tab for each individual planner which I can easily alter the formula for.

 

[Noxqss38242]

I added a row column and came up with this formula so far...but it still isn't working:

{=INDEX('Inactive Trailers'!$B$2:$T$2595,SMALL(IF(COUNTIF('Inactive Trailers'!$N$2:$N$2595,">20")*COUNTIF('Inactive Trailers'!$C$2:$C$2595,"James Bond"),MATCH(ROW('Inactive Trailers'!$C$2:$C$2595),ROW('Inactive Trailers'!$C$2:$C$2595)),""),ROWS('Inactive Trailers'!$A$1:A1)),COLUMNS('Inactive Trailers'!$A$1:A1))}

It shows everything instead of only ">20" and "James Bond".....still haven't figured out the "loaded" only column either....

 

[RobP]

** edited - sorry, I see "SORT" is from Excel 2021.... *** need to keep trying as this wont work for you apologies

Hi,
so this isn't quite perfect - and I'm using O365 - so I'm hoping you can put this formula in as an array formula between {} in your 2016 version.

I've made my example on the same sheet, just for ease of solving really, but I'm sure you can amend to your own data on other sheets etc. as you seem to know what you're doing.

The SORT function drawback is that the array size is still huge even when blank, so will spill down your sheet massively with your number of rows..)

I hope you can follow what I've done with some of the other "array" formulas I've left in as I was building it, so you can see how it was built. Let me know how it goes..

I put my ref. points (name, no of days, true/false across in Col W.)

cheers
Rob

inactive_trailers.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
1	IL-CHI	James Bond	IL								195922	09-Nov	1	1			AVAILABLE	3MDKLB	TRUE				James Bond
2	IL-CHI	James Bond	IL								634284	09-Nov	1	1			AVAILABLE	3MDKLB	FALSE				20
3	IL-CHI	James Bond	IL								175228	Aug-21	22	22			O/S ON THE	ABDP	FALSE				FALSE
4	IL-CHI	James Bond	IL								237337	Aug-30	13	13			AVAILABLE	ABDP	FALSE
5	IL-CHI	MoneyPenny	IL								634381	Aug-25	18	18			AVAILABLE	CEDLW	FALSE
6	IL-CHI	James Bond	IL								634563	Aug-31	12	12			AVAILABLE	CEDLW	FALSE
7	IL-CHI	James Bond	IL								634528	09-Jan	23	23			AVAILABLE	CEDLW	FALSE
8	IL-CHI	James Bond	IL								195853	Aug-21	22	22			AVAILABLE	CHWLIL	FALSE
9	IL-CHI	James Bond	IL								175369	09-May	7	7			O/S ON THE	CHWLIL	FALSE
10	IL-CHI	James Bond	IL								175362	09-Aug	4	4			AVAILABLE	DTJO	FALSE
11	IL-CHI	MoneyPenny	IL								185431	09-Aug	25	25			DISPATCHED	20669	TRUE
12	IL-CHI	James Bond	IL								635064	09-Aug	4	4			AVAILABLE	DTJO	FALSE
13	IL-CHI	James Bond	IL								634470	09-Dec	23	23			LOADED	DTJO	FALSE
14
15
16
17
18
19
20
21
22	name	true/false	days	all + <>LOADED			Result
23	1	0	0	0			175228
24	1	1	0	0			195853
25	1	1	1	1			634528
26	1	1	0	0
27	0	1	0	0
28	1	1	0	0
29	1	1	1	1
30	1	1	1	1
31	1	1	0	0
32	1	1	0	0
33	0	0	1	0
34	1	1	0	0
35	1	1	1	0
36
Inactive Trailers

Cell Formulas
Range	Formula
A23:A35	A23	=--(B1:B13=W1)
B23:B35	B23	=--(S1:S13=S3)
C23:C35	C23	=--(N1:N13>W2)
D23:D35	D23	=--(B1:B13=W1)*--(S1:S13=W3)*--(N1:N13>W2)*--(Q1:Q13<>"LOADED")
G23:G35	G23	=SORT(IF(--(B1:B13=W1)*--(S1:S13=W3)*--(N1:N13>W2)*--(Q1:Q13<>"LOADED")*(K1:K13)>0,--(B1:B13=W1)*--(S1:S13=W3)*--(N1:N13>W2)*--(Q1:Q13<>"LOADED")*(K1:K13),""))
Dynamic array formulas.