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!