How to extract the list from Master data between two dates - Situation differs ..

Dear Mr. Oeldere,

Thanks for your guidance, yes, i have done the expected result message box in the excel file for your kind reference.

Thanks in advance

Bala

Dear Mr. Oeldere,

Thanks for your efforts, I am learning and practicing automation report generation, if i do pivot table each and every time i have to go and refresh. I do not want to do it. I want to do it in ARRAY formula to extract those required details.

Thanks

Yes, you are right. But my learning thirst and requirement is for automation report and learning ARRAY formula.

Dear Mr. Oeldere,

I am really thank you and admired about your continuous way of convincing me to go with pivot table. But here situation is different, i have to learn this formula and try to use it my existing MIS system, which will be used by my team members. They are unaware about these pivot table, hence i am trying to incorporate automation extraction of report based on date inputs. This is what i am really anticipate to do it and mentioned in my 1st mail itself.

Thanks

Dear Forum,

Any one pls help me to solve this requirement

Thanks in advance

Dear Mr. Oeldere,

Please refer my exact requirement in the sheet. I need to extract the record between two dates. Kindly help me on this.

Bala

@Oeldere,

Anyway, what ever you have done with the help of pivot table. That is not my exact expected system, i want it to be in using ARRAY formula. I agreed that, you have also used that, but it has been done with pivot table.

Kindly understand.

@oeldere,

Fine.. I acknowledge all your efforts and you are really amazing. Can you do me a favour? Could you please help me on my exact requirement with as i proposed. Not exactly with pivot table and I never prefer this option.

Bala

We can use Index and Match .. not an issue. But i do not want to use it through pivot option.

Bala

Thanks oeldere...

Dear Ms. Ali,

Good morning and have a great day. Thanks for your response. I have used your formula and but unfortunately is not returning any value. I request you, could you please go through my attachment and guide me.

Regards

Dear Ms. Ali,

The following is the exact thread, which i had posted yesterday.

Again me... now i have come with same situation but this time is little bit different requirement. I need to extract the list as below given situation.

My previous threads were also related to extract the individual details. But, those were based on date of registered at bank. Now i want to extract the individual details by month of loan sanctioned ("J" column), Not by the Date of registered.. Date of registered and Date of loan sanctioned differs here. . I have done the count using "Helper Column".

For example: On July 2014 - Dec 2014, Totally 52 persons were registered in the bank for applying loan. But only 14 were sanctioned loan by bank and loan were sanctioned in different months. Now I want extract the individual data "based on loan were sanctioned".

If I give the inputs like, From : July 2014 , To: 31.12.2014, I need to get the count as "14" as well as i need to get extract details for this 14 people.

Please refer the attachment. I have tried few formula, but not able to get the logic to extract by loan sanctioned date. "Array formulas are really dynamic" and i am in crawling stage now in array formula.

Please guide me.

The same post today i have shared with http://www.mrexcel.com/forum/excel-q...-criteria.html for solution. M

Mr. Oeldere has tried to help me as much as he can.

Thanks in advance.

Bala

Skipping from post #1 to the end, ignoring the convoluted mess of confusion, calamity, catastrophe and chaos in between!

Is this what you need?

Note that I have deleted your 'Helper' column, it wasn't helping anything.

Dear Mr. Jasan,

Yes, this is what i exactly wanted. I got it. Thanks for your support and guidance.

Dear Ms. Ali,

Thanks for your efforts to address my requirement.

Million thanks

Bala

Dear Mr. Jason,

Thanks a lot your support and understood my requirement. Please guide me, How do we call (The term) mixing up of IF, MATCH, INDEX, SMALL, ROWS, COLUMNS. I want to learn the logic of structured formula. Is this ARRAY or Nested?

Thanks in advance.

Any formula is referred to as 'Nested' when one or more functions are used with the brackets of another function.

As a simple example

=IF(A1=1,SUM(B1:B10),"")

The SUM function is inside the brackets of the IF function, so that means it is nested.

To solve your problem in this thread, Column L uses a Nested Array formula (formula is enclosed in {}) with 6 functions.

The other columns use Nested formulas with 3 functions.

Originally Posted by oeldere

I did not see the link posted in #1, but the link in #24 in NOT working.

Code tags are for posting 'Code', there are 'Quote' tags for quoting previous replies.

The broken link in post #24 is irrelevant, the one in post #1 fulfils the requirement of the forum rules.

A way to simplify construction of array formulas is to start with a helper column (or several if needed) then join all of the parts together.

Using the example file, the following helper formula would give the S.NO of each matching for as it is copied down from K4 to K63

=IF(J4>=M$1,IF(J4<=$M$2,A4))

To make that useful for an array, all you have to do is expand the ranges to cover all rows rather than the current row. Remember you need to make the ranges absolute.

=IF($J$4:$J$63>=M$1,IF($J$4:$J$63<=$M$2,$A$4:$A$63))

Note that the last part of the array, $A$4:$A$63 in the example, would ideally be a list of consecutive numbers starting at 1, the S.NO column gives us this, otherwise we would need to use

=IF($J$4:$J$63>=M$1,IF($J$4:$J$63<=$M$2,ROW($A$4:$A$63)-ROW($A$4)+1))

to generate the list of consecutive numbers within the formula.

Next we need to extract the useful elements from the array, this is where SMALL is used. The ARRAY that we have above sits (is nested) in the first part of the function.

=SMALL(IF($J$4:$J$63>=$M$1,IF($J$4:$J$63<=$M$2,$A$4:$A$63)),ROWS(M$4:M4))

The ROWS(M$4:M4) part at the end is used to count as you copy the formula down the page, so that you get the 1st, 2nd, 3rd, etc. values from the array.

You will see that only the row of the first part of the range is absolute (has the $ sign), this allows the range to increase as you fill the formula down the sheet, from M$4:M4 to M$4:M5, etc.

Once the formula is entered into more rows than needed to show all of the data, it will start returning errors, so the final part of the task is to blank out those errors.

One habit that a lot of people have, is to nest the whole formula in the IFERROR function, when you are only checking an error on a simple formula, this is most often the best way to do it, but with arrays, it can be far from efficient.

=IF(ROWS(M$4:M4)>$N$2,"",SMALL(IF($J$4:$J$63>=$M$1,IF($J$4:$J$63<=$M$2,$A$4:$A$63)),ROWS(M$4:M4)))

The simple test uses the same counting method as the SMALL function to compare how many rows have used the formula compared to the count in N2, once the formula has passed the required number of rows, the blank is returned with processing the array, removing the need for the error check.

The results of the array formula can then be used with a simple INDEX formula to return the correct data for the other columns.

Hi,
use this array formula in M4 and drag across

Formula:

Please Login or Register  to view this content.

Thanks a lot Mr. Jason, Ms. Ali and Mr. TubyBTH for your help and teaching. Really made a insight and curious to learn so much in ARRAY formula.

Million Thanks

Dear Mr. Oeldere,

Really i am sorry, i though i was done already. i added reputation also to Mr. Jason. thats why i thought i was mared as "SOLVED".

I will ensure, it wont happen again in future. Have a great day

Bala