If Formula Required

[Techtrend]

HI Team

Details are mentioned in the attached document,
Need to filter stocks based on Signal change in C Col

Thanks in advance
Satish

 

[Luke M]

Can do this with an array formula. See attached.

 

[Techtrend]

Thank you so much,,It works fine

 

[neeraj2050]

@Luke: It's great!! Sir, could you please explain this formula

 

[Khalid NGO]

Hi to all,

Similar as LM solution but with normally entered:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,1/($C$2:$C$100<>$F$2:$F$100)*ROW($C$2:$C$100),ROWS(C$2:C2))),"")

=IFERROR(INDEX(F:F,AGGREGATE(15,6,1/($C$2:$C$100<>$F$2:$F$100)*ROW($C$2:$C$100),ROWS(D$2:D2))),"")

Regards,

 

[neeraj2050]

@Khalid Sir: Could you please suggest a link, wherein, I could get the knowledge about Aggregate and Row function as I have no idea about it

 

[Khalid NGO]

Hi Neeraj,

Pattern of using AGGREGATE function is almost similar to the regularly used function SUBTOTAL.

If you are okay with SUBTOTAL then you can easily understand the logic of AGGREGATE.

You will find several links on the web and on this forum too by using search option:
http://www.exceluser.com/excel_help/functions/function-aggregate.htm
https://www.techonthenet.com/excel/formulas/aggregate.php
http://www.excelfunctions.net/Excel-Aggregate-Function.html

Regards,

 

[neeraj2050]

@Khalid Sir: Thanks!! Could you please also suggest for Row and Column function

 

[Khalid NGO]

Hi Neeraj,

You can understand the logic with this too:
=AGGREGATE(15,6,1/(CriteriaColumn=Criteria)*Values,1)


15 = function number i.e. SMALL
6 = used for ignoring error values
CriteriaColumn = Specify your criteria range, for example A2:A10
Criteria = Specify your criteria, for example single cell B2 or "abc" or range B2:B10
Values = Specify value range
1 at the last is used as K parameter, it can be replaced with ROW(A1) or ROWS(A$1:A1) so that it can be incremented +1 by dragging down.

Above aggregate function and LM's SMALL(IF(.....ROW( array produces same results.

See this link for ROW & COLUMN function:
https://www.myonlinetraininghub.com/excel-rows-and-columns-functions

HTH,

 

[GraH - Guido]

@Luke: It's great!! Sir, could you please explain this formula

Hi Neeraj, I notice you are asking to explain formulas once and a while. A good place to start with the basics is the Excel help, and you can always use the formula evaluation tool. Look for it in formula auditing on the formulas ribbon.

It explains step by step how Excel calculates and it shows for each argument the intermediate result. In Luke's formula the underlined part is the starting point. Press evaluate to see the step result and repeat. Besides being a nice too to help you debug your own formulas, you can also use it to understand the logic on how the formula is actually working. The preview window might be too small when dealing with large arrays, but still, I find it rather useful.