A formula to delete duplicates from a filtered list

[debeast]

I have been trying to get a fix for this issue for the past 2 days.

I have an excel file that runs to around 54,645 rows showing individual expenses for FY 2015 month-wise. I need to replace duplicate expenses with zero only in one month (June). The list is filtered and it is best that it stays that way (sorted to different columns).

How can I remove the duplicates that appear in this month alone?

Any help is much appreciated.

 

[Special-K99]

Formulas can't delete cells.

 

[debeast]

Formulas can't delete cells.

Well, I got to replace the duplicates to zero using "+IF(AND". I have the results that I want on another sheet.

However, since this is in another sheet and when I try to copy the values to my destination sheet, the values are pasted on the hidden cells too!

Is there a workaround to paste values only on visible cells?

 

[Akuini]

Is there a workaround to paste values only on visible cells?

I answered this question in another thread.
http://www.mrexcel.com/forum/excel-...data-filtered-list-another-filtered-list.html

Here is the code:

Sub CopyVisibleToVisible()
'Copy paste(value):
'from filtered to filtered range
'from filtered to unfiltered range
'from unfiltered to filtered range
'Not work on hidden column

    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
    
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Copy Range :", Title, rngA.Address, Type:=8)
    
    Set rngB = Application.InputBox("Paste Range (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.count
    rc = rngA.Columns.count
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
    
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.RowHeight <> 0
    Next
    
    Application.Goto rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

 

[debeast]

I answered this question in another thread.
http://www.mrexcel.com/forum/excel-...data-filtered-list-another-filtered-list.html

Here is the code:

Sub CopyVisibleToVisible()
'Copy paste(value):
'from filtered to filtered range
'from filtered to unfiltered range
'from unfiltered to filtered range
'Not work on hidden column

    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
    
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Copy Range :", Title, rngA.Address, Type:=8)
    
    Set rngB = Application.InputBox("Paste Range (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.count
    rc = rngA.Columns.count
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
    
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.RowHeight <> 0
    Next
    
    Application.Goto rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

How I wish I came across your code earlier.

Perfect!

Applied your code and bam! For a VBA noob this is magic!

You are a life-saver indeed.

 

[debeast]

I answered this question in another thread.
http://www.mrexcel.com/forum/excel-...data-filtered-list-another-filtered-list.html

Here is the code:

Sub CopyVisibleToVisible()
'Copy paste(value):
'from filtered to filtered range
'from filtered to unfiltered range
'from unfiltered to filtered range
'Not work on hidden column

    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
    
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Copy Range :", Title, rngA.Address, Type:=8)
    
    Set rngB = Application.InputBox("Paste Range (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.count
    rc = rngA.Columns.count
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
    
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.RowHeight <> 0
    Next
    
    Application.Goto rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

Is there any way I could learn this magic too!?

Seriously, I would appreciate it if I could get some pro-tips on how to get started on learning VBA...

 

[Akuini]

How I wish I came across your code earlier.

Perfect!

Applied your code and bam! For a VBA noob this is magic!

You are a life-saver indeed.

I'm glad to help.
Thanks for the feed back & your kind word.

Is there any way I could learn this magic too!?
Seriously, I would appreciate it if I could get some pro-tips on how to get started on learning <acronym title="visual basic for applications">VBA</acronym>...

Maybe other member could help you with this.
This discussion maybe could help:
http://www.mrexcel.com/forum/mrexce...al-basic-applications-reference-material.html