Macro to compare and move data from one worksheet to another

D

dwood

New Member
Joined
Nov 29, 2006
Messages
12
I have two worksheets. Sheet 1 contains two columns of data. Column A contains a list of account numbers and column B contains demographic information about the account number in column A. Sheet 2 contains a list of account numbers in column A and Column B is blank. I need a macro that will look at the account numbers in column A and if it finds the identical account number in column A of Sheet B, copy the demographic data in Column B of Sheet A to Column B of Sheet 2. Since the two worksheets do not necessarily contain the same account numbers, they will not match up row by row. If it cannot find a match in Sheet 2 for an account number in Sheet 1, I need it to skip to the next account number and repeat the process. When the macro reaches the last account number in Sheet 1, I need it to stop. This is a reoccurring monthly report with approximately 200 account numbers and the demographic data is carried forward month to month.
 
Sort by date Sort by votes
is it possible to copy 3 column value from Sheet1 for all matches in Sheet2

Sheet1 Content

WP Name</SPAN>
SSR Type</SPAN>
Value</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
SSR Submitted</SPAN>
1. SSR Submitted</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
SSR Approved</SPAN>
2. SSR Approved</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
SSR Approved (Rejected)</SPAN>
2.1 SSR Approved (Rejected)</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
BOQ Submitted</SPAN>
3. BOQ Submitted</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
BOQ Verfied</SPAN>
4. BOQ Verfied</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
BOQ Verfied (Rejected)</SPAN>
4.1 BOQ Verfied (Rejected)</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Deliveried</SPAN>
5. EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Received and Material Ready</SPAN>
6. EQ Received and Material Ready</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
Readiness Checked</SPAN>
7. Readiness Checked</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
Readiness Checked (Rejected)</SPAN>
7.1 Readiness Checked (Rejected)</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
PAT Submitted</SPAN>
8. PAT Submitted</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
PAT Approved</SPAN>
9. PAT Approved</SPAN>

<TBODY>
</TBODY>


Sheet2 Content

WP Name</SPAN>
SSR Type</SPAN>
Value</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
SSR Submitted</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Deliveried</SPAN>

<TBODY>
</TBODY>


After Macro the status of sheet2 should be

WP Name</SPAN>
SSR Type</SPAN>
Value</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Deliveried</SPAN>
5. EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
SSR Submitted</SPAN>
1. SSR Submitted</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Deliveried</SPAN>
5. EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
EQ Deliveried</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
EQ Deliveried</SPAN>
5. EQ Deliveried</SPAN>

<TBODY>
</TBODY>
 
Last edited:
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
sadat2706,

Welcome to the MrExcel forum.

What version of Excel are you using?


Your data structure is different then the data for dwood. And, you do not want to hijack a thread.


Please start your own new Tread, with your own informative Title.


We can not tell what cells, rows, columns, your raw data is in.

Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message with a link to your new thread and I will have a look.
 
Upvote 0
Please help, i will have morethan 20,000 rows so I am just giving a sample

here is the content of sheet1


A</SPAN>
B</SPAN>
C</SPAN>
1</SPAN>
Acct #</SPAN>
LS</SPAN>
Status</SPAN>
2</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
1. SSR Submitted</SPAN>
SSR Submitted</SPAN>
3</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2. SSR Approved</SPAN>
SSR Approved</SPAN>
4</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2.1 SSR Approved (Rejected)</SPAN>
SSR Approved (Rejected)</SPAN>
5</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
3. BOQ Submitted</SPAN>
BOQ Submitted</SPAN>
6</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4. BOQ Verfied</SPAN>
BOQ Verfied</SPAN>
7</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4.1 BOQ Verfied (Rejected)</SPAN>
BOQ Verfied (Rejected)</SPAN>
8</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
5. EQ Deliveried</SPAN>
EQ Deliveried</SPAN>
9</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
6. EQ Received and Material Ready</SPAN>
EQ Received and Material Ready</SPAN>
10</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7. Readiness Checked</SPAN>
Readiness Checked</SPAN>
11</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7.1 Readiness Checked (Rejected)</SPAN>
Readiness Checked (Rejected)</SPAN>
12</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
8. PAT Submitted</SPAN>
PAT Submitted</SPAN>
13</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
9. PAT Approved</SPAN>
PAT Approved</SPAN>

<TBODY>
</TBODY>



Here is content of Sheet2

A</SPAN>
B</SPAN>
C</SPAN>
1</SPAN>
Acct #</SPAN>
LS</SPAN>
2</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
1. SSR Submitted</SPAN>
3</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2. SSR Approved</SPAN>
4</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2.1 SSR Approved (Rejected)</SPAN>
5</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
3. BOQ Submitted</SPAN>
6</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4. BOQ Verfied</SPAN>
7</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4.1 BOQ Verfied (Rejected)</SPAN>
8</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
5. EQ Deliveried</SPAN>
9</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
6. EQ Received and Material Ready</SPAN>
10</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7. Readiness Checked</SPAN>
11</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7.1 Readiness Checked (Rejected)</SPAN>
12</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
8. PAT Submitted</SPAN>
13</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
9. PAT Approved</SPAN>
14</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
1. SSR Submitted</SPAN>
15</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2. SSR Approved</SPAN>
16</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2.1 SSR Approved (Rejected)</SPAN>
17</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
3. BOQ Submitted</SPAN>
18</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4. BOQ Verfied</SPAN>
19</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4.1 BOQ Verfied (Rejected)</SPAN>
20</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
5. EQ Deliveried</SPAN>
21</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
6. EQ Received and Material Ready</SPAN>
22</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7. Readiness Checked</SPAN>
23</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7.1 Readiness Checked (Rejected)</SPAN>
24</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
8. PAT Submitted</SPAN>
25</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
9. PAT Approved</SPAN>

<TBODY>
</TBODY>


after macro the Status of sheet 2 should be like bellow

A</SPAN>
B</SPAN>
C</SPAN>
1</SPAN>
Acct #</SPAN>
LS</SPAN>
2</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
1. SSR Submitted</SPAN>
SSR Submitted</SPAN>
3</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2. SSR Approved</SPAN>
SSR Approved</SPAN>
4</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2.1 SSR Approved (Rejected)</SPAN>
SSR Approved (Rejected)</SPAN>
5</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
3. BOQ Submitted</SPAN>
BOQ Submitted</SPAN>
6</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4. BOQ Verfied</SPAN>
BOQ Verfied</SPAN>
7</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4.1 BOQ Verfied (Rejected)</SPAN>
BOQ Verfied (Rejected)</SPAN>
8</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
5. EQ Deliveried</SPAN>
EQ Deliveried</SPAN>
9</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
6. EQ Received and Material Ready</SPAN>
EQ Received and Material Ready</SPAN>
10</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7. Readiness Checked</SPAN>
Readiness Checked</SPAN>
11</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7.1 Readiness Checked (Rejected)</SPAN>
Readiness Checked (Rejected)</SPAN>
12</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
8. PAT Submitted</SPAN>
PAT Submitted</SPAN>
13</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
9. PAT Approved</SPAN>
PAT Approved</SPAN>
14</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
1. SSR Submitted</SPAN>
SSR Submitted</SPAN>
15</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2. SSR Approved</SPAN>
SSR Approved</SPAN>
16</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
2.1 SSR Approved (Rejected)</SPAN>
SSR Approved (Rejected)</SPAN>
17</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
3. BOQ Submitted</SPAN>
BOQ Submitted</SPAN>
18</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4. BOQ Verfied</SPAN>
BOQ Verfied</SPAN>
19</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
4.1 BOQ Verfied (Rejected)</SPAN>
BOQ Verfied (Rejected)</SPAN>
20</SPAN>
P20- OSP Detail Design and Implementation (New)</SPAN>
5. EQ Deliveried</SPAN>
EQ Deliveried</SPAN>
21</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
6. EQ Received and Material Ready</SPAN>
EQ Received and Material Ready</SPAN>
22</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7. Readiness Checked</SPAN>
Readiness Checked</SPAN>
23</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
7.1 Readiness Checked (Rejected)</SPAN>
Readiness Checked (Rejected)</SPAN>
24</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
8. PAT Submitted</SPAN>
PAT Submitted</SPAN>
25</SPAN>
P20- OSP Detail Design and Implementation</SPAN>
9. PAT Approved</SPAN>
PAT Approved</SPAN>

<TBODY>
</TBODY>
 
Upvote 0
sadat2706,

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


After you start a new thread (as described above), then send me a Private Message with a link to your new thread, and, I will have a look at it.
 
Upvote 0
hiker95 said:
dwood,


Sample worksheets:


Sheet1

*AB
1account numberdemographic
212345demographic for 12345
312554demographic for 12554
412555demographic for 12555
5**

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 127px"><COL style="WIDTH: 209px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4




Sheet2

*AB
1account numberdemographic
212121*
312345*
412350*
512555*
6**

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 127px"><COL style="WIDTH: 209px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4




After the macro:


Sheet2

*AB
1account numberdemographic
212121*
312345demographic for 12345
412350*
512555demographic for 12555
6**

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 127px"><COL style="WIDTH: 209px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub UpdateW2()
' hiker95, 06/16/2011
' http://www.mrexcel.com/forum/showthread.php?t=557784
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w2.Columns(1), 0)
  On Error GoTo 0
  If FR <> 0 Then w2.Range("B" & FR).Value = c.Offset(, 1)
Next c
Application.ScreenUpdating = True
End Sub


Then run the UpdateW2 macro.


If the above macro does not work correctly, then we will need screenshots of the two worksheets:

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:[/QUOTE I used this Macro in a similar situation, however my account number will duplicate in the 2nd worksheet and I need for the macro to update each account number in the second worksheet that it matches even if it is a duplicate, could you advise how to revise the macro for that?]
Click to expand...
 
Upvote 0
pbeuaprey,

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


After you start a new thread (as described above), then send me a Private Message with a link to your new thread, and, I will have a look at it.
 
Upvote 0
Hi Hiker95,

Sorry to disturb you.
Can you please help me to create macro to calculate Average and Standrd deviation columnwise, example is posted in my post. Please....
 
Upvote 0
Hi,

I have a question. Can you help me with building a macro for my problem?
I have to separate excel files that difference from name every time.
Can rename them to perform the merge.

1 excel file for my product that I have:
ABCDE
Product codeDescriptionStorageSoltBarcode
1Rent10.908718481280232

1 csv file that I retrieve from my webpage. Extracted column with text to column. Deleted column A.
ABCDEFG
_sku"post_title""_stock""_wpm_gtin_code""post_type""post_parent""ID"
95818023​
"Auto + Vliegtuig Race set 10-delig""""8718481280232""product""0""8268"
95818025​
"Kleiset met gereedschap 10-delig""""8718053880259""product""0""8273"
95818041​
"Insteek Mozaïek set 210-delig""""8717154280418""product""0""8274"
95818045​
"Trapveer met Regenboog Kleuren""""8718053880457""product""0""8276"

Only the barcode is the same as "_wpm_gtin_code" nut without "
Is it possible to compare the barcode with "_wpm_gtin_code" and then merge the storage in H like "1"

Hope you can help me.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA Match Cells, Copy Adjacent Cells
2
Replies
11
Views
542
Fluff
Fluff
S
  • Question
Copy rows from multiple worksheets into multiple worksheets in another workbook based on criteria
Replies
1
Views
105
eiloken
E
lost_in_the_sauce
  • Question
SumProduct works but locks my laptop, need something less memory intensive
Replies
2
Views
104
MARK858
MARK858
S
  • Question
Can a macro help create a user hierarchy?
Replies
7
Views
256
Akuini
Akuini
P
  • Question
Analytics Project - Sumifs
Replies
1
Views
280
bobaol
B

Forum statistics

Threads
1,215,729
Messages
6,126,525
Members
449,316
Latest member
sravya

Share this page

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
Back
Top