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

#### Asfandyar Ali

##### Member
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))

#### Somendra Misra

##### Excel Ninja
What is the content of cells in between?

Regards,

#### Asfandyar Ali

##### Member
blank or want to ignore in between cells

#### Somendra Misra

##### Excel Ninja
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,

#### Asfandyar Ali

##### Member
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

• 9.8 KB Views: 4

#### Somendra Misra

##### Excel Ninja
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,

#### Asfandyar Ali

##### Member
Sir, but we are not using SMALL function anywhere in formula. am i right?

#### Somendra Misra

##### Excel Ninja
Kindly go through the first argument of AGGREGATE function. It's SMALL function.

Regards,

#### Asfandyar Ali

##### Member
O yeah sorry. but how can i resolve my problem as mentioned in sample(11) file is there any way to resolve it

#### Somendra Misra

##### Excel Ninja
OK let me ask you my question in a different way?

Why you want the result only in cell B2, B10 and B21?

Regards,

#### Asfandyar Ali

##### Member
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

• 85.1 KB Views: 2

#### Somendra Misra

##### Excel Ninja
See your file. I think your report template will repeat like in your sample it is repeated twice.

Regards

#### Attachments

• 88.7 KB Views: 7

#### Asfandyar Ali

##### Member
OMG. GENIUS YOU ARE. Let me see what u've done. i will get back to you if i face any issue. bundle of thanks bro

#### Somendra Misra

##### Excel Ninja
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,

#### Asfandyar Ali

##### Member
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

#### Somendra Misra

##### Excel Ninja
Just see the edited comment #39. If you have problem in understanding write back.

Regards,

#### Asfandyar Ali

##### Member
Ok Bro. Thanks alot )

#### Somendra Misra

##### Excel Ninja
Welcome Back Anytime!!!

Regards,

#### Asfandyar Ali

##### Member
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

• 87.6 KB Views: 6

#### Somendra Misra

##### Excel Ninja
Hi @Asfandyar Ali

I don' t see any error in the file. It had split first and second PO#.

Regards,

#### Asfandyar Ali

##### Member
Dear Sir,
It does not return other values (as shown in attached snap) in 2nd, 3rd and so on repeated report templates

#### Attachments

• 2.7 KB Views: 3

Regards,

#### Asfandyar Ali

##### Member
Please check if its clear now

#### Attachments

• 95.9 KB Views: 4

#### Somendra Misra

##### Excel Ninja
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,

#### Asfandyar Ali

##### Member
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