software

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-0000DataA1ValueA1
(555) 000-0000DataA2ValueA2
(555) 555-9770DataA3ValueA3
(555) 555-4464DataA4ValueA4
(555) 555-4464DataA5ValueA5
(555) 555-4720DataA6ValueA6
(555) 555-8823DataA7ValueA7
(555) 555-3834DataA8ValueA8
(555) 555-4125DataA9ValueA9

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?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))),quot;quot;,INDEX(X!A:A,M ATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Copy A1 to C1

Put in D1: =IF(X!A1=quot;quot;,quot;quot;,IF(COUNTIF(X!$A$1:A1,X!A1)gt;1,quot;quot;,ROW ()))

Select A11, 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-0000DataA1ValueA1
(555) 555-9770DataA3ValueA3
(555) 555-4464DataA4ValueA4
(555) 555-4720DataA6ValueA6
(555) 555-8823DataA7ValueA7
(555) 555-3834DataA8ValueA8
(555) 555-4125DataA9ValueA9

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Smohrmanquot; wrote:
gt; Hi Team!
gt;
gt; I have a spreadsheet with three colums of data. The first column contains
gt; records which have occasional phone number duplication- see blelow:
gt;
gt; (555) 000-0000DataA1ValueA1
gt; (555) 000-0000DataA2ValueA2
gt; (555) 555-9770DataA3ValueA3
gt; (555) 555-4464DataA4ValueA4
gt; (555) 555-4464DataA5ValueA5
gt; (555) 555-4720DataA6ValueA6
gt; (555) 555-8823DataA7ValueA7
gt; (555) 555-3834DataA8ValueA8
gt; (555) 555-4125DataA9ValueA9
gt;
gt; What I need to do is (somehwhat) automate the process of filtering or
gt; deleting out all rows which have duplicate data in the first column, but not
gt; second or third columns. I'm sure it's been done...I tried the Excel
gt; out-of-the-box help suggestions and I've had no real luck. Any ideas?
gt;

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:
quot;(In another sheet: Y (say)quot;

I added a sheet to the workbook, then put in A1 the formula you gave:=IF(ISERROR(SMALL($D:$D,ROW(A1))),quot;quot;,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 quot;Update
Values: Xquot;

I get the same thing with each step (quot;Copy A1 to C1quot;) 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?quot;Maxquot; wrote:

gt; One play, using non-array formulas ..
gt;
gt; Assume source data in sheet: X, cols A to C, from row1 down
gt; The key col is col A
gt;
gt; In another sheet: Y (say),
gt;
gt; Put in A1:
gt; =IF(ISERROR(SMALL($D:$D,ROW(A1))),quot;quot;,INDEX(X!A:A,M ATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
gt; Copy A1 to C1
gt;
gt; Put in D1: =IF(X!A1=quot;quot;,quot;quot;,IF(COUNTIF(X!$A$1:A1,X!A1)gt;1,quot;quot;,ROW ()))
gt;
gt; Select A11, fill down to say D50 ?
gt; to cover the max expected extent of data in X
gt;
gt; Cols A to C in Y will auto-return only the unique* lines from X,
gt; all lines neatly bunched at the top
gt; *unique items in the key col A in X
gt;
gt; Note: Refresh the data in X by clearing it with the Delete key
gt; (do not delete the cols), then paste/paste special the new data
gt;
gt; For the posted sample data, the results we'd get would be:
gt;
gt; (555) 000-0000DataA1ValueA1
gt; (555) 555-9770DataA3ValueA3
gt; (555) 555-4464DataA4ValueA4
gt; (555) 555-4720DataA6ValueA6
gt; (555) 555-8823DataA7ValueA7
gt; (555) 555-3834DataA8ValueA8
gt; (555) 555-4125DataA9ValueA9
gt;
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Smohrmanquot; wrote:
gt; gt; Hi Team!
gt; gt;
gt; gt; I have a spreadsheet with three colums of data. The first column contains
gt; gt; records which have occasional phone number duplication- see blelow:
gt; gt;
gt; gt; (555) 000-0000DataA1ValueA1
gt; gt; (555) 000-0000DataA2ValueA2
gt; gt; (555) 555-9770DataA3ValueA3
gt; gt; (555) 555-4464DataA4ValueA4
gt; gt; (555) 555-4464DataA5ValueA5
gt; gt; (555) 555-4720DataA6ValueA6
gt; gt; (555) 555-8823DataA7ValueA7
gt; gt; (555) 555-3834DataA8ValueA8
gt; gt; (555) 555-4125DataA9ValueA9
gt; gt;
gt; gt; What I need to do is (somehwhat) automate the process of filtering or
gt; gt; deleting out all rows which have duplicate data in the first column, but not
gt; gt; second or third columns. I'm sure it's been done...I tried the Excel
gt; gt; out-of-the-box help suggestions and I've had no real luck. Any ideas?
gt; gt;

Smohrman,

in the formula you are using replace the X with the sheet name you are
reading from. If the sheet name contains spaces, enclose it in single
quotes. Otherwise you can include them but they are not necessary.

=IF(ISERROR(SMALL($D:$D,ROW(A1))),quot;quot;,INDEX('My
Data'!A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))

Does this help?

Kostis VezeridesKostis: Thanks for the help !

Smohrman:
As explained in my 1st response / by Kostis,
the suggested set-up presumes your source data is in a sheet: X

Anyway, here's a working sample construct
to illustrate the implementation:
cjoint.com/?fceQb3ocN1
Extracting Unique Lines based on key col A.xls
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

Hi Smohrman,

Here's a macro solution:

Sub DeleteDuplicateRows()
Dim lLastRow As Long
Dim lLastCol As Long
Dim I As Long
Dim J As Long
Dim K As Long
lLastRow = ActiveSheet.UsedRange.Rows.Count - 1
lLastCol = ActiveSheet.UsedRange.Columns.Count - 1
For I = 0 To lLastRow - 1
For J = lLastRow To I 1 Step -1
For K = 0 To lLastCol
If ActiveSheet.Range(quot;A1quot;).Offset(I, K).Value lt;gt;
ActiveSheet.Range(quot;A1quot;).Offset(J, K).Value Then
Exit For
End If
Next K
If K gt; lLastCol Then
ActiveSheet.Range(quot;A1quot;).Offset(J, 0).EntireRow.Delete
End If
Next J
Next I
End Sub

Cheersquot;Smohrmanquot; gt; wrote in message
...
gt; Hi Team!
gt;
gt; I have a spreadsheet with three colums of data. The first column contains
gt; records which have occasional phone number duplication- see blelow:
gt;
gt; (555) 000-0000 DataA1 ValueA1
gt; (555) 000-0000 DataA2 ValueA2
gt; (555) 555-9770 DataA3 ValueA3
gt; (555) 555-4464 DataA4 ValueA4
gt; (555) 555-4464 DataA5 ValueA5
gt; (555) 555-4720 DataA6 ValueA6
gt; (555) 555-8823 DataA7 ValueA7
gt; (555) 555-3834 DataA8 ValueA8
gt; (555) 555-4125 DataA9 ValueA9
gt;
gt; What I need to do is (somehwhat) automate the process of filtering or
gt; deleting out all rows which have duplicate data in the first column, but
not
gt; second or third columns. I'm sure it's been done...I tried the Excel
gt; out-of-the-box help suggestions and I've had no real luck. Any ideas?
gt;
Thank you Max! I had trouble with the syntax at first, but simply renamed my
sheets to X and Y and copied the formula you had in A1 through D1 in the
online example, copied it down and it worked.

I then had to autofilter col D for non-blanks, copy and paste the results to
another sheet to see the actual number of non-duplicated entries from column
A. Is that step necessary or am I missing something?

Thanks- I'm light-years closer than I was...

quot;Maxquot; wrote:

gt; Kostis: Thanks for the help !
gt;
gt; Smohrman:
gt; As explained in my 1st response / by Kostis,
gt; the suggested set-up presumes your source data is in a sheet: X
gt;
gt; Anyway, here's a working sample construct
gt; to illustrate the implementation:
gt; cjoint.com/?fceQb3ocN1
gt; Extracting Unique Lines based on key col A.xls
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---

Thanks macropod...man I wish I knew how to use that macro!

I've tried and tried but haven't had any success implementing macros. No
one seems to have the time to explain it fully.

Nor (I should say) have I found the time to dedicate myself to learning how
to use macros...I know, I know- I'm going to have to do it someday...after I
write the novel inside me, take that trip to Hawaii, etc.

If you're not screaming busy and you could educate me with a series of clear
steps on how to use the macro you've generated I'd certainly be willing to
try again!

Thanks,
Smohrman
quot;macropodquot; wrote:

gt; Hi Smohrman,
gt;
gt; Here's a macro solution:
gt;
gt; Sub DeleteDuplicateRows()
gt; Dim lLastRow As Long
gt; Dim lLastCol As Long
gt; Dim I As Long
gt; Dim J As Long
gt; Dim K As Long
gt; lLastRow = ActiveSheet.UsedRange.Rows.Count - 1
gt; lLastCol = ActiveSheet.UsedRange.Columns.Count - 1
gt; For I = 0 To lLastRow - 1
gt; For J = lLastRow To I 1 Step -1
gt; For K = 0 To lLastCol
gt; If ActiveSheet.Range(quot;A1quot;).Offset(I, K).Value lt;gt;
gt; ActiveSheet.Range(quot;A1quot;).Offset(J, K).Value Then
gt; Exit For
gt; End If
gt; Next K
gt; If K gt; lLastCol Then
gt; ActiveSheet.Range(quot;A1quot;).Offset(J, 0).EntireRow.Delete
gt; End If
gt; Next J
gt; Next I
gt; End Sub
gt;
gt; Cheers
gt;
gt;
gt; quot;Smohrmanquot; gt; wrote in message
gt; ...
gt; gt; Hi Team!
gt; gt;
gt; gt; I have a spreadsheet with three colums of data. The first column contains
gt; gt; records which have occasional phone number duplication- see blelow:
gt; gt;
gt; gt; (555) 000-0000 DataA1 ValueA1
gt; gt; (555) 000-0000 DataA2 ValueA2
gt; gt; (555) 555-9770 DataA3 ValueA3
gt; gt; (555) 555-4464 DataA4 ValueA4
gt; gt; (555) 555-4464 DataA5 ValueA5
gt; gt; (555) 555-4720 DataA6 ValueA6
gt; gt; (555) 555-8823 DataA7 ValueA7
gt; gt; (555) 555-3834 DataA8 ValueA8
gt; gt; (555) 555-4125 DataA9 ValueA9
gt; gt;
gt; gt; What I need to do is (somehwhat) automate the process of filtering or
gt; gt; deleting out all rows which have duplicate data in the first column, but
gt; not
gt; gt; second or third columns. I'm sure it's been done...I tried the Excel
gt; gt; out-of-the-box help suggestions and I've had no real luck. Any ideas?
gt; gt;
gt;
gt;
gt;

Hi Smohrman,

For the most part, using the macro is as simple as:
.. opening up you Excel workbook at the worksheet you want to work on
.. pressing Alt-F11 to access the vba editor
.. clicking Insert|Module
.. copying amp; pasting the code I gave you into that module
.. either:
. pressing F5 to run the macro
. pressing Alt-F11 again to return to the worksheet and:
. pressing Alt-F8 to open the macro listing dialogue box
. selecting the macro and pressing 'Run'

Learning how to code them is somewhat more involved. {:-o}

Cheersquot;Smohrmanquot; gt; wrote in message
...
gt; Thanks macropod...man I wish I knew how to use that macro!
gt;
gt; I've tried and tried but haven't had any success implementing macros. No
gt; one seems to have the time to explain it fully.
gt;
gt; Nor (I should say) have I found the time to dedicate myself to learning
how
gt; to use macros...I know, I know- I'm going to have to do it someday...after
I
gt; write the novel inside me, take that trip to Hawaii, etc.
gt;
gt; If you're not screaming busy and you could educate me with a series of
clear
gt; steps on how to use the macro you've generated I'd certainly be willing to
gt; try again!
gt;
gt; Thanks,
gt; Smohrman
gt;
gt;
gt;
gt; quot;macropodquot; wrote:
gt;
gt; gt; Hi Smohrman,
gt; gt;
gt; gt; Here's a macro solution:
gt; gt;
gt; gt; Sub DeleteDuplicateRows()
gt; gt; Dim lLastRow As Long
gt; gt; Dim lLastCol As Long
gt; gt; Dim I As Long
gt; gt; Dim J As Long
gt; gt; Dim K As Long
gt; gt; lLastRow = ActiveSheet.UsedRange.Rows.Count - 1
gt; gt; lLastCol = ActiveSheet.UsedRange.Columns.Count - 1
gt; gt; For I = 0 To lLastRow - 1
gt; gt; For J = lLastRow To I 1 Step -1
gt; gt; For K = 0 To lLastCol
gt; gt; If ActiveSheet.Range(quot;A1quot;).Offset(I, K).Value lt;gt;
gt; gt; ActiveSheet.Range(quot;A1quot;).Offset(J, K).Value Then
gt; gt; Exit For
gt; gt; End If
gt; gt; Next K
gt; gt; If K gt; lLastCol Then
gt; gt; ActiveSheet.Range(quot;A1quot;).Offset(J, 0).EntireRow.Delete
gt; gt; End If
gt; gt; Next J
gt; gt; Next I
gt; gt; End Sub
gt; gt;
gt; gt; Cheers
gt; gt;
gt; gt;
gt; gt; quot;Smohrmanquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi Team!
gt; gt; gt;
gt; gt; gt; I have a spreadsheet with three colums of data. The first column
contains
gt; gt; gt; records which have occasional phone number duplication- see blelow:
gt; gt; gt;
gt; gt; gt; (555) 000-0000 DataA1 ValueA1
gt; gt; gt; (555) 000-0000 DataA2 ValueA2
gt; gt; gt; (555) 555-9770 DataA3 ValueA3
gt; gt; gt; (555) 555-4464 DataA4 ValueA4
gt; gt; gt; (555) 555-4464 DataA5 ValueA5
gt; gt; gt; (555) 555-4720 DataA6 ValueA6
gt; gt; gt; (555) 555-8823 DataA7 ValueA7
gt; gt; gt; (555) 555-3834 DataA8 ValueA8
gt; gt; gt; (555) 555-4125 DataA9 ValueA9
gt; gt; gt;
gt; gt; gt; What I need to do is (somehwhat) automate the process of filtering or
gt; gt; gt; deleting out all rows which have duplicate data in the first column,
but
gt; gt; not
gt; gt; gt; second or third columns. I'm sure it's been done...I tried the Excel
gt; gt; gt; out-of-the-box help suggestions and I've had no real luck. Any ideas?
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
Hi Macropod,

When running the macro, i get an error:

quot;Compile Error: Syntax Errorquot;

The error line highlighted in the VB editor is:

quot;If ActiveSheet.Range(quot;A1quot;).Offset(I, K).Value lt;gt;quot;

Is there anything I should customize, or anything you can see wrong here?
I'm excited about the chance of getting this working!

Thanks in advance

quot;macropodquot; wrote:

gt; Hi Smohrman,
gt;
gt; For the most part, using the macro is as simple as:
gt; .. opening up you Excel workbook at the worksheet you want to work on
gt; .. pressing Alt-F11 to access the vba editor
gt; .. clicking Insert|Module
gt; .. copying amp; pasting the code I gave you into that module
gt; .. either:
gt; . pressing F5 to run the macro
gt; . pressing Alt-F11 again to return to the worksheet and:
gt; . pressing Alt-F8 to open the macro listing dialogue box
gt; . selecting the macro and pressing 'Run'
gt;
gt; Learning how to code them is somewhat more involved. {:-o}
gt;
gt; Cheers
gt;
gt;
gt; quot;Smohrmanquot; gt; wrote in message
gt; ...
gt; gt; Thanks macropod...man I wish I knew how to use that macro!
gt; gt;
gt; gt; I've tried and tried but haven't had any success implementing macros. No
gt; gt; one seems to have the time to explain it fully.
gt; gt;
gt; gt; Nor (I should say) have I found the time to dedicate myself to learning
gt; how
gt; gt; to use macros...I know, I know- I'm going to have to do it someday...after
gt; I
gt; gt; write the novel inside me, take that trip to Hawaii, etc.
gt; gt;
gt; gt; If you're not screaming busy and you could educate me with a series of
gt; clear
gt; gt; steps on how to use the macro you've generated I'd certainly be willing to
gt; gt; try again!
gt; gt;
gt; gt; Thanks,
gt; gt; Smohrman
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;macropodquot; wrote:
gt; gt;
gt; gt; gt; Hi Smohrman,
gt; gt; gt;
gt; gt; gt; Here's a macro solution:
gt; gt; gt;
gt; gt; gt; Sub DeleteDuplicateRows()
gt; gt; gt; Dim lLastRow As Long
gt; gt; gt; Dim lLastCol As Long
gt; gt; gt; Dim I As Long
gt; gt; gt; Dim J As Long
gt; gt; gt; Dim K As Long
gt; gt; gt; lLastRow = ActiveSheet.UsedRange.Rows.Count - 1
gt; gt; gt; lLastCol = ActiveSheet.UsedRange.Columns.Count - 1
gt; gt; gt; For I = 0 To lLastRow - 1
gt; gt; gt; For J = lLastRow To I 1 Step -1
gt; gt; gt; For K = 0 To lLastCol
gt; gt; gt; If ActiveSheet.Range(quot;A1quot;).Offset(I, K).Value lt;gt;
gt; gt; gt; ActiveSheet.Range(quot;A1quot;).Offset(J, K).Value Then
gt; gt; gt; Exit For
gt; gt; gt; End If
gt; gt; gt; Next K
gt; gt; gt; If K gt; lLastCol Then
gt; gt; gt; ActiveSheet.Range(quot;A1quot;).Offset(J, 0).EntireRow.Delete
gt; gt; gt; End If
gt; gt; gt; Next J
gt; gt; gt; Next I
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Cheers
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Smohrmanquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi Team!
gt; gt; gt; gt;
gt; gt; gt; gt; I have a spreadsheet with three colums of data. The first column
gt; contains
gt; gt; gt; gt; records which have occasional phone number duplication- see blelow:
gt; gt; gt; gt;
gt; gt; gt; gt; (555) 000-0000 DataA1 ValueA1
gt; gt; gt; gt; (555) 000-0000 DataA2 ValueA2
gt; gt; gt; gt; (555) 555-9770 DataA3 ValueA3
gt; gt; gt; gt; (555) 555-4464 DataA4 ValueA4
gt; gt; gt; gt; (555) 555-4464 DataA5 ValueA5
gt; gt; gt; gt; (555) 555-4720 DataA6 ValueA6
gt; gt; gt; gt; (555) 555-8823 DataA7 ValueA7
gt; gt; gt; gt; (555) 555-3834 DataA8 ValueA8
gt; gt; gt; gt; (555) 555-4125 DataA9 ValueA9
gt; gt; gt; gt;
gt; gt; gt; gt; What I need to do is (somehwhat) automate the process of filtering or
gt; gt; gt; gt; deleting out all rows which have duplicate data in the first column,
gt; but
gt; gt; gt; not
gt; gt; gt; gt; second or third columns. I'm sure it's been done...I tried the Excel
gt; gt; gt; gt; out-of-the-box help suggestions and I've had no real luck. Any ideas?
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;