• 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.

Formula Challenge 007 - return distinct/duplicate /unique values as an array

Thanks Sir.
Is there any way to increment red highligted function automatically whenever i paste the formula in below cells?

=INDEX($A$2:$A$21,AGGREGATE(15,6,IF(FREQUENCY(MATCH($A$2:$A$21&"",$A$2:$A$21&"",0),MATCH($A$2:$A$21&"",$A$2:$A$21&"",0))>0,ROW($A$2:$A$21)-ROW($A$2)+1),1))
 
But how will you trigger the formula? I mean you must have some information next to cell in any direction which will trigger the formula.

Regards,
 
please see attach file.
if i want to paste the formula to the cell "B10" it should change the range from "$F$2:$F$6" to " $F$3:$F$6"

=INDEX($A$2:$A$21,AGGREGATE(15,6,IF(FREQUENCY(MATCH($A$2:$A$21&"",$A$2:$A$21&"",0),MATCH($A$2:$A$21&"",$A$2:$A$21&"",0))>0,ROW($A$2:$A$21)-ROW($A$2)+1),$F$2:$F$6))
 

Attachments

  • sample(11).xlsx
    9.8 KB · Views: 5
I think there is some confusion, SMALL function has two argument
1. An Array of numbers
2. k, means which small 1st, 2nd and so on....

So Basically if you drag the formula down the k will increment. You cannot use $F$2:$F$6 as it will give an array of {1;2;3;4;5}, so SMALL function will give 1st,2nd,3rd,4th & 5th SMALL number from array and INDEX will return only 1st result.

So you have to pass any one number in 'k' argument.

Regards,
 
Actually i am working on one small project and thats a requirement. i am trying to design automated report which would extract values from the data. I have asked Sir Narayan to help me in this regard but its in the process.
In the meanwhile im trying myself to figure out how to implement.
Attached is the sample form and i want distinct "PO No." value from the "Data" tab into the "Po No." value in "Report" tab.
But the problem is next "Po No." cell in "Report" tab is way below the first cell :(
I hope you understood my problem
 

Attachments

  • SAMPLE FILE.xlsx
    85.1 KB · Views: 3
See your file. I think your report template will repeat like in your sample it is repeated twice.

Regards
 

Attachments

  • SAMPLE FILE.xlsx
    88.7 KB · Views: 9
Glad you like it & thanks for your kind words. Just write back I had only change ROWS to COUNTIF in k argument of AGGREGATE function & Created a Static named range PO_No. which can be made dynamic.

Other wise you can use direct ref of PO no. in array argument in the formula.

You can also use below function.

=INDEX(PO_No,AGGREGATE(15,6,IF(FREQUENCY(MATCH(PO_No&"",PO_No&"",0),MATCH(PO_No&"",PO_No&"",0))>0,ROW(DATA!$G$2:$G$28)-ROW(DATA!$G$2)+1),COUNTIF(Report!$J$10:J65,J65)))

Where PO_No is named range.

Main thing is changing the ROWS to COUNTIF.

Regards,
 
Sir, i am eager to understand this formula. i would request you to please explain all the functions one by one. I would be grateful to you.
You are awesomee...Thankyou again :)
 
Dear @Somendra Misra Sir,
I tried to extract rest of the data using index, match and countifs function into the sample file. It returns accurate values for the first PO No. But i couldn't figure out how to return values in the below repeated report templates with respect to the Po No.
Could you please have a look at the file and rectify the problem?
 

Attachments

  • SAMPLE FILE(12).xlsx
    87.6 KB · Views: 7
Dear Sir,
It does not return other values (as shown in attached snap) in 2nd, 3rd and so on repeated report templates
 

Attachments

  • Untitled.png
    Untitled.png
    2.7 KB · Views: 3
Check the range in your IFERROR-INDEX-MATCH formula its going from I28:I56 and D28:D56 which is blank in data. Also change L10 in MATCH formula to correct ref.

Regards,
 
Dear Sir,
I want to start over the range from I2:I28 and D2:D28 ignoring first PO No found which is
10210036164-1 on page 2 and similarly for rest of the pages.
Also L10 should automatically be changed to L65 whenever i drag down to generate 100 pages
 
You must log in or register to reply here.
Back
Top