Solved

Excel Formula

Posted on 2016-07-26
21
69 Views
Last Modified: 2016-08-01
Hello,

I have an Excel Spreadsheet with positive and negative values in Column A.
For Example:

-100.00
100.00
-50.00
50.00
20
10

How can I remove the values that equal each other regardless of the negative or positive value, so in my example remove the 100, -100, 50 & -50 and only keep those values where there isn’t a match? i.e. the 20 & 10
0
Comment
Question by:jspc
  • 6
  • 3
  • 3
  • +4
21 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41730552
Try this macro

Sub delabsdups()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 1) <> ""
    j = i + 1
    Do While Cells(j, 1) <> ""
        If Abs(Cells(i, 1)) = Abs(Cells(j, 1)) Then
            Cells(j, 1).EntireRow.Delete
            Cells(i, 1).EntireRow.Delete
            i = i - 1
            Exit Do
        End If
        j = j + 1
    Loop
    i = i + 1
Loop
End Sub
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41730644
HI,

maybe you wanted to delete where the sum = 0
Sub DelSumZero()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 1) <> ""
    j = i + 1
    Do While Cells(j, 1) <> ""
        If Cells(i, 1) + Cells(j, 1) = 0 Then
            Cells(j, 1).EntireRow.Delete
            Cells(i, 1).EntireRow.Delete
            i = i - 1
            Exit Do
        End If
        j = j + 1
    Loop
    i = i + 1
Loop
End Sub

Open in new window

Regards
0
 

Author Comment

by:jspc
ID: 41730720
Hi Saqib,

I ran your script in a Marco and it removed 95% of the data. I know this is not correct as there should be thousands of entries remaining.
0
 

Author Comment

by:jspc
ID: 41730721
Running your script Rgonzo1971 now - There are 25,000 records in the Sheet and the Marco has been going for over 5 min now, I think my Excel has now crashed.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41730726
jspc, your response is very general and cannot help us understand your issue. Can you be more specific, with an example?
0
 

Author Comment

by:jspc
ID: 41730736
I don’t know how to be more specific with my question than the original question posted at the start.
0
 

Author Comment

by:jspc
ID: 41730779
Sorry, please use this file
Test.xlsx
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 41730980
jspc,
In the attached file two columns were added.  The first to calculate the absolute value of the Amount column.  The second uses the COUNTIF function to count the number of those values that are the same.   Rows with multiple values that equal each other (+/-) will have a COUNTIF value greater than 1 and can be deleted.   Rows with 1 in the COUNTIF column are unique with no matching value in any other rows.   When the COUNTIF column is filtered to show only the 1's, there are 8,372 of 25,888 rows that are unique.
The sheet calculation take at least 30 seconds to update on my PC so please be aware.  You may want to copy / paste values so the sheet is easier to work with.

Thanks - Jerry
EE-28959762-jp.xlsx
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41730999
then try (swifter code)
Sub DelSumZero()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 6) <> ""
    If Evaluate("=SUMPRODUCT(--((F2:F1000000)=-" & Cells(i, 6).Address(False, False) & "))") Then
        j = i + 1
        Do While Cells(j, 6) <> ""
            If Cells(i, 6) + Cells(j, 6) = 0 Then
                Cells(j, 6).EntireRow.Delete
                Cells(i, 6).EntireRow.Delete
                i = i - 1
                Exit Do
            End If
            j = j + 1
        Loop
    End If
    i = i + 1
Loop
End Sub

Open in new window

0
 
LVL 8

Expert Comment

by:Koen
ID: 41731014
alternatively, if you don't want to use code:

add a column: enter the formula in row1:
=if(a1<0,a1*-1,a1)
copy this down the entire sheet
select remove duplicates from the menu and use this field to remove the duplicate records.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jspc
ID: 41731073
If I apply a Subtotal by column ‘Period’, then total the ‘Amount’ field I get a value. Please see attached example.
Focusing on July in this case, July total should be $49177

This value is the result of all the non-offset values, so the unique values.
After I run any suggested formula it doesn't leave this value, it should.

So at the end of the day, I’m trying to eliminate all the other entries and leave only those which have no offset.
Untitled.png
Test.xlsx
0
 
LVL 8

Expert Comment

by:Koen
ID: 41731175
I'm sorry, but your test file just above still has multiple duplicates...

e.g. I see on line 50,51,68 and 69 a value of either 1288 or -1288

But, I stand corrected, my way of working will remove all the duplicates, but will leave the first encounter of any hit in the list...and i guess you don't want that
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 41731210
Jspc,

You changed the criteria of the question...

In the attached file, the COUNTIF has been changed to
=COUNTIFS([Period],[@Period],[ABS],[@ABS], [Deposit No.],[@[Deposit No.]])
This will find unique values based on the Period, the Amount and the Deposit Number.   The attached file has the original table in one worksheet and the cleaned table in a second worksheet and match your subtotals.

Thanks - Jerry
EE-28959762-jp-v2.xlsx
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41731295
@koen - the ABS function does the same as your IF statement.

=ABS(A1)

will convert A1 to the absolute value, whether it is positive or negative.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41731337
Then try

Sub DelSumZero()
Application.ScreenUpdating = False
Dim i As Long, j As Long
i = 2
Do While Cells(i, 6) <> ""
    If Evaluate("=SUMPRODUCT(--((F2:F1000000)=-" & Cells(i, 6).Address(False, False) & "),--(C2:C1000000=" & Cells(i, 3).Address(False, False) & "),--(G2:G1000000=" & Cells(i, 7).Address(False, False) & "))") Then
        Debug.Print i
        j = i + 1
        Do While Cells(j, 6) <> "" And Cells(i, 7) = Cells(j, 7)
            If Cells(i, 6) + Cells(j, 6) = 0 And Cells(i, 3) = Cells(j, 3) Then
                    Cells(j, 6).EntireRow.Delete
                    Cells(i, 6).EntireRow.Delete
                    i = i - 1
                    Exit Do
            End If
            j = j + 1
        Loop
    End If
    i = i + 1
Loop
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41731519
I think the issue is because it is not as simple as a pair with one positive and one negative. I was working on something in the office but had to dash for a train before I could upload.

In 2 separate columns do countifs formulas, in plain speak

Formula 1: If value in F is less than zero count number of occurences of -F for that period less number of occurences of F for that period else 0

Formula 2: If value in F is more than zero count number of occurences of F for that period less number of occurences of -F for that period else 0

In a third column add those two together. Where there are equal number of opposites the sum will be zero.

Apply a filter and filter on period and the third added column to exclude zero.

Doing this for July 15 showed the $49k like you had.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41732044
Set processing approach using advanced filter.  Takes about 40 seconds on my ancient laptop.
Sub Q_28959762()
    Dim wks As Worksheet, wksCrit As Worksheet
    
    Set wks = Worksheets("Sheet1")
    Application.ScreenUpdating = False

    wks.Range("K1:M1").Value = Array("ABS", "EqNext", "EqPrev")
    wks.Range("K2").Formula = "=ABS(F2)"
    wks.Range("K2").AutoFill wks.Range(wks.Range("K2"), wks.Range("J2").End(xlDown).Offset(0, 1))
    wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).Value = wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).Value
    wks.Range("K2").CurrentRegion.Sort key1:=wks.Range("K1"), header:=xlYes
    wks.Range("L2").Formula = "=K2=K3"
    wks.Range("M2").Formula = "=K2=K1"
    wks.Range("L2:M2").AutoFill wks.Range(wks.Range("L2:M2"), wks.Range("K2").End(xlDown).Offset(0, 1))
    wks.Range(wks.Range("L2:M2"), wks.Range("L2").End(xlDown)).Value = wks.Range(wks.Range("L2:M2"), wks.Range("L2").End(xlDown)).Value
    
    Set wksCrit = Worksheets("Sheet2")
    wksCrit.Range("A1:B1").Value = Array("EqNext", "EqPrev")
    wksCrit.Range("A2").Value = True
    wksCrit.Range("B3").Value = True
    
    wks.Range("K1").CurrentRegion.AdvancedFilter xlFilterInPlace, wksCrit.Range("A1:B3")
    wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    wks.ShowAllData
    
    wks.Range("K:M").Delete
    wks.Range("A1").CurrentRegion.Sort key1:=wks.Range("A1"), header:=xlYes
    wksCrit.Range("A1:B3").Clear
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:jspc
ID: 41732081
Hi Jerry Paladino,

I was asked to give greater detail so needed to alter the question.

Thank you for you help and thank you to everyone who has given input, much appreciated.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 41732102
Jspc,

No problem at all.  Glad we we're able find a solution to your question.

Thanks - Jerry
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41732515
I know you have now closed the question but here is the solution that I was working on yesterday:

Formula 1 in column K:
=IF($F2>0,COUNTIFS($F$1:$F$25888,-$F2,$G$1:$G$25888,$G2)-COUNTIFS($F$1:$F$25888,$F2,$G$1:$G$25888,$G2),0)

Formula 2 in column L:
IF($F2<0,COUNTIFS($F$1:$F$25888,-$F2,$G$1:$G$25888,$G2)-COUNTIFS($F$1:$F$25888,$F2,$G$1:$G$25888,$G2),0)

Formula 3 in Column M:
=SUM(K2:L2)

Filter on Column G for July 15 and column M to exclude 0 and the sum of column F gives $49177.20

The 3 formulas could be combined into one column if you wished:

=SUM(IF($F2>0,COUNTIFS($F$1:$F$25888,-$F2,$G$1:$G$25888,$G2)-COUNTIFS($F$1:$F$25888,$F2,$G$1:$G$25888,$G2),0),IF($F2<0,COUNTIFS($F$1:$F$25888,-$F2,$G$1:$G$25888,$G2)-COUNTIFS($F$1:$F$25888,$F2,$G$1:$G$25888,$G2),0))

To end up with just the required data you have two options:
1) Filter on column M to exclude 0 and then copy the visible data elsewhere. When a filter is applied copying a block of data will only copy those cells that are visible and pasting will paste as a contiguous block.
2) Filter to show only 0 and then delete the visible rows. Again selecting as a block and deleting rows will only delete the visible rows, leaving the hidden rows untouched.

Thanks
Rob H
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41738092
So, you didn't need to eliminate values based on ABS()?
There is a great deal of difference between your $49177 sum and my $6683.82 for the July 15 period, if only eliminating 'duplicates' within a time period.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now