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

Extract Unique record by using formula

trprasad78

trprasad78

Member
I need to extract unique record from list of Job number.
I want this only using excel formula.
I know we can do this using pivot, or remove duplicates, but i want to extract unique record automatically another location using formula.
even if added record in source record, it has to added in result data.

I have attached sample file for your reference.

Please do the needful.

Thank you
 

Attachments

  • sample.xlsx
    9.2 KB · Views: 17
First make dynamic named range for your column A.
Either as table or formula method.
Named range formula:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Then using that you can write formula.
E2:
=INDEX(lstJobN,MATCH(0,INDEX(COUNTIF($E$1:E1,lstJobN),0,0),0))

Copy down until you see #N/A.

It won't be truly dynamic as you need to have formula populated prior to any additional Job no. are pulled. But if you have enough range populated with formula it will work.

If you don't want #N/A showing up in pre-populated range. Just nest it in IFERROR.
 
Here's my explanation.
MATCH(0,INDEX(COUNTIF($E$1:E1,lstJobN),0,0),0)
This portion is used to return first row in lstJobN where there is no match in same column (E).

Lets break it down, and look at E3.
COUNTIF($E$1:E2,lstJobN) part is simply, checking if each value in lstJobN is found in $E$1:E2 range. It will normally evaluate to 1, but since it's nested in INDEX formula, it will return array of...
INDEX({1;0;0;0;0;0;0;0;1;1;0;0;0},0,0)
In INDEX formula, row# 0 and column# 0 represents entire rows and columns.
EX. INDEX($A$1:$B$100,0,0) is equivalent of $A$1:$B$100
INDEX($A$1:$B$100,0,1) is equivalent of $A$1:$A$100

Therefore Match part will become...
MATCH(0,{1;0;0;0;0;0;0;0;1;1;0;0;0},0)

First 0 occurs in row2 and INDEX(lstJobN, 2) resolves to "JB2523".

Inside INDEX is needed to return array from COUNTIF part (instead of single result).
 
Last edited:
Thank you explain in detail, still i am not clear about below part.

i tried =COUNTIF($E$1:E1,lstJobN) formula but i am getting 0 as out put.
if you have any link to understand countif nested in INdex formula or please explain how array working here.

I know index function and countif function, but i not able to understand how this giving this kind of out put.

Chihiro said:
Lets break it down, and look at E3.
COUNTIF($E$1:E2,lstJobN) part is simply, checking if each value in lstJobN is found in $E$1:E2 range. It will normally evaluate to 1, but since it's nested in INDEX formula, it will return array of...
INDEX({1;0;0;0;0;0;0;0;1;1;0;0;0},0,0)
Click to expand...
 
Hi ,

It has nothing to do with COUNTIF being nested within INDEX.

When the COUNTIF function is used in its standard syntax , the first parameter is an array or range , and the second parameter is a scalar , a single value.

For example , suppose you have defined the range A3:A11 as a named range called List. Suppose this range consists of the following numbers :

1 , 8 , 7 , 9 , 9 , 3 , 9 , 4 , 7

Now , suppose we have the value 9 entered in cell D1.

We can put in the following formula :

=COUNTIF(List , D1)

and it will display the result as 3.

But , suppose we interchange the order of the two parameters , and put in the formula :

=COUNTIF(D1 , List)

You will see the value 1 displayed in the cell where this formula has been entered , but when you press F9 in the cell , you will see :

{0;0;0;1;1;0;1;0;0}

What this is doing is it is comparing D1 with every element of List , and outputting 0 if the two are not the same , and 1 if they are.

Next , suppose we enter 3 in cell D2 , and enter the formula :

=COUNTIF(D1:D2 , List)

If you press F9 in the cell , you will now see :

{0;0;0;1;1;1;1;0;0}

where the 1s signify either a 3 or a 9.

The 0s signify where the values are neither 3 nor 9.

How do we use this array ?

Let us see what happens if we enter this formula as follows :

=INDEX(List , MATCH(0 , COUNTIF(E$1:E1 , List) , 0))

This is an array formula , to be entered using CTRL SHIFT ENTER.

It is to be entered in cell E2.

So what happens in E2 ?

The COUNTIF function finds none of the numbers in the list in cell E1 , which means it will return an array of zeros :

{0;0;0;0;0;0;0;0;0}

The MATCH function looks for a zero , and finds it in the very first element of the array ; taking the first element of the named range List , the INDEX function returns the first value from the named range List , which is 1.

Thus E2 will have the value 1.

What happens if this formula is copied down to E3 ?

The COUNTIF function is now :

COUNTIF(E$1:E2 , List)

which means that the returned value of 1 is now included in the first parameter. Thus , what the above formula will return is the array :

{1;0;0;0;0;0;0;0;0}

Thus , the MATCH function will now return 2 , since the zero is now in the second place.

The INDEX function will therefore return the second element of the named range List.

Thus , as we copy this formula down , each of the elements of the named range List will be returned , and since the COUNTIF function will count all occurrences , when ever a duplicate is encountered , it will skip that element and move to the next element.

What we will have finally is a list of all the distinct values in the named range List.

Now , to the main question : why do we have the construct :

=INDEX(List,MATCH(0,INDEX(COUNTIF(F$7:F8,List),),0))

The highlighted INDEX function eliminates the need to enter the overall formula as an array formula , which means with this usage , you do not need to use CTRL SHIFT ENTER. The formula is a non-array formula , and just ENTER will do.

Narayan
 
You must log in or register to reply here.
Back
Top