Help With a Formula to Find the Nth Occurrence

[artiststevens]

SEE POST #4 FOR A BETTER EXPLANATION.

Hello,
I need help with two formulas. One needs to be tweaked a little and one needs to be created from scratch.

The First Formula
I had help creating a formula that when that scans one column searching for cells that contain the letter X, Y, and Z it finds columns where X or Y is false but Z is true. The final result would be the column heading of the first column that was different.

The formula, with the help of helper columns, would find the first instance where the Z’s were true but the either the X’s or Y’s were false. I now want to modify the formula so that I can find the 2nd, 3rd, 4th, or 150th occurrence if I wanted. If there are no additional occurrences after the first the remaining results for the formula would remain blank and so on.

The helper formula is below:

Please Login or Register  to view this content.

The formula that finds the heading of the first occurrence is below.

Please Login or Register  to view this content.

The Second Formula
I need a formula that list the headings of the rows that have an X in column J were all of the X rows equal true in the column.

Example
Rows: 3, 6, 7, and 13 all have an “X” in column J
All of them equal true in Columns: N, O, Q, AND R
So the formula would find the first occurrence of this and the result would be the heading of that column. In the next column it would look for the 2nd occurrence and then the 3rd etc. for however many occurrences I need.

A spreadsheet with examples is attached for a better understanding of what I'm trying to accomplish.


Thank you in advance for any and all help.

[artiststevens]

Still looking for help. Any assistance will be greatly appreciated.

[hemesh]

Hello stevens, Still not able to figure out what you are trying to do as there are so many output in your sheet, where you want your results and what are the criteria for them.

[artiststevens]

Hello Hemesh, Thank you for your input. I separated my questions into two separate Excel files and provided more details. Hopefully it will now be easier to understand. I appreciate any and all help. Thanks again.

[artiststevens]

Any further help would be greatly appreciated.

[hemesh]

Hello Stevens !There Are total 8 Columns i.e.A to H but while matching the same is equals to 7 i.e. 0 to 6! Don't you think it should be 0-7. Please correct me if I am wrong

[artiststevens]

Yes that would be better. Do you think you can help me?

[hemesh]

Stevens are you looking for Something Like This

[artiststevens]

Hello Hemesh,
That was it exactly! Thank you very much for all of your help. Do you think you can help me with the 2nd file Nth Occurence Example2? Any help can provide would be greatly appreciated. Thank you very much again.

[hemesh]

Hello Stevens find the attachment I have used the same formula in Helper columns Then I used the formula to extract the column name.
Formula used for column name is Array formula if you change the formula or change the references in that case do not simply hit control and enter instead hold control and shift then hit enter to make it array formula.

( Once a formula is confirmed as array you will find {} curly braces surrounding your formula which cannot be entered manually)

Confirmation of array is Hold Control and Shift then hit enter

If your query is solved click " * " at the bottom left corner of my post and mark thread as solved

[artiststevens]

Hello Hemesh,
Thank you again for all of your help. I made a slight mistake with what I needed so I attached a spreadsheet that explains it better but what I need is a formula that finds the Nth occurrences of where all of the X's in a column have true values. I'd really appreciate it if you could look at my spreadsheet and help me solve this problem. Thank you for any and all help.

[hemesh]

Which column you want to have Nth occurrence, from column with heading A-Z or from 0-7

Its confusing a little bit

[hemesh]

Hello Stevens ! Basically the formula used for helper column has the issue. I have corrected that one and used array formula in that place. Please find the attached.
Delete the formula from Helper Column and Try this from T3
=IF(AND(L3=TRUE,$J3="Z"),IF(COUNTIFS($J$3:$J$13,"Z",OFFSET($L$2,1,T$2,11,1),TRUE)=COUNTIF($J$3:$J$13,"Z"),TRUE,""),"")
Then drag top to bottom and left to right.




Regards!

[artiststevens]

That was it! Thank you very much Hemesh for taking the time to help me with solving this problem. It is greatly appreciated. Rep was given. Thank you again!

[hemesh]

You are Welcome stevens and thanks for the rep