Formula

[limo2088]

Is there a way or is there a formula which can skip certain values and only show the values you want. Bare with me
I have items in cell E6:E20 and F6:F20, and a date above both, what I need this to do is when I insert a date into J5, it looks at the dates in Column E5, and F5 and whatever the items are under those dates would show up under the date I entered in J5.

So in the example I have 11/7/2021 in cell J5, So I want the items under E5 because that's what matches my date in J5. But I do not want to include any of the Cells which say "Off, Call Off, No Show, Training.
Then Also if I enter 11/8/2021 in J5, I would want the items under F5 to populate under J5, again minus any words which say "Off, Call Off, No Show, Training.

No idea if this is possible and I know it sounds really confusing

TestMS.xlsx
	D	E	F	G	H	I	J	K	L	M	N
3							Example 1			Example 2
4
5		11/7/2021	11/8/2021				11/7/2021			11/8/2021
6	1	Chevy	Cord			1	Chevy		1	Cord
7	2	Ford	Paper			2	Ford		2	Paper
8	3	Mercury	Card			3	Mercury		3	Card
9	4	Nissan	Off			4	Nissan		4	Marker
10	5	Tree	Marker			5	Tree		5	Sun
11	6	Clouds	Sun			6	Clouds		6	Grass
12	7	Off	Grass			7	Gmc		7	Pipe
13	8	Call Off	No Show			8	Ground		8	Video
14	9	Gmc	Pipe			9	Plastic		9	Computer
15	10	Ground	Video			10	Desk		10	Sheet
16	11	No Show	Computer			11	Phone		11	Mouse
17	12	Plastic	Off
18	13	training	Sheet
19	14	Desk	Off
20	15	Phone	Mouse
21
Sheet1

 

[Ezguy4u]

limo let's get the ball rolling. Now usually this creates more questions than answers but we have to start some place. Here is a program that will do what you have asked. If we are lucky, some of the A Students will weigh in and show us even better solutions. Let the discussion begin.

Option Explicit

Sub Trans01()

Dim CellCnt As Integer
Dim i As Integer
Dim row1 As Integer

CellCnt = Cells(Rows.Count, "E").End(xlUp).Row
row1 = 6
If Range("J5") = Range("E5") Then
For i = 6 To CellCnt
If Cells(i, 5) <> "Off" And Cells(i, 5) <> "Call Off" _
And Cells(i, 5) <> "No Show" And Cells(i, 5) <> "training" Then

Cells(row1, 10) = Cells(i, 5)

Cells(row1, 9) = row1 - 5
row1 = row1 + 1
End If

Next i
End If

If Range("M5") = Range("F5") Then
row1 = 6
For i = 6 To CellCnt
If Cells(i, 6) <> "Off" And Cells(i, 6) <> "Call Off" _
And Cells(i, 6) <> "No Show" And Cells(i, 6) <> "Training" Then

Cells(row1, 13) = Cells(i, 6)

Cells(row1, 12) = row1 - 5
row1 = row1 + 1
End If
Next i

End If

End Sub

21-11-21 Date.xlsm
	D	E	F	G	H	I	J	K	L	M
3							Example 1			Example 2
4
5		11/7/2021	11/8/2021				11/7/2021			11/8/2021
6	1	Chevy	Cord			1	Chevy		1	Cord
7	2	Ford	Paper			2	Ford		2	Paper
8	3	Mercury	Card			3	Mercury		3	Card
9	4	Nissan	Off			4	Nissan		4	Marker
10	5	Tree	Marker			5	Tree		5	Sun
11	6	Clouds	Sun			6	Clouds		6	Grass
12	7	Off	Grass			7	Gmc		7	Pipe
13	8	Call Off	No Show			8	Ground		8	Video
14	9	Gmc	Pipe			9	Plastic		9	Computer
15	10	Ground	Video			10	Desk		10	Sheet
16	11	No Show	Computer			11	Phone		11	Mouse
17	12	Plastic	Off
18	13	training	Sheet
19	14	Desk	Off
20	15	Phone	Mouse
Transfer

 

[DJunqueira]

In case you prefer a formula.

=FILTER(INDEX(B4:C18,,MATCH(J5,B3:C3,0)),MMULT(IF(INDEX(B4:C18,,MATCH(J5,B3:C3,0))<>TRANSPOSE(M12:M15),--ISTEXT(INDEX(B4:C18,,MATCH(J5,B3:C3,0))),0),{1;1;1;1})=4)

 

[DJunqueira]

You also are supposed to create a little list with the terms to avoid for easy management.

 

[Peter_SSs]

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’)

If the formula suggestion in post #3 works for you then you likely also have the LET function so this much shorter one would also work.

21 11 13.xlsm
	E	F	G	H	I	J	K	L
1
2
3						Example 1
4
5	11/07/2021	11/08/2021				11/07/2021		Exclude
6	Chevy	Cord				Chevy		Off
7	Ford	Paper				Ford		Call Off
8	Mercury	Card				Mercury		No Show
9	Nissan	Off				Nissan		Training
10	Tree	Marker				Tree
11	Clouds	Sun				Clouds
12	Off	Grass				Gmc
13	Call Off	No Show				Ground
14	Gmc	Pipe				Plastic
15	Ground	Video				Desk
16	No Show	Computer				Phone
17	Plastic	Off
18	training	Sheet
19	Desk	Off
20	Phone	Mouse
21
List with exclusions

Cell Formulas
Range	Formula
J6:J16	J6	=LET(f,FILTER(E6:F20,E5:F5=J5),FILTER(f,ISNA(MATCH(f,L6:L9,0))))
Dynamic array formulas.

If you have MS365 but do not yet have the LET function then this should do it

=FILTER(FILTER(E6:F20,E5:F5=J5),ISNA(MATCH(FILTER(E6:F20,E5:F5=J5),L6:L9,0)))

 

[Peter_SSs]

If you do not have MS365 here is an alternative formula approach for Excel 2010 or later.

21 11 13.xlsm
	E	F	G	H	I	J	K	L
1
2
3
4						2
5	11/07/2021	11/08/2021				11/08/2021		Exclude
6	Chevy	Cord				Cord		Off
7	Ford	Paper				Paper		Call Off
8	Mercury	Card				Card		No Show
9	Nissan	Off				Marker		Training
10	Tree	Marker				Sun
11	Clouds	Sun				Grass
12	Off	Grass				Pipe
13	Call Off	No Show				Video
14	Gmc	Pipe				Computer
15	Ground	Video				Sheet
16	No Show	Computer				Mouse
17	Plastic	Off
18	training	Sheet
19	Desk	Off
20	Phone	Mouse
21
List with exclusions (2)

Cell Formulas
Range	Formula
J4	J4	=MATCH(J5,E5:F5,0)
J6:J20	J6	=IFERROR(INDEX(E:F,AGGREGATE(15,6,ROW(E$6:E$20)/(ISNA(MATCH(INDEX(E$6:F$20,0,J$4),L$6:L$9,0))),ROWS(J$6:J6)),J$4),"")

 

[limo2088]

If you do not have MS365 here is an alternative formula approach for Excel 2010 or later.

21 11 13.xlsm
	E	F	G	H	I	J	K	L
1
2
3
4						2
5	11/07/2021	11/08/2021				11/08/2021		Exclude
6	Chevy	Cord				Cord		Off
7	Ford	Paper				Paper		Call Off
8	Mercury	Card				Card		No Show
9	Nissan	Off				Marker		Training
10	Tree	Marker				Sun
11	Clouds	Sun				Grass
12	Off	Grass				Pipe
13	Call Off	No Show				Video
14	Gmc	Pipe				Computer
15	Ground	Video				Sheet
16	No Show	Computer				Mouse
17	Plastic	Off
18	training	Sheet
19	Desk	Off
20	Phone	Mouse
21
List with exclusions (2)

Cell Formulas
Range	Formula
J4	J4	=MATCH(J5,E5:F5,0)
J6:J20	J6	=IFERROR(INDEX(E:F,AGGREGATE(15,6,ROW(E$6:E$20)/(ISNA(MATCH(INDEX(E$6:F$20,0,J$4),L$6:L$9,0))),ROWS(J$6:J6)),J$4),"")

I apologize I am using Excel 2019, This formla looks great but when I do it, it still put the terms, Off, Call Off, No Show, etc in the rows J6:J16, I know I am doing something wrong

 

[limo2088]

I apologize I am using Excel 2019, This formla looks great but when I do it, it still put the terms, Off, Call Off, No Show, etc in the rows J6:J16, I know I am doing something wrong

Never mind, it works, thank you sooooo much!!! Thank you everyone who replied

 

[Peter_SSs]

Never mind, it works, thank you sooooo much!!! Thank you everyone who replied

You're welcome. Glad we could help. Thanks for the follow-up.

Thanks also for updating your profile.