On 5 Feb, 16:47, Joel wrote:
One line was wrong (see below). *My code uses column K as an auxilary column.
*This can be change to any empty column.
from
Range("J" & RowCount) = Abs(Range("J" & MatchRow))
to
Abs(Range("J" & RowCount)) = Abs(Range("J" & MatchRow))
"K" wrote:
On Feb 5, 11:58 am, Joel wrote:
K: This request is very similar to the following:http://www.microsoft.com/office/comm....mspx?dg=micro...
Sub DeleteDuplicates1()
'
' Macro1 Macro
' Macro recorded 1/29/2008 by Joel Warburg
'
'
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'sort data
Range("A6:J" & LastRow).Sort _
* *Key1:=Range("G6"), _
* *MatchCase:=False, _
* *Key2:=Range("J6"), _
* *MatchCase:=False, _
'delete duplicates
'put true in column K if need to be deleted
RowCount = 6
Do While Range("A" & RowCount) < ""
* *Match = False
* *MatchRow = RowCount + 1
* *Do While Range("G" & RowCount) = Range("G" & MatchRow) And _
* * * Range("J" & RowCount) = abs(Range("J" & MatchRow))
* * * If Range("K" & RowCount) < True And _
* * * * *Range("K" & MatchRow) < True Then
* * * * *If Range("G" & RowCount) = Range("G" & MatchRow) And _
* * * * * * Range("J" & RowCount) = -1 * Range("J" & MatchRow) Then
* * * * * * Range("K" & RowCount) = True
* * * * * * Range("K" & MatchRow) = True
* * * * * * Exit Do
* * * * *End If
* * * End If
* * * MatchRow = MatchRow + 1
* *Loop
* *RowCount = RowCount + 1
Loop
'Sort by column K which contains True if item should be deleted.
Range("A6:K" & LastRow).Sort _
* *Key1:=Range("K6")
If Range("K6") = True Then
* *LastRow = Range("K6").End(xlDown).Row
* *Rows("6" & ":" & LastRow).Delete
End If
'remove auxillary columns K
Columns("K:K").Delete
end sub
"K" wrote:
ROW * * * *A * * * B * * * * * C * * * * *G * * * * * * * * * * * * *J
5
6 *000 * * * * * G56 * * * TYSS * *000G56TYSS * * *1000
7 *001 * * HJ1 * * WER5 * *001HJ1WER5 * * *1900
8 *001 * * HJ1 * * WER5 * *001HJ1WER5 * * *-1900
9 *546 * * ST8 * * SVR5 * *546ST8SVR5 * * *5600
10 230 * * W33 * * ZTT8 * *230W33ZTT8 * * *-2000
11 230 * * W33 * * ZTT8 * *230W33ZTT8 * * *4500
12 230 * * W33 * * ZTT8 * *230W33ZTT8 * * *2000
13 130 * * TV5 * * LK12 * *130TV5LK12 * * *800
14 152 * * Y12 * * RE88 * *152Y12RE88 * * *9000
15 152 * * Y12 * * RE88 * *152Y12RE88 * * *4400
16 152 * * Y12 * * RE88 * *152Y12RE88 * * *-3000
17 152 * * Y12 * * RE88 * *152Y12RE88 * * *-9000
18 152 * * Y12 * * RE88 * *152Y12RE88 * * *3000
19 003 * * Q45 * * OPK2 * *003Q45OPK2 * * *6000
In Row 5 I have headings and in Range ("A6:C10000") I have data as
show above
in each cell of Range("G6:G10000") I have formula which is "=A6&B6&C6"
and
this formula been drag down to cell "G10000". This formula actually
combines the
value which are given in coloumns "A , B and C". *I have figures in
coloumn "J"
I want Macro which should check coloumn "G" and if the values of cells
in coloumn
"G" are same and in same Row of those cell in coloumn "J" cells if I
have "+" and
"-" figures of same amount then Row of those cells from coloumn "A" to
coloumn
"L" should be deleted. * For example in above data in coloumn "G" cells
of Row 14 to 18
we have same values of "152Y12RE88" and there are plus and minus
figures in coloumn "J" cells of same Rows of coloumn "G" cell in which
we have same values. The plus
and minus figures next to value of "15Y12RE88" are "9000 , 4400 ,
-3000 , -9000
and 3000". Now macro should delet 9000 and -9000 as they cancel each
other
and these figures are same in amount and also delete 3000 and -3000 so
the
figure should be left now is 4400. Basically I want Row from coloum
"A" to "L"
should be deleted when there are same values in coloumn "G" and there
figures
in coloumn "J" cells next to coloumn "G" cell value which cancel each
other.
Please if any friend can help
(Some time data goes funny when you post message so i cleared the
above data below)
ROW
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
A -------Coloumn
000
001
001
546
230
230
230
130
152
152
152
152
152
003
B------------Coloumn
G56
HJ1
HJ1
ST8
W33
W33
W33
TV5
Y12
Y12
Y12
Y12
Y12
Q45
C---------------Coloumn
TYSS
WER5
WER5
SVR5
ZTT8
ZTT8
ZTT8
LK12
RE88
RE88
RE88
RE88
RE88
OPK2
G---------------Coloumn
000G56TYSS
001HJ1WER5
001HJ1WER5
546ST8SVR5
230W33ZTT8
230W33ZTT8
230W33ZTT8
130TV5LK12
152Y12RE88
152Y12RE88
152Y12RE88
152Y12RE88
152Y12RE88
003Q45OPK2
J----------------Coloumn
1000
1900
-1900
5600
-2000
4500
2000
800
9000
4400
-3000
-9000
3000
6000- Hide quoted text -
- Show quoted text -
Hi Joel, Thanks for replying. I tried putting your macro but its not
working for some reason and also you have put Range "K" and its
deleting coloumn "K" as i havent metioned about coloumn or row K in my
question anywhere. please can you tell me that what should i be doing- Hide quoted text -
- Show quoted text -
Hi Joel, i changed the line which you told me and it did work but
there is still little problem as its not deleting all
the debit and credit figures. the data i have on my spreadsheet is
already been sorted and only need to delete all the debit and credit
figures which can come in same code. Like above in coloum "G" cell
there is one code which is
"152Y12RE88" and with your macro it delete just -3000 and 3000 figures
which come in coloumn "J" cell under the code
i mentioned but macro not deleting the -9000 and 9000 figures which
also relates the same code . Please can you give any help on this.
Thanks