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

Help for small formula. INDEX and MATCH

B

bhasoriya

Member
Hello Friends,

Need your help for a small formula in Index and match.


In sheet 1

B C

1-Mar-12 Jani

1-Mar-12 Jani

1-Mar-12 Mehta

1-Mar-12 Mehta

1-Mar-12 Patel

1-Mar-12 Thakkar


In sheet 2

A2 has date (1-Mar-12)

B2 has =INDEX(Sheet1!C:C, MATCH($A$2,Sheet1!B:B,0)) (Result is Jani)

If i copy this formula in B3 then result would be exclude duplicate names and should appear next new name.


Result would be in

B3 Mehta

B4 Patel

B5 Thakkar


Thanks a lot
 
Hi Bhasoriya ,


It's not so simple !


Go through the following links :


1. http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/


2. http://www.get-digital-help.com/2009/04/16/create-unique-list-from-column-where-an-adjacent-column-meets-criteria/


Narayan
 
Hi Bhasoriya ,


Assuming your data is from A1 to B6 (at sheet1)


At sheet2 you have date(1-Mar-12) at A2:


At B2, try this:

=IFERROR(INDEX(Sheet1!B:B,MATCH(0,COUNTIF($A$1:$A1,Sheet1!B:B)+IF(Sheet1!$A:$A<>Sheet2!$A$2, 1, 0),0)),"")....CTRL+SHIFT+ENTER (to enter it as an array formula) and copy it down as needed.


For detail explanation, please refer the below link to understand how the formula works..

Reference: http://www.get-digital-help.com/2009/06/27/how-to-create-a-unique-distinct-list-where-other-columns-meet-two-criteria/


Post back in case you require any clarification.


Kaushik
 
Hello kaushik03,


In sheet2, B2 is showing Result as "Jani" but in

B3,B4 & B5 are still showing "jani"


Is there another way to exclude duplicate names from sheet1 column B in result.


Result should be in

B3 Mehta

B4 Patel

B5 Thakkar


Thanks for helping :)
 
Hello Bhasoriya


I Think This formula will help you, otherwise please upload a file for better understanding


formula is


=IF(COUNTIF(Sheet1!B:B,$A$2)<ROWS($B$2:B2),"",INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$B$1:$B$10=$A$2,ROW(Sheet1!$B$1:$B$10)),ROW(A1))))


after insert the formula when you are going to press enter use the array method (ctrl+shift+enter) and drag down


With Regards


SP
 
Hello sgmpatnaik,


I Have upload here file for example.


https://rapidshare.com/files/4060717424/Resouce used.xlsx


Thanks a lot... again
 
Hi Bhasoriya ,


Check out the file here :


http://speedy.sh/Db8Uy/Resource-used.xlsx


Please note that the formulae are borrowed from the links I had posted earlier , 2 days back.


Also , the formulae will not work if the date is entered in only one row on the second sheet ; for the formulae to work , the date has to be repeated.


Narayan
 
Hello Narayank991,


Yes i can repeat the DATE in Sheet 2 from A2 to A26..., as you mention here. But If i am entering same formula in B6 to B25.... It is repeating the name.. It should not be repeated. If name appear once. then He would not be repeated again for that date.


Thanks Narayank991, I had tried the formulas from

1. http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

2. http://www.get-digital-help.com/2009/04/16/create-unique-list-from-column-where-an-adjacent-column-meets-criteria/


But here there are bulk of formulas.. i was confused..
 
Hi Bhasoriya ,


Have you checked the file I uploaded ? There is no repetition there.


Narayan


EDIT : Sorry , I'll get back to you on this.
 
You must log in or register to reply here.
Back
Top