Max,
I tried to follow but am still experiencing problems.
I am assuming that you want me to add another sheet to the work book:
"(In another sheet: Y (say)"
I added a sheet to the workbook, then put in A1 the formula you gave:
=IF(ISERROR(SMALL($D:$D,ROW(A1))),"",INDEX(X!A:A,M ATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Immediately I get a file browse dialog box that opens up titled "Update
Values: X"
I get the same thing with each step ("Copy A1 to C1") etc. I must be
missing some part of your instructions. I don't see how the formula in the
new sheet in A1 would be tied to the data in another sheet. Can you clarify?
"Max" wrote:
One play, using non-array formulas ..
Assume source data in sheet: X, cols A to C, from row1 down
The key col is col A
In another sheet: Y (say),
Put in A1:
=IF(ISERROR(SMALL($D:$D,ROW(A1))),"",INDEX(X!A:A,M ATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Copy A1 to C1
Put in D1: =IF(X!A1="","",IF(COUNTIF(X!$A$1:A1,X!A1)1,"",ROW ()))
Select A1:D1, fill down to say D50 ?
to cover the max expected extent of data in X
Cols A to C in Y will auto-return only the unique* lines from X,
all lines neatly bunched at the top
*unique items in the key col A in X
Note: Refresh the data in X by clearing it with the Delete key
(do not delete the cols), then paste/paste special the new data
For the posted sample data, the results we'd get would be:
(555) 000-0000 DataA1 ValueA1
(555) 555-9770 DataA3 ValueA3
(555) 555-4464 DataA4 ValueA4
(555) 555-4720 DataA6 ValueA6
(555) 555-8823 DataA7 ValueA7
(555) 555-3834 DataA8 ValueA8
(555) 555-4125 DataA9 ValueA9
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Smohrman" wrote:
Hi Team!
I have a spreadsheet with three colums of data. The first column contains
records which have occasional phone number duplication- see blelow:
(555) 000-0000 DataA1 ValueA1
(555) 000-0000 DataA2 ValueA2
(555) 555-9770 DataA3 ValueA3
(555) 555-4464 DataA4 ValueA4
(555) 555-4464 DataA5 ValueA5
(555) 555-4720 DataA6 ValueA6
(555) 555-8823 DataA7 ValueA7
(555) 555-3834 DataA8 ValueA8
(555) 555-4125 DataA9 ValueA9
What I need to do is (somehwhat) automate the process of filtering or
deleting out all rows which have duplicate data in the first column, but not
second or third columns. I'm sure it's been done...I tried the Excel
out-of-the-box help suggestions and I've had no real luck. Any ideas?