PuntingJawa
Board Regular
- Joined
- Feb 25, 2021
- Messages
- 157
- Office Version
- 365
- 2019
- Platform
- Windows
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.
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 |