• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Formulas

B

BigBuilder

New Member
Hi,

Simplistically, I have, in cell A1 a code: RTH - text field.
In cell A2, I have the same code.

In cell B1 I have another text field: Other.
In cell B2 I have another text field: Total.

I have an array formula:
IF(A1:A2=RTH,IF(B1:B2="Total","Yes","No"))

Does this work ? No !!

Examining the IF formula, it gives a True, True for the first IF and a True, False for the second IF and then the whole formulareturns a No.
As I was expecting a Yes, I've no idea why it's not doing what I expected.
Any ideas welcome as well as any other way of doing this.

Thanks.
 
Check if the below logic works...

IF(OR(AND(A1="RTH",A2="RTH"),AND(B1="total",B2="total")),"Yes","No")
 
Hi @BigBuilder!

If I understood your requirements... the formula you can use is this (array formula):

=IF(AND(A1:A2="RTH",B1:B2="Total"),"Yes","No")

Or this (non array formula):

=IF(AND(A1="RTH",A2="RTH",B1="Total",B2="Total"),"Yes","No")

Remember: All the text in Excel must be with quotes. Blessings!
 
John Jairo V said:
Hi @BigBuilder!

If I understood your requirements... the formula you can use is this (array formula):

=IF(AND(A1:A2="RTH",B1:B2="Total"),"Yes","No")

Or this (non array formula):

=IF(AND(A1="RTH",A2="RTH",B1="Total",B2="Total"),"Yes","No")

Remember: All the text in Excel must be with quotes. Blessings!
Click to expand...
Great solutions and many thanks.

I should've mentioned that the spreadsheet I'm trying to interrogate is 5,000 lines long. Not a big spreadsheet in the context of many others but, unfortunately, not one that lends itself to a non-array formula solution.
The array solution is interesting though. I've previously understood that you can't use ANDs in array formulas due to the way the AND logic works - so, I've tended to shy away from ANDs - and, (excuse the pun), OR's but I'll certainly give this one a go.

Thanks again, both.

Just tried the AND solution and, unfortunately, no joy there.
I'm speculating that both parts of the AND need to be true before the AND will return True.
In this case, I have, for the first part of the AND True, True but, for the second part I have False, True so the AND False's out.
 
Last edited:
Hi @BigBuilder.

Helps a lot if you upload a sample file with the expected results and logic explain... like this the answers must be puntual and optimal. Blessings!
 
Yes, John, that'd be ideal.
Regrettably I'm analysing sensitive health data so uploading anything isn't an option.
Just imagine the headlines in the Daily Mail if I did !

Regards.
 
Hi ,

It is unfortunate that you are asking us to deduce your logic from your formula , which according to you is not giving you the result you want.

If you can state your requirements in plain English , it will be a few minutes to arrive at a solution ; explain what are the input data cells , where the formula is to be entered , and the logic by which the output is to be obtained. That is all it takes to get a solution from this forum.

Narayan
 
NARAYANK991 said:
Hi ,

It is unfortunate that you are asking us to deduce your logic from your formula , which according to you is not giving you the result you want.

If you can state your requirements in plain English , it will be a few minutes to arrive at a solution ; explain what are the input data cells , where the formula is to be entered , and the logic by which the output is to be obtained. That is all it takes to get a solution from this forum.

Narayan
Click to expand...

Narayan,
Thanks for this response.
Apologies, I'm obviously missing something regarding what additional info is required. My original post, I thought, explained exactly what I wanted i.e., I have an array formula that isn't giving the result I expected and I gave an example set of data that isn't playing by the rules - as I see them.
I apologise for then generating responses that weren't quite related to what I wanted, probably because I didn't explain the size of the spreadsheet data I was trying to analyse which made the non-array solutions unuseable.
Can I assume that, given the formula I posted and all your responses that my formula or, variations thereof, work on the data I posted ?
If so, then that is progress as it eliminates any stupid mistakes I may have made in my code.
Going on the above assumption, the only, other, strange thing about the data is that the data: Other and Total are prefixed by a single quote in the original input data sheet (').
I didn't want to confuse the issue by mentioning it before as I wanted to eliminate any possible mistakes in my formula.
So, I'm, logically. comparing, e.g., 'Other with Other and 'Data with Data in the formula.
Now, the above statements and the comments below may have nothing to do with this but ......
If I bring up the Format Cells sub-menu, Excel thinks the cells with either 'Other or 'Data are General format.
If I enter ISTEXT to interrogate either of the cells, Excel thinks the cells are Text i.e., ISTEXT returns a True.
LENGTH gives both cells as length 5 so LENGTH is ignoring the single quote.
LEFT/MID also ignores it.
I've tried CONCATENATE to incorporate the single quote into the comparision string without success, well, the CONCATENATE works ok but the array formula still doesn't give the expected results.
I've Google'd my issue and, one forum thread suggested that the IF needs the first cell in the array formula to be True, otherwise it'll return a False. Don't know on that one.

Anyway, to conclude: I think I've explained what the input cells are - with examples, i.e., "RTH" in cells A1 and A2, "Other" and "Total" in cells B1 and B2, the array formula can be entered in any other cell, really; it's location isn't vital, and the logic; I want a "Yes" to come out of the array formula.
If anyone feels they need further info, please get back to me as this one has me really puzzled.
Unfortunately, I can't get the input data re-coded or amended as it's from a third-party organisation.

Thanks again, very much, for all your responses.
 
Hi ,

I can still understand only the following :

1. The input cells are A1 , A2 , B1 and B2.

2. The formula can be entered in any cell ; let us assume C1

3. You want the formula to return the text Yes.

Can you explain under what conditions it should return Yes , and under what conditions it should return any other output ?

Do I understand that :

If A1 = "RTH" and A2 = "RTH" and B1 = "Other" and B2 = "Total" , you want the output to be Yes ?

If so , why do you need an array formula ?

Sorry for being dense.

Narayan
 
A formula for 5000 rows and just one sample? Try this non array formula:
=IF(AND(COUNTIF(A1:A2,"RTH")=2,COUNTIF(B1:B2,"Total")>0),"Yes","No")
If it does not work overall then come back with specifics where it does not work.
 
NARAYANK991 said:
Hi ,

I can still understand only the following :

1. The input cells are A1 , A2 , B1 and B2.

2. The formula can be entered in any cell ; let us assume C1

3. You want the formula to return the text Yes.

Can you explain under what conditions it should return Yes , and under what conditions it should return any other output ?

Do I understand that :

If A1 = "RTH" and A2 = "RTH" and B1 = "Other" and B2 = "Total" , you want the output to be Yes ?

If so , why do you need an array formula ?

Sorry for being dense.

Narayan
Click to expand...
Hi, Narayan,
No need to apologise, my fault for missing that piece of the jigsaw.
I want the formula to return Yes when it first matches RTH in cells A1 to A2 and then matches Total in any cell in cells B1 to B2.
What I'm actually trying to do and therein may lie the problem; RTH is a code amongst many codes in the spreadsheet I'm analysing and occurs quite a way down the code list circa line 2473. Now, RTH has a list of text fields associated with it in another column. These text fields have values associated with them. So, what I want to do is to go down one column until I match RTH and, within the subset of data associated with RTH, I want to find the row matched Total, then I want to go across that line and pick up the number I'm interested in.
So it's a bit like a double VLOOKUP. If I could get VLOOKUP to first go down the columns and find RTH, then, within that RTH data subset, VLOOKUP, in a different column, the line with Total in it, then I could tell it to go across n columns to retrieve the number I want.
I can get around this using the OFFSET and that'll work well. Problem is that it won't work on closed workbooks. I don't want to copy the source data into my spreadsheet as it'll make it huge and unuseable for my team.
I could probably get around it using a macro but don't want to do that either as my team has different versions of Excel, (don't ask), and any macro that works on my machine may not work on someone else's.
Hopefully this clarifies things a bit but, as always, please get back to me if further info is required.

Regards.
 
Looks like you're closer to describing your situation. If you can use helper column then you can use a concatenate like below (Lets say in cell C1):
=A1&B1
copy down
And then write in a free cell.
=MATCH("rthtotal",C1:C5000,0)
It will first row where this combination is found.

If helper column is not a possibility then ARRAY enter following formula:
=MATCH("rthtotal",A1:A5000&B1:B5000,0)

Change upper limit of 5000 to suit your case.

If above formula gives the desired result then it can be placed inside INDEX function to retrieve info needed.

PS: The formula will give incorrect result if you have condition for successive entry of "rth" as well.
 
shrivallabha said:
A formula for 5000 rows and just one sample? Try this non array formula:
=IF(AND(COUNTIF(A1:A2,"RTH")=2,COUNTIF(B1:B2,"Total")>0),"Yes","No")
If it does not work overall then come back with specifics where it does not work.
Click to expand...
Thanks, Shrivallabha, that's a really interesting solution. I didn't, (obviously), know you could do that.

However, you've correctly predicted that the data sheet does, indeed, contain multiple entries of "RTH".
Is there any way around that ?

Thanks again.

Regards.
 
Hi ,

I should think something like this should work :

=INDEX(FinalColumnFromWhichOutputIsRequired , SMALL(IF(FirstColumnOfInterest = "RTH", IF(SecondColumnOfInterest = "Total" , ROW(FirstColumnOfInterest) - MIN(ROW(FirstColumnOfInterest)) + 1)) , ROW(A1)))

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 
Thanks very much to you, Narayan and you, Shrivallabha for contributing to the eventual solution which, I'm sure you'll see, is a bit of a mix of your 2 solutions.
Thanks to you Shrivallabha for enlightening me to the fact that you can concatenate search strings from different fields in a spreadsheet without having to insert another column to contain the concatenated search string.
I would've inserted another column to contain a concatenated search string which would've been constructed as: CONCATENATE("RTH","TOTAL"). Not very efficient.
And, you, Narayan as you made me think that if INDEX will work, well, why not the LOOKUPs ? I was a bit apprehensive as I wasn't sure whether they'd work in an array formula as I needed an array formula to contain the MATCH syntax but, they did !
So, combining both suggested solutions, I came up with the array formula as below.
=HLOOKUP(G6,'[Referral to Treatment - 13-04.xls]Provider'!$C$14:$BN$5000,MATCH(D8&"Total",'[Referral to Treatment - 13-04.xls]Provider'!$C$14:$C$5000&'[Referral to Treatment - 13-04.xls]Provider'!$F$14:$F$5000,0),FALSE)
However, also, thanks to everyone else who contributed. It's taken me a long time to admit defeat and post on this forum but the problem's been resolved very quickly so, thanks again to all.
 
You must log in or register to reply here.
Back
Top