Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows that are equal amts but different signs
Hi,
i have a long list of data where there a SOME rows with equal amounts but with opposite signs, ie, row 3 may have amount 1234 and row 14 is -1234. I want to have these equal but opposite amounts deleted from my list. The rows may or may not have same invoice number, ie, row 3 may have invoice number 321 but row 14 may have invoice number 321A (or 03210 etc). What is the fastest way to do this? Thanks, Dolphin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows that are equal amts but different signs
This will take some doing, because of the mix of numeric entries and
alphanumeric entries. Step one will be: make a backup copy of your file so you can practice this and potentially make mistakes without fear of a catastrophic data loss. Let's call the column with the numbers you want to compare the "comparison column". Insert a "helper" column and number each row starting at 1 with a different number in each row. There are several ways to do this, but when you're finished each entry in this column should be static- that is to say, NOT a formula. After you perform the row deletes this column will allow you to sort the data back into its original order. If this is not important you can skip this step. Next, insert another helper column, and copy and paste as values the entries in the comparison column. Highlight this column, and run this macro (which I found in a previous post from Gord Dibbon- many thanks, Gord!). This will remove the alpha characters, leaving only numeric entries. Sub RemoveAlphas() ' Remove alpha characters from a string. ' except for decimal points ' if don't want decimals, delete the decimal pt. from "[0-9.]" befroe running Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Next, insert another helper column; in the first row enter this formula: =ABS(cell reference) where cell reference is the column you just removed the alpha characters from. The ABS function returns the absolute value of each entry, and will allow you to compare -1234 and 1234. Copy and paste this column over itself as values; highlight all rows and sort on this column. Next, insert a new column and enter this formula in the first row, then copy and paste into remaining rows: =IF(OR(B2=B1,B2=B3),"xxx","") You'll need to adjust the cell references. This formula returns the entry xxx if a cell matches the cell above or below it. This is how you'll find repeat entries. Copy and paste this column over itself as values; highlight all rows and sort on this column. All the xxx rows should now be grouped together: delete the xxx rows. Note that this will delete all the repeated entries, as your post stated; I'm bringing this up because a lot of times people want to leave only one instance of repeated numbers. Finally, sort on the column of numbers from step one to return the data to its original order, and delete all the helper columns you added. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed: Comparing cell values and deleting rows | Excel Discussion (Misc queries) | |||
Deleting empty rows automatically | Excel Discussion (Misc queries) | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Deleting Unique Rows | Excel Discussion (Misc queries) | |||
Deleting rows containing common data | Excel Discussion (Misc queries) |