If column B has # then pull data from column A beside it

[PuntingJawa]

I'm not quite sure how to go about this.

In the table below, I am trying to place the formula to cell M5 that will filter A column based on the information in M3 from data in B column.

With my example, all the cells shown from Column A should be filtered down from M5 since 1 matches the information in M3.

M9 crated items.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Items crated	Crate #	Crate QTY	Serial numbers and counts									Serial numbers by status
2				DCP Count	DCB Count	ERE Count	DTM Count	DTH Count	PPB Count	DMM Count	DMH Count	DCA Count	Crate #
3	DCP2210R007	1	Crate 1	17	14	2	5	4	4	16	19	0	1	2	3	4	HOLD	LOOSE	RMA
4	DCP2210R004	1	12	DCP	DCB	ERE	DTM	DTH	PPB	DMM	DMH	DCA	12	12	17	0	32	3	5
5	DCP2210R003	1	Crate 2	DCP2210R007	DCB1910R007	ERE1912R048	DTM2007R177	DTH2007R141	PPB1911R001	DMM2010R065	DMH2010R078
6	DCP2210R006	1	12	DCP2210R004	DCB2210R006	ERE1912R047	DTM2007R190	DTH2007R180	PPB1910R027	DMM2010R058	DMH2010R080
7	DCB1910R007	1	Crate 3	DCP2210R003	DCB2210R008		DTM2008R042	DTH2008R056	PPB1910R020	DMM2010R012	DMH2010R073
8	DCP2206R004	1	17	DCP2210R006	DCB2210R004		DTM2007R044	DTH2009R159	PPB1911R019	DMM0210R067	DMH2010R029
9	DCP2210R020	1	Crate 4	DCP2206R004	DCB1910R901		DTM2007R018			DMM2010R059	DMH2010R009
10	DCB2210R006	1	0	DCP2210R020	DCB2206R004					DMM2010R060	DMH2010R010
7-23-2024

Cell Formulas
Range	Formula
D2:L2	D2	=D4&" Count"
D3	D3	=COUNTIF($A$3:$A$1048576,"*DCP*")
E3	E3	=COUNTIF($A$3:$A$1048576,"*DCB*")
F3	F3	=COUNTIF($A$3:$A$1048576,"*ERE*")
G3	G3	=COUNTIF($A$3:$A$1048576,"*DTM*")
H3:I3	H3	=COUNTIF($A$3:$A$1048576,"*DTH*")
J3	J3	=COUNTIF($A$3:$A$1048576,"*DMM*")
K3	K3	=COUNTIF($A$3:$A$1048576,"*DMH*")
L3	L3	=COUNTIF($A$3:$A$1048576,"*DCA*")
C4,M4	C4	=COUNTIF($B$3:$B$1048576,1)
N4,C6	N4	=COUNTIF($B$3:$B$1048576,2)
O4,C8	O4	=COUNTIF($B$3:$B$1048576,3)
P4,C10	P4	=COUNTIF($B$3:$B$1048576,4)
Q4	Q4	=COUNTIF($B$3:$B$1048576,"HOLD")
R4	R4	=COUNTIF($B$3:$B$1048576,"LOOSE")
S4	S4	=COUNTIF($B$3:$B$1048576,"RMA")
D5:D21,L5,K5:K23,J5:J20,H5:I8,G5:G9,F5:F6,E5:E18	D5	=FILTER($A$3:$A$1048576,ISNUMBER(SEARCH(D4,$A$3:$A$1048576)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'7-23-2024'!_FilterDatabase	='7-23-2024'!$A$3:$A$1048576	D5:L5, D3:L3
'7-23-2024'!Criteria	='7-23-2024'!$B$3:$B$1048576	M4:S4, C10, C8, C6, C4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M5	Cell Value	=0	text	NO
A1:D1,T1:XFD4,M3:S4,M1:M2,6:1048576,A2:L5,N5:XFD5	Cell Value	=0	text	NO

 

[StephenCrump]

Do you mean like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Items crated	Crate #	Crate QTY	Serial numbers and counts									Serial numbers by status
2													Crate #
3	DCP2210R007	1											1	2	3	4
4	DCP2210R004	3
5	DCP2210R003	1											DCP2210R007	DCP2210R006	DCP2210R004	n/a
6	DCP2210R006	2											DCP2210R003		DCP2206R004
7	DCB1910R007	1											DCB1910R007
8	DCP2206R004	3											DCP2210R020
9	DCP2210R020	1											DCB2210R006
10	DCB2210R006	1
11
Sheet1

Cell Formulas
Range	Formula
M5:M9,O5:O6,N5,P5	M5	=FILTER($A3:$A10,$B3:$B10=M3,"n/a")
Dynamic array formulas.

 

[PuntingJawa]

Do you mean like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Items crated	Crate #	Crate QTY	Serial numbers and counts									Serial numbers by status
2													Crate #
3	DCP2210R007	1											1	2	3	4
4	DCP2210R004	3
5	DCP2210R003	1											DCP2210R007	DCP2210R006	DCP2210R004	n/a
6	DCP2210R006	2											DCP2210R003		DCP2206R004
7	DCB1910R007	1											DCB1910R007
8	DCP2206R004	3											DCP2210R020
9	DCP2210R020	1											DCB2210R006
10	DCB2210R006	1
11
Sheet1

Cell Formulas
Range	Formula
M5:M9,O5:O6,N5,P5	M5	=FILTER($A3:$A10,$B3:$B10=M3,"n/a")
Dynamic array formulas.

That is a lot more simplistic than the one I made use. For mine, I had to swap column B with A. Thanks a bunch! I am glad I was on the right track.
=DROP(FILTER($A$3:$B$1048575,N3=$A$3:$A$1048575),,1)

 

[StephenCrump]

Or you could use: =FILTER($B$3:$B$1048575,N3=$A$3:$A$1048575)

(Although I hope you're not actually using a million plus lines)

 

[PuntingJawa]

Or you could use: =FILTER($B$3:$B$1048575,N3=$A$3:$A$1048575)

(Although I hope you're not actually using a million plus lines)

I had another workbook for populating serial numbers to crate tags based on information in a specific cell. It's a monster of a workbook, but what you provided here is going to make my life a lot easier. It doesn't limit me to have information in an odd looking way. I do appreciate your help and that of this board. I've learned a lot here.