Trying to extract Duplicates using VLookup

[suell]

Hi, I have a main list of 200 entries. The headings are ID, Date, value. The ID and Date will be duplicated at various times, however the value will be different. E.g.

ID…..Date……Value

101…01/01/13…89

101…01/01/13…78

102…02/03/13…66

102…02/03/13…50

I was given a smaller unique list of IDs and Dates and was told to extract the related value from the main list for both the ID And Date. I tried a vlookup and also a concatenation of the ID and Date but it always duplicates the value. I get the following:

101...01/01/13...89

101...01/01/13...89

Is there a way to extract duplicate IDs and Dates and their relevant different values?

 

[Debraj]

Hi Suell,


can you please try below formula..

=IFERROR(INDEX(ValueRange,SMALL(IF((IDRange=GivenID)*(DateRange=GivenDate),ROW(ValueRange)-1,""),ROW(A1))),"")


Confirm the formula by [b]Ctrl + Shift + Enter [/b] not just Enter..


which will looks like..

{=IFERROR(INDEX($C$2:$C$5,SMALL(IF(($A$2:$A$5=$F$2)*($B$2:$B$5=$G$2),ROW($C$2:$C$5)-1,""),ROW(A1))),"")}

For more detail check below post..

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Regards,

Deb

 

[suell]

Hi,I tried the array formula but it did not work. just gave me empty cells. I will read the link you provided. Thanks...

 

[Debraj]

Hi Suell!


It may be my fault, that I have not read your query corerctly

* I am unable to setup how you setup..

extract duplicate IDs and Dates and their relevant different values

Can you please upload a sample file.. with expected output..


In the meanwhile...

Have a look attached file...


https://dl.dropboxusercontent.com/u/78831150/Excel/Trying%20to%20extract%20Duplicates%20using%20VLookup%28Suell%29.xlsx


Regards,

Deb

 

[suell]

Hi Debraj Roy, sorry for delay,fell asleep..Having trouble uploading sample. Your attached example is nearly what I want. The way you show the Values hould be underneath each other like the main list. the result list should replicate the main list. Love your formula though.

 

[suell]

HiDebraj Roy, here is an example of what the data should look like:

ID…..Date……Value (Main List)

101…01/01/13…89

101…01/01/13…78

102…02/03/13…66

102…02/03/13…50

104…03/03/13…99


ID…..Date (unique list given to me)

101…01/01/13

102…02/03/13


ID…..Date……Value (this is how it should look)

101…01/01/13…89

101…01/01/13…78

102…02/03/13…66

102…02/03/13…50


Thanks

 

[Sajan]

Hi Suell,

Here is one solution for your question:


For ease of reference, I have assumed the following Named ranges:

IDList refers to the IDs in the Main list, and is assumed to be in column A

DateList refers to the dates in the Main list, and is assumed to be in column B

ValueList refers to the values in the Main list, and is assumed to be in column C


UniqueID refers to the IDs in the Unique list

UniqueDate refers to the dates in the Unique list


I got the following results, setup in cells E1:G5

[pre]

ID	Date	       Value
101	1/1/2013	89
101	1/1/2013	78
102	2/3/2013	66
102	2/3/2013	50

[/pre]
To get the results, use the following formula (shown for cell E2):

=IFERROR(INDEX(A:A, SMALL(IFERROR(IF(MATCH(IDList&DateList, UniqueID&UniqueDate, 0), ROW(IDList)), FALSE), ROWS($E$2:$E2))), "---")

enter with Ctrl + Shift + Enter


Copy down to additional rows, until you get "---"; Copy to the next two columns also.

If you want your results on a different sheet, remember to prefix the A:A above with the name of the sheet with the Main list.


Cheers,

Sajan.

 

[suell]

Hi Sajan, thanks for replying....I have the main list in A1:C6 and the unique list in A9:B11. I put your formula in E2,is that right? I get ######.

When you say(MATCH(IDList&DateList, UniqueID&UniqueDate, do you use the "&" as stated.

 

[Sajan]

Hi suell,

The formula in cell E2 needs to be entered with Ctrl + Shift + Enter.


Yes, the "&" is the concatenation operator, and it is concatenating the two values (ID and date)


Also, remember to give the Names as indicated for your ranges. (For example, A2:A6 would be named "IDList" -- assuming that A1 has a header label.)


Let me know if you run into any issues.


Cheers,

Sajan.

 

[suell]

Hi Sajan, I did ctrl shift enter originally but I am getting the ID "101" as the result. I do have A2:A6 as the named IDLIST and the unique lists are named correctly. I have the unique list in E2:F3 and G2 is where I am keying the formula in. Am I nearly there....

 

[Sajan]

Hi suell,

I think I see what is causing the issue for you.


You are trying to get the value next to the uniqueID and UniqueDate in the second list, while I left that second list alone, and created a third list starting with E2.


The reason I took the approach of recreating the uniqueID and UniqueDate in a third list is because you may need multiple rows in the results for the same ID and Date (which is indeed the case for your sample data).


The 101 you are getting in E2 is correct. When you copy the formula to cell F2, you should get 1/1/2013. When you copy the formula to cell G2, you should get 89.


Hope this makes sense.


Cheers,

Sajan.

 

[suell]

Hi Sajan,,,,sorry I am a bit confused now....I know the the main list is in A2:A6, the unique list is in A9:B11, do I enter the formula in E2?

 

[Sajan]

Hi suell,

the main list is in cells A1:C6

IDList is A2:A6

DateList is B2:B6

ValueList is C2:C6

(A1:C1 is the heading for the main list)


The second list (which is the Unique list) is in cells A9:B11

UniqueID is A10:A11

UniqueDate is B10:B11

(A9:B9 is the heading for the unique list)


The third list (which is the results list) is in cells E1:G5

cells E1:G1 is the heading

Starting on cell E2 is where you would use the formula.

The same formula returns the ID, when used in column E, Date when used in column F, and Value when used in column G.

Copy the formula in cell E2 to the cells in E2:G5.


Please note that the results list recreates the ID, Date and Values.


Cheers,

Sajan.

 

[suell]

Hi,forgive my questions....I am following your method to the letter...I am getting hashes in E2 where I am putting the formula....I am doing exactly as you have instructed....=IFERROR(INDEX(A:A, SMALL(IFERROR(IF(MATCH(IDList&DateList, UniqueID&UniqueDate, 0), ROW(IDList)), FALSE), ROWS($E$2:$E2))), "---")

enter with Ctrl + Shift + Enter.....I have named the ranges correctly...Can i ask a question why do you do this ROWS($E$2:$E2) in the formula?

 

[Sajan]

Hi suell,

I will post a sample workbook shortly. That might make it easy to see the formulas in action.


ROWS($E$2:$E2) is a technique to get the value 1, 2, 3, etc. in each row. In this case, we are trying to get the first value when on E2, the second value when on E3, and so on.


Since I do not have access to file sharing sites on the computer I am on right now, I will need to login to a different computer. I will post a sample file in the next few minutes.


Cheers,

Sajan.

 

[Sajan]

Hi suell,

One more thing... I am using Excel 2010.

Which version of Excel are you using?

 

[suell]

I am also using 2010.

 

[Sajan]

Hi suell,

Following is a link to the sample workbook:

http://speedy.sh/ppb5X/Chandoo-Suell-Extract-Duplicates.xlsx


Let me know if you have any questions.


Cheers,

Sajan.

 

[suell]

Thanks Sajan,,,I had exactly as you had....it was just the columns needed to be widened....which is strange because my font is Arial 11 and for some strange reason the columns needed to be wider than usual otherwise I would get the hashes....thank you so much for your time and patience...I will take time now to understand and break down your formula for my understanding....thanks again....

 

[Sajan]

Hi suell,

Glad you were able to figure out the problem. Thanks for the feedback. Glad to help.


If you need any help with the formulas, please let me know.


Cheers,

Sajan.