Match, cut & Paste

puru

New Member
Joined
Apr 13, 2009
Messages
6
<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 112pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=149 height=17 x:num="0">CAD - </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 101pt; BACKGROUND-COLOR: transparent" width=135 x:num="-2450000">CAD (2,450,000.00)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="2450000">CAD 2,450,000.00 </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0">CAD - </TD></TR></TBODY></TABLE>



<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 112pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=149 height=17 x:num="2138248.62">CAD 2,138,248.62 </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 101pt; BACKGROUND-COLOR: transparent" width=135 x:num="0">CAD - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="0">CAD - </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent" x:num="-2138248.62">CAD (2,138,248.62)</TD></TR></TBODY></TABLE>



<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 112pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=149 height=17 x:num="0">CAD - </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 101pt; BACKGROUND-COLOR: transparent" width=135 x:num="-1899000">CAD (1,899,000.00)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="1899000">CAD 1,899,000.00 </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0">CAD - </TD></TR></TBODY></TABLE>


I have debits in Column F and Credits in Column G
Now what I want is
1) A VB Code that will automatecally match two amounts in column F & G based on the negative and positive amount. These amounts could be in F45 the match in G195 or F58 & match in G88. Please be advised that the columns are not always F & G.
2) After amounts in two columns are matched a VB Code that will cut matched rows from "sheet1" and paste to "sheet2" and delete the blank rows from "sheet1"
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi puru,

I see no one has responded to your post and I noticed it the other day.

Do you still need assistance ?

The basis of what you want is straight forward, but the confusing bit is when you say :

"Please be advised that the columns are not always F & G."

What other columns would they be in. Is there only one value on each Row? either positive or negative.?

Regards

ColinKJ
 

puru

New Member
Joined
Apr 13, 2009
Messages
6
Hi puru,

I see no one has responded to your post and I noticed it the other day.

Do you still need assistance ?

The basis of what you want is straight forward, but the confusing bit is when you say :

"Please be advised that the columns are not always F & G."

What other columns would they be in. Is there only one value on each Row? either positive or negative.?

Regards

ColinKJ

Hello Colin,

The columns are not always F&G means the colums could any one of these (D&E, F&G, H&I, J&K, L&M and N&O).
And yes you are right. There is only one value on each row (positive or negative).

Thanks,

Puru
 

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi puru,

OK, I've put something together for you.

Don`t know how familiar you are with VBA code, but just put this in a standard module, and assign it to a Ctrl+key combination.

Code:
Sub CutAndPaste()
Application.ScreenUpdating = False
For Col = 4 To 14 Step 2
d:
    Ro = ActiveCell.SpecialCells(xlLastCell).Row
    For R = 1 To Ro
        If Cells(R, Col) <> "" And Len(Cells(R, Col).Text) > 6 Then
            x = Cells(R, Col).Text: Valu = 0: ValTX = ""
            For w = 1 To Len(x)
                Y = Mid$(x, w, 1)
                Select Case Y
                    Case IsNumeric(Y), ".", "0"
                    ValTX = ValTX + Y
                    Case "("
                    ValTX = "-" + ValTX
                End Select
            Next w
            Valu = Val(ValTX)
 
            For RR = 1 To Ro
                If Cells(RR, Col + 1) <> "" And Len(Cells(RR, Col + 1).Text) > 6 Then
                    x = Cells(RR, Col + 1).Text: Valu1 = 0: ValTX1 = ""
                    For w = 1 To Len(x)
                        Y = Mid$(x, w, 1)
                        Select Case Y
                            Case IsNumeric(Y), ".", "0"
                            ValTX1 = ValTX1 + Y
                            Case "("
                            'ValTX1 = "-" + ValTX1
                        End Select
                    Next w
                    Valu1 = Val(ValTX1)
                    If Valu1 = Valu Then
                        Sheets(2).Activate
                        Ro2 = ActiveCell.SpecialCells(xlLastCell).Row + 1
                        Sheets(2).Cells(Ro2, 1) = Sheets(1).Cells(R, Col).Text
                        Sheets(2).Cells(Ro2, 2) = Sheets(1).Cells(R, Col + 1).Text
                        Sheets(2).Cells(Ro2 + 1, 1) = Sheets(1).Cells(RR, Col).Text
                        Sheets(2).Cells(Ro2 + 1, 2) = Sheets(1).Cells(RR, Col + 1).Text
                        Sheets(1).Activate
                        If R > RR Then
                            Sheets(1).Rows(Mid$(Str$(R), 2) + ":" + Mid$(Str$(R), 2)).Delete
                            Sheets(1).Rows(Mid$(Str$(RR), 2) + ":" + Mid$(Str$(RR), 2)).Delete
                        Else
                            Sheets(1).Rows(Mid$(Str$(RR), 2) + ":" + Mid$(Str$(RR), 2)).Delete
                            Sheets(1).Rows(Mid$(Str$(R), 2) + ":" + Mid$(Str$(R), 2)).Delete
                        End If
                        GoTo d
                    End If
                End If
            Next RR
        End If
    Next R
Next Col
Application.ScreenUpdating = True
End Sub

I've used the examples you posted plus additional values scattered across the columns (D&E, F&G, H&I, J&K, L&M and N&O) and various Rows on Sheet(1).

The matched results are copied into Columns A & B on Sheet(2).

If you want a copy of the test WB, send me a private message with an email address, and I'll send it over.

Regards

ColinKJ
 

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,215
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top