Problem moving data
 

I have data on one sheet "SJournal" that I need to copy or move ont
another sheet "Invoice". I also have a condidtion that I need to meet
Column "A" of "Sjournal" must match column "B" of "Invoice" and th
matches will
be on different rows. For example: "Sjournal" may have
the data 16791 on row 12 (12, "A") and "Invoice may have
the data 16791 on row 67 (67, "B"). If the data matches then I want t
copy rows "J" "K" "L", & "M" to "Invoice".

Following is the code I have:

Dim t As Long
Dim jrnlArr As Variant
Dim invArr As Variant

jrnlArr = Sheets("SJournal").Range("A1:M9999")
invArr = Sheets("Invoice").Range("A1:M9999")

For t = 1 To 9999
If jrnlArr(t, 1) = invArr(t, 2) Then
invArr(t, 10) = jrnlArr(t, 10)
invArr(t, 11) = jrnlArr(t, 11)
invArr(t, 12) = jrnlArr(t, 12)
invArr(t, 13) = jrnlArr(t, 13)
End If
Next t
Sheets("Invoice").Range("A1:M9999") = invArr


The two problems, I think I have: (1) I believe the above code require
the match to be on the same rows. 2) "SJournal" will have 4 matches t
1 "Invoice" match so my data will be over-written.

Example "Invoice" rows:
"A" "B" "C" "D"

2/5/06 16771 CUSTOM MOULDING LLC 216.26
2/5/06 16772 VULCAN THREADED PRODUCTS, Inc. 36.81
2/5/06 16773 VULCAN THREADED PRODUCTS, Inc. 51.29
2/5/06 16774 VULCAN THREADED PRODUCTS, Inc. 19.79

Sample "SJournal" rows:
16771 112.83
16771 28.21
16771 75.22
16772 11.33
16772 2.83
16772 22.65
16773 15.78
16773 3.95
16773 31.56
16774 6.09
16774 1.52
16774 12.1

--
Metraza
-----------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...fo&userid=3164
View this thread: http://www.excelforum.com/showthread.php?threadid=51693

Problem moving data
 

Hi,
Some comments/questions:

Column D of INVOICE appears to be a summation of the "second" column of the
SJOURNAL data you posted - is it calculated this way (and which column of
SJOURNAL does it appear?)?

And as there can be more than one entry in SJOURNAL for an INVOICE,
which/what goes on the (one?) INVOICE line from SJOURNAL?

"Metrazal" wrote:


I have data on one sheet "SJournal" that I need to copy or move onto
another sheet "Invoice". I also have a condidtion that I need to meet:
Column "A" of "Sjournal" must match column "B" of "Invoice" and the
matches will
be on different rows. For example: "Sjournal" may have
the data 16791 on row 12 (12, "A") and "Invoice may have
the data 16791 on row 67 (67, "B"). If the data matches then I want to
copy rows "J" "K" "L", & "M" to "Invoice".

Following is the code I have:

Dim t As Long
Dim jrnlArr As Variant
Dim invArr As Variant

jrnlArr = Sheets("SJournal").Range("A1:M9999")
invArr = Sheets("Invoice").Range("A1:M9999")

For t = 1 To 9999
If jrnlArr(t, 1) = invArr(t, 2) Then
invArr(t, 10) = jrnlArr(t, 10)
invArr(t, 11) = jrnlArr(t, 11)
invArr(t, 12) = jrnlArr(t, 12)
invArr(t, 13) = jrnlArr(t, 13)
End If
Next t
Sheets("Invoice").Range("A1:M9999") = invArr


The two problems, I think I have: (1) I believe the above code requires
the match to be on the same rows. 2) "SJournal" will have 4 matches to
1 "Invoice" match so my data will be over-written.

Example "Invoice" rows:
"A" "B" "C" "D"

2/5/06 16771 CUSTOM MOULDING LLC 216.26
2/5/06 16772 VULCAN THREADED PRODUCTS, Inc. 36.81
2/5/06 16773 VULCAN THREADED PRODUCTS, Inc. 51.29
2/5/06 16774 VULCAN THREADED PRODUCTS, Inc. 19.79

Sample "SJournal" rows:
16771 112.83
16771 28.21
16771 75.22
16772 11.33
16772 2.83
16772 22.65
16773 15.78
16773 3.95
16773 31.56
16774 6.09
16774 1.52
16774 12.18


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=516936

Problem moving data
 

Let me try to explain what I am trying to do a little better. (It i
hard to put what I want to do into words but here goes) The "SJournal
worksheet has 2 columns with data in it, the rest are empty. The firs
column has my invoice number and the balance of the row has on
numerical number located in either the J,K,L, or M column. Now
"SJournal" will have three rows with the same invoice number and th
one number located in columns J,K,L, or M.
What I want to do is copy any data located in the J,K,L, or M to th
same row in the "Invoice" worksheet. However, the "Invoice" workshee
only has one invoice number per row. Does that make sense?

To answer you sumation question. Yes, the values are sums from th
other rows. However, I cannot calculate this
value from a percentage on the "Invoice" sheet because the
percentages vary.

I've basically got data divided into 3 rows on the "SJournal" that
need to merge into 1 row on the "Invoice" sheet and the only commo
relationship is the invoice number. (Column A) on the "SJournal" an
(Column B) on the "Invoice" sheet.

Man, I hope that all makes sense.

Thanks in advance,

ME

--
Metraza
-----------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...fo&userid=3164
View this thread: http://www.excelforum.com/showthread.php?threadid=51693

Problem moving data
 

It's late and I'm still confused! Can you post sample spreadsheet to me (with
expected results)

"Metrazal" wrote:


I have data on one sheet "SJournal" that I need to copy or move onto
another sheet "Invoice". I also have a condidtion that I need to meet:
Column "A" of "Sjournal" must match column "B" of "Invoice" and the
matches will
be on different rows. For example: "Sjournal" may have
the data 16791 on row 12 (12, "A") and "Invoice may have
the data 16791 on row 67 (67, "B"). If the data matches then I want to
copy rows "J" "K" "L", & "M" to "Invoice".

Following is the code I have:

Dim t As Long
Dim jrnlArr As Variant
Dim invArr As Variant

jrnlArr = Sheets("SJournal").Range("A1:M9999")
invArr = Sheets("Invoice").Range("A1:M9999")

For t = 1 To 9999
If jrnlArr(t, 1) = invArr(t, 2) Then
invArr(t, 10) = jrnlArr(t, 10)
invArr(t, 11) = jrnlArr(t, 11)
invArr(t, 12) = jrnlArr(t, 12)
invArr(t, 13) = jrnlArr(t, 13)
End If
Next t
Sheets("Invoice").Range("A1:M9999") = invArr


The two problems, I think I have: (1) I believe the above code requires
the match to be on the same rows. 2) "SJournal" will have 4 matches to
1 "Invoice" match so my data will be over-written.

Example "Invoice" rows:
"A" "B" "C" "D"

2/5/06 16771 CUSTOM MOULDING LLC 216.26
2/5/06 16772 VULCAN THREADED PRODUCTS, Inc. 36.81
2/5/06 16773 VULCAN THREADED PRODUCTS, Inc. 51.29
2/5/06 16774 VULCAN THREADED PRODUCTS, Inc. 19.79

Sample "SJournal" rows:
16771 112.83
16771 28.21
16771 75.22
16772 11.33
16772 2.83
16772 22.65
16773 15.78
16773 3.95
16773 31.56
16774 6.09
16774 1.52
16774 12.18


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=516936