On Friday, June 5, 2015 at 11:10:16 AM UTC+5:30, Nitya Satheesh wrote:
On Thursday, June 4, 2015 at 8:53:59 PM UTC+5:30, Cameron Mitchell wrote:
Hi,

try this:

Highlight both columns and use the "Remove Duplicates" button in the "Data" ribbon. Select both columns, and click "ok"

hope that helps,

Cameron

Hi Cameron,

I tried that and got"no duplicates found". I don't know why that happened.

Hi Cameron!
I tried that again and it worked! Thanks a lot !

Here is a cell formula approach
A names the range of data in A (numerics), and likewise B (assuming strings not containing the | character of lengths up to 200 in this example)
Array Enter (CtrlShiftEnter) the following formula in C2 (or any available cell not starting in row 1)
=IFERROR(INDEX(A&"|"&B,MATCH(,COUNTIF(O$1:O1,""&IF (A&"|"&B=0,"",A&"|"&B)),)),"")
and fill down
This will produce
={"1|a";"1|b";"3|b";"3|c";"4|b";"5|d";"6|e";"7|d "}
Then to split into two columns D and E
D2=VALUE(TRIM(LEFT(SUBSTITUTE($C2,"|",REPT(" ",200)),200)))
E2=TRIM(RIGHT(SUBSTITUTE(K2,"|",REPT(" ",200)),200))
Fill D2:E2 Down
This produces
={1,"a";1,"b";3,"b";3,"c";4,"b";5,"d";6,"e";7,"d"}
Having these formulas will allow automatic duplicate removal whenever new data is entered in range A and range B.
Using the RibbonDataData ToolsRemove Duplicates is a manual process that must be repeated with new data. However, to another user of the workbook (or even yourself months later), that the Remove Duplicates functionality has been used, and is to be used with new data, is not apparent without substantial documentation.
Regards
Brian

To deal with the dates, use my formula method and then use the A|B column to lookup the dates.
Suppose you have, in A2:C20
{1,"a",40913,"1|a";1,"a",40913,"1|b";1,"a",40944," 2|a";1,"a",40973,"2|c";1,"b",40943,"3|c";1,"b",409 43,"4|d";1,"b",40972,"";2,"a",40970,"";2,"a",40970 ,"";2,"a",41001,"";2,"c",41031,"";2,"c",41064,"";3 ,"c",41094,"";3,"c",41125,"";3,"c",41156,"";3,"c", 41186,"";4,"d",40971,"";4,"d",40971,"";4,"d",41002 ,""}
Then naming the result of my first formula C_
in E2 array enter and fill down
=IFERROR(INDEX(C_,MATCH(D2,A&"|"&B,)),"")
This produces the required dates
={40913;40943;40970;41031;41094;40971}
Let me know if you would like a workbook showing the example. The above may look dauting for you to interpret and construct in your workbook.
Regards
Brian

Should read name the Dates _C
s/a dauting
s/b daunting
Regards
Brian

On Tuesday, June 16, 2015 at 10:19:05 AM UTC+5:30, Brian Canes wrote:
Here is a cell formula approach
A names the range of data in A (numerics), and likewise B (assuming
strings not containing the | character of lengths up to 200 in this
example)
Array Enter (CtrlShiftEnter) the following formula in C2 (or any
available cell not starting in row 1)
=IFERROR(INDEX(A&"|"&B,MATCH(,COUNTIF(O$1:O1,""&IF (A&"|"&B=0,"",A&"|"&B)),)),"")
and fill down
This will produce
={"1|a";"1|b";"3|b";"3|c";"4|b";"5|d";"6|e";"7|d "}
Then to split into two columns D and E
D2=VALUE(TRIM(LEFT(SUBSTITUTE($C2,"|",REPT(" ",200)),200)))
E2=TRIM(RIGHT(SUBSTITUTE(K2,"|",REPT(" ",200)),200))
Fill D2:E2 Down
This produces
={1,"a";1,"b";3,"b";3,"c";4,"b";5,"d";6,"e";7,"d"}
Having these formulas will allow automatic duplicate removal whenever
new data is entered in range A and range B.
Using the RibbonDataData ToolsRemove Duplicates is a manual process
that must be repeated with new data. However, to another user of the
workbook (or even yourself months later), that the Remove Duplicates
functionality has been used, and is to be used with new data, is not
apparent without substantial documentation.
Regards
Brian




--
Brian Canes

Thanks Brian! This is great for future use of the worksheet!