Formula or Macro??

[nicole327]

I have a spreadsheet like the one below.

I want to be able to create a macro or formula to remove rows of data that have less than 3 matching names. Example: I would want something to be able to remove Doe,Jen...Matthews, Sarah...etc...My excel sheet has over 700 rows that I would have to go through and that would take forever.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Smith,John[/TD]
[TD]$1,896.56[/TD]
[/TR]
[TR]
[TD]Smith,John[/TD]
[TD]$4,678.45[/TD]
[/TR]
[TR]
[TD]Smith,John[/TD]
[TD]$2,467.89[/TD]
[/TR]
[TR]
[TD]Doe,Jen[/TD]
[TD]$3,678.21[/TD]
[/TR]
[TR]
[TD]White,Paul [/TD]
[TD]$6,496.12[/TD]
[/TR]
[TR]
[TD]White,Paul[/TD]
[TD]$1,339.74[/TD]
[/TR]
[TR]
[TD]White,Paul[/TD]
[TD]$2,892.99[/TD]
[/TR]
[TR]
[TD]Matthews,Sarah[/TD]
[TD]$4,294.77[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Try this macro (cannot delete rows with formulas). Assumes your name list begins in A2.

Sub GoneSouthIfLessThanThree()
'assumes names are in col A starting in A2 - change to suit
Dim R As Range, c As Range
Set R = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False
For Each c In R
    If Application.CountIf(R, c.Value) < 3 Then c.Value = "#N/A"
Next c
On Error Resume Next
R.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

JoeMo is right that a formula cannot delete rows. The best you could do with formulas is to create a new list that omitted the ones with less than three matching names. Formula below is copied across and down.

Excel Workbook
	A	B	C	D	E
1	Name	Amount		Name	Amount
2	Smith,John	1,896.56		Smith,John	1,896.56
3	Smith,John	4,678.45		Smith,John	4,678.45
4	Smith,John	2,467.89		Smith,John	2,467.89
5	Doe,Jen	3,678.21		White,Paul	6,496.12
6	White,Paul	6,496.12		White,Paul	1,339.74
7	White,Paul	1,339.74		White,Paul	2,892.99
8	White,Paul	2,892.99
9	Matthews,Sarah	4,294.77
At least 3

If you are interested in an alternative macro approach, here is a non-looping way to implement the deletions.

Sub AtLeast3()
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(1))
    .Value = Evaluate(Replace("if(#="""",1,if(countif(#,#)<3,1,#))", "#", .Address))
    .SpecialCells(xlConstants, xlNumbers).EntireRow.Delete
  End With
  Application.ScreenUpdating = True
End Sub

 

[nicole327]

Thank you both for responding! Now here is another question...Where do i insert the formula to test it? And..I am new to macros so how do i go about creating a new one? Thank you!

 

[Peter_SSs]

Where do i insert the formula to test it?

My post shows you where to put the formula (at least for the layout I showed) and tells you where to copy it.

And..I am new to macros so how do i go about creating a new one? Thank you!

To implement a macro ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code given into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro by name & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[nicole327]

Thank you SO much!! It worked!!!

*Can I save this macro to use each week?

 

[Peter_SSs]

Thank you SO much!! It worked!!!

Good news. You're welcome.

*Can I save this macro to use each week?

Yes, you can save it in your 'Personal' macro workbook and it will be available any time.
To do that, open the vba window as before (Alt+F11) and see if, in the left pane, there is a VBAProject(PERSONAL.XLSB)

If not go back to your workbook (any workbook) and on the Developer ribbon tab (if you don't have that Customize the Ribbon... by right-clicking on it and in the right pane check the 'Developer' box, OK) choose Record Macro. In the dialog box that pops up choose 'Store macro in:' Personal Macro Workbook, OK. Select any cell and hit the Delete key then choose 'Stop Recording' (Developer ribbon tab again).
Now in the vba window you should have a VBAProject(PERSONAL.XLSB)

Double-click the VBAProject(PERSONAL.XLSB) then proceed from step 2 in post 5. make sure you save changes to 'Personal' if prompted when closing Excel.