I need help with this array formula

valmir

Board Regular
Joined
Feb 10, 2021
Messages
142
Office Version
  1. 365
Platform
  1. Windows
Hi! Everyone
I have this array formula in L:S that pulls data from B:J. Is it possible to apply a filter or something so that only values named ACA in column C (with the respective row matches) are pulled out?
Thanks!

PRI COMPETITIONS test.xlsx
ABCDEFGHIJKLMNOPQRS
5102-10-2021ACA110+1302-10-2021ACA110+1
6203-11-2021SBE150+5326-06-2021ACA113-2
7320-10-2021PET1220117-02-2021ACA120+2
8427-10-2021INT121+1315-12-2019ACA10030+3
9531-10-2021CCU101-1000-01-1900ACA0
10607-11-2021CAA1000100-01-1900ACA000000
11700-01-1900LIB0030-06-2021BAI130+3
12800-01-1900DLS000031-03-2021BAI120+2
13900-01-1900KAB0007-11-2021CAA1000
141000-01-1900DES000016-05-2021CAA121+1
151100-01-1900MAQ0013-04-2021CAA140+4
161200-01-1900WIL000023-02-2020CAA00101+1
171300-01-1900PRO0020-10-2019CAA10040+4
181400-01-1900SCC000000-01-1900CAA000
191500-01-1900SAG0031-10-2021CCU101-1
201600-01-1900ACA0002-05-2021CCU120+2
211700-01-1900SBE000020-03-2021CCU120+2
221800-01-1900PET0001-03-2020CCU10021+3
231900-01-1900INT000006-10-2019CCU10010+1
242000-01-1900CCU0000-01-1900CCU0
252100-01-1900CAA000012-05-2021DES120+2
262200-01-1900LIB000017-03-2021DES1000
272300-01-1900DLS0012-02-2020DES10010+1
282400-01-1900KAB000020-08-2019DES10020+2
292500-01-1900DES0000-01-1900DES000
302600-01-1900MAQ000000-01-1900DES0
312700-01-1900WIL0000-01-1900DLS000
322800-01-1900PRO000000-01-1900DLS0
332900-01-1900SCC0031-07-2021FER141+3
343000-01-1900SAG000014-03-2021FER130+3
3508-02-2020FER010112
3619-09-2019FER100202
3727-10-2021INT1211
38129-12-2020INT110+1324-04-2021INT1101
39220-03-2021CCU120+2329-12-2020INT1101
40318-04-2021WIL1220108-03-2020INT100202
41417-03-2021DES1000113-10-2019INT100101
42513-04-2021CAA140+4300-01-1900INT000
43606-04-2021SAG112-1000-01-1900KAB0
44710-04-2021PRO121+1300-01-1900KAB000
45806-02-2021MAQ123-1016-07-2021LIB1110
46907-02-2021PET110+1303-04-2021LIB1220
471003-04-2021LIB1220123-11-2019LIB100415
481117-02-2021ACA120+2300-01-1900LIB0
491231-03-2021BAI120+2300-01-1900LIB000
501328-02-2021SCC121+1300-01-1900LIB000000
511407-03-2021SRC120+2327-10-2019MAI000000
521514-03-2021FER130+3300-01-1900MAI000000
531624-04-2021INT110+1305-06-2021MAQ1000
541702-05-2021CCU120+2306-02-2021MAQ123-1
551809-05-2021WIL1110114-03-2020MAQ100314
561912-05-2021DES120+2323-10-2019MAQ100314
572016-05-2021CAA121+1300-01-1900MAQ0
582123-05-2021SAG1000100-01-1900MAQ000
592229-05-2021PRO110+1320-10-2021PET1220
602305-06-2021MAQ1000112-06-2021PET103-3
612412-06-2021PET103-3007-02-2021PET1101
622516-07-2021LIB1110103-11-2019PET001022
632626-06-2021ACA113-2000-01-1900PET0
642730-06-2021BAI130+3300-01-1900PET000000
652819-07-2021SCC140+4329-05-2021PRO1101
662924-07-2021SRC142+2310-04-2021PRO1211
673031-07-2021FER141+3319-01-2020PRO100404
6827-08-2019PRO001011
6900-01-1900PRO0
7000-01-1900PRO000
71127-08-2019PRO00101+1023-05-2021SAG1000
72220-08-2019DES10020+2306-04-2021SAG112-1
73331-08-2019SAG10010+1326-02-2020SAG001123
74419-09-2019FER10020+2331-08-2019SAG100101
75522-09-2019SRC10041+5300-01-1900SAG0
76620-10-2019CAA10040+4300-01-1900SAG000
77706-10-2019CCU10010+1303-11-2021SBE1505
78813-10-2019INT10010+1300-01-1900SBE000
79923-10-2019MAQ10031+4319-07-2021SCC1404
801027-10-2019MAI000000028-02-2021SCC1211
811103-11-2019PET00102+2018-12-2019SCC010112
821223-11-2019LIB10041+5300-01-1900SCC000
831315-12-2019ACA10030+3300-01-1900SCC0
841418-12-2019SCC01011+2100-01-1900SCC000000
851522-12-2019WIL10020+2324-07-2021SRC1422
861619-01-2020PRO10040+4307-03-2021SRC1202
871712-02-2020DES10010+1316-02-2020SRC010000
881826-02-2020SAG00112+3022-09-2019SRC100415
891908-02-2020FER01011+2109-05-2021WIL1110
902016-02-2020SRC010000118-04-2021WIL1220
912123-02-2020CAA00101+1022-12-2019WIL100202
922201-03-2020CCU10021+3300-01-1900WIL000
932308-03-2020INT10020+2300-01-1900WIL0
942414-03-2020MAQ10031+4300-01-1900WIL000000
952500-01-1900MAI000000000-01-19000000000
962600-01-1900PET000000000-01-19000000000
972700-01-1900LIB000000000-01-19000000000
982800-01-1900ACA000000000-01-19000000000
992900-01-1900SCC000000000-01-19000000000
1003000-01-1900WIL000000000-01-19000000000
Seasons
Cell Formulas
RangeFormula
L5:S100L5=SORTBY(CHOOSE({1,2,3,4,5,6,7,8},$B$5:$B$100,$C$5:$C$100,$D$5:$D$100,$E$5:$E$100,$F$5:$F$100,$G$5:$G$100,$H$5:$H$100,$I$5:$I$100,$J$5:$J$100),$C$5:$C$100,1,$B$5:$B$100,-1)
Dynamic array formulas.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=SORT(FILTER(B5:J100,C5:C100="ACA"),1,-1)

You could also simplify your formula like
Excel Formula:
=SORT(B5:J100,{2,1},{1,-1})
 
Solution

valmir

Board Regular
Joined
Feb 10, 2021
Messages
142
Office Version
  1. 365
Platform
  1. Windows
Thanks a lot once again. I knew it would be something like that but from theory to actually putting into practice goes a long way! :giggle:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
142
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff! I was wondering if there is an "opposite" function to this formula, meaning, instead of including all matching "ACA" values, in this case, that would be including all matching values except those with ACA?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
Yup, just change the ="ACA" to <>"ACA"
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
142
Office Version
  1. 365
Platform
  1. Windows
Worked perfectly! Thanks! :) 👏👏👏
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
My pleasure. (y)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,856
Messages
5,766,786
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top