List based on multiple criteria, including roman numerals as values

kakehavata

New Member
Joined
Mar 29, 2021
Messages
24
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hi guys.

I have created a list which shows all values that correspond to certain criteria using an array formula. It works perfectly:

=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E"), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")

I want to add another condition in the IF function, namely that a certain roman numeral will appear in a given range, namely all odd numbers between 1 and 7 (I, III, V, VII). This is what I tried to do:

=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*OR($D$8:$D$385="I",$D$8:$D$385="III",$D$8:$D$385="V",$D$8:$D$385="VII"), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")

This doesn't seem to be working - I am getting the same list, although some values in the D column have other roman numerals (such as II or IV).

I would also like to take into account all values which have two or more roman numerals, one of which is (in this example) an odd number (value of cells like this would be ="III-IV", "V-VI" or "I/III/V/VII").

Can you help me?

Thank you very much!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hello,

It may be easier to do a helper column in AY if you have room to test for your Column D roman numerals with;

=IF(ISNUMBER(SEARCH("I",D8)),1,IF(ISNUMBER(SEARCH("II",D8)),1,IF(ISNUMBER(SEARCH("III",D8)),1,IF(ISNUMBER(SEARCH("V",D8)),1,IF(ISNUMBER(SEARCH("VII",D8)),1,"")))))


This will return a 1 when true and a blank for false for each row then your array formula could be;

=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*($AY$8:$AY$385=1), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")

Careful when using IFERROR with this array as it may slow down your sheet but for 300 rows it should be ok.
 
Upvote 0
Hello,

It may be easier to do a helper column in AY if you have room to test for your Column D roman numerals with;

=IF(ISNUMBER(SEARCH("I",D8)),1,IF(ISNUMBER(SEARCH("II",D8)),1,IF(ISNUMBER(SEARCH("III",D8)),1,IF(ISNUMBER(SEARCH("V",D8)),1,IF(ISNUMBER(SEARCH("VII",D8)),1,"")))))


This will return a 1 when true and a blank for false for each row then your array formula could be;

=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*($AY$8:$AY$385=1), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")

Careful when using IFERROR with this array as it may slow down your sheet but for 300 rows it should be ok.

Thanks for the answer, but unfortunately, I still get a 1 every time... Could this be because "I" as a letter is part of every even number (II, IV, VI, VIII)? (You also added the "II" to the search criteria, I deleted it, because it's an even number)
 
Upvote 0
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Here it is, this is just a segment to give you an idea.


Example.xlsx
ABCDEFGHIKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
8IB01BM0 
9I-IIB00BM0 
10I-II2B00BM0 
11I-IVB00BM01
12I-IVB00BM01
13І-ІV B00BM01
14І-VІІІB00BM01
15I-VIII8B00BM01
16III-IV2B00BM01
17III-VIB00BM01
18V1P01BM01
19V-VI2P0VIII0BM01
20V-VI2P00BM01
21V-VI2P00BM01
22VI1P02BM01
23VI1B02BM01
24VI1P0BM01
25VII-VIII2B00BM01
26VII-VIII2B0BM01
27000000000000BM0 
ВСИЧКИ
Cell Formulas
RangeFormula
D8D8=ROMAN(1)
AX18:AX27,AX8:AX16AX8=COUNTIFS($A$8:$A8,$A8,$B$8:$B8,$B8,$G$8:$G8,$G8, $AW$8:$AW8,$AW8)
AY8:AY27AY8=IF(ISNUMBER(AY9SEARCH("I",D8)),1,IF(ISNUMBER(SEARCH("III",D8)),1,IF(ISNUMBER(SEARCH("V",D8)),1,IF(ISNUMBER(SEARCH("VII",D8)),1,""))))
AX17AX17=COUNTIFS($A$8:$A21,$A17,$B$8:$B21,$B17,$G$8:$G21,$G17, $AW$8:$AW21,$AW17)
D10D10=AT1&"-"&AT2
D16D16=AT3&"-"&AT4
AT19AT19=ROMAN(8)
AU27,AU25,AU22,AU19:AU20,AU8:AU17AU8=IF(OR(D8="I", D8="III", D8="V", D8="VII"), "1", IF(OR(D8="II", D8="ІV", D8="VI", D8="VIII"), "2", "0"))
AU18,AU23,AU21AU18=IF(OR(D18="I", D18="III", D18="V", D18="VII"), "1", IF(OR(D18="II", D18="IV", D18="VI", D18="VIII"), "2", "0"))
G27,AJ27,AG27,AD27,AA27,X27,U27,R27,O27,I27G27=SUM(G8:G26)
AS18:AS27,AS8:AS16AS8=COUNTIFS($A$8:$A8,$A8,$B$8:$B8,$B8,$G$8:$G8,$G8,$H$8:$H8,$H8, $AR$8:$AR8,$AR8)
AS17AS17=COUNTIFS($A$8:$A21,$A17,$B$8:$B21,$B17,$G$8:$G21,$G17,$H$8:$H21,$H17, $AR$8:$AR21,$AR17)
 
Upvote 0
Your original formula, should be written like
Excel Formula:
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*(($D$8:$D$385="I")+($D$8:$D$385="III")+($D$8:$D$385="V")+($D$8:$D$385="VII")), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")
but I cannot test it as the sample data is blank in some of the cells the formula is looking at.
 
Upvote 0
To use a OR condition in an array formula you should add the conditions,

Something like (NOT TESTED)
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*(($D$8:$D$385="I")+($D$8:$D$385="III")+($D$8:$D$385="V")+($D$8:$D$385="VII")), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")

M.
 
Upvote 0
Your original formula, should be written like
Excel Formula:
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*(($D$8:$D$385="I")+($D$8:$D$385="III")+($D$8:$D$385="V")+($D$8:$D$385="VII")), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")
but I cannot test it as the sample data is blank in some of the cells the formula is looking at.
Sorry, I don't know how much info to disclose. This formula gives TRUE and FALSE as answers, and the original formula makes a list of values in the B column. Also, does your formula take into account values such as D22, which have "I" in them, but the number is actually "VI"?
 
Upvote 0
To use a OR condition in an array formula you should add the conditions,

Something like (NOT TESTED)
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*(($D$8:$D$385="I")+($D$8:$D$385="III")+($D$8:$D$385="V")+($D$8:$D$385="VII")), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")

M.
I think this actually works!! But I have to test it some more to confirm.

EDIT: It works only when there is one roman numeral (i.e. I), not when there is more than one (i.e I-II).
 
Upvote 0
The formula I posted is exactly the same as the one Marcelo posted. ;)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top