Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Data between sheets in the same workbook and moving data between Workbooks. | Excel Worksheet Functions | |||
Referencing Problem with moving cells | Excel Discussion (Misc queries) | |||
similar problem to moving to next cell | New Users to Excel | |||
Copying and moving matching data problem | Excel Programming | |||
moving cursor, may solve big problem | Excel Programming |