Matching data from one sheet to another

jwmcgraw

New Member
Joined
Jan 3, 2014
Messages
3
Hello all,
I have read through a hundred posts and thats why I'm here. Apologies to the readers of redundant posts but I can't seem to get any functions to work. So here goes ...
I have sheet2 which has data I need transferred to sheet1. Both sheet1 and sheet2 have a common field, product #, but out of order so copy and paste obviously won't work. In Sheet2, the Sell Price values are in, but I need those transferred to Sheet1.

Sheet2
Product# Sell Price UOM

Sheet1
Product# Product Sell Price

In sheet2, there are more Product#'s than on Sheet1 so I need an error function, or rather a way to hold those values back and just bring the ones that match sheet1 over.

I hope this is descriptive enough. I thought I could extrapolate something I've found in another thread, but I can't seem to get the right function going. Any help is appreciated :)

Thanks!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
jwmcgraw,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Can you post screenshost of the actual raw data worksheets?

And, can you post a screenshot of 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

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


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.
 
Upvote 0
I'm using Microsoft Excel 2010. I've tried over the weekend to do it on my own but I just can't seem to get this working.
Here's what Sheet2 looks like. The Prod ID's are in different order between the two Sheets. And there are more products on Sheet2 that won't be on Sheet1. So there are going to be values on Sheet2 that I don't want transferred over. I'm trying to get the sell price value from Sheet2 to transfer over to Sheet1 based on the matching of the Prod ID's from both sheets. Rather, if Prod ID's match on both Sheets, bring over the value in the sell price value.

Excel 2010 - SHEET2
ABCD
1Product IDProd NameSell PriceUnit Price
21234Prod 1105
31238Prod 55040
41237Prod 44030
51236Prod 33020

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>




Here's what the table should look like (final product) with the new indexed data from sheet2 highlighted in blue.

Excel 2010 - SHEET1
ABCD
1Product IDProd NameSell PriceUnit Price
21234Prod 1105
31235Prod 22010
41236Prod 33020
51237Prod 44030
61238Prod 55040

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1




jwmcgraw,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Can you post screenshost of the actual raw data worksheets?

And, can you post a screenshot of 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

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


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.
 
Upvote 0
I'm Sorry. I just realized that the values weren't highlighted in Sheet1. But the values in Sheet1 in the Sell Price column are the ones that need to be brought over from sheet 2. Sorry about the confusion.
 
Upvote 0
jwmcgraw,

If I understand your correctly.

Sample raw data on worksheet Sheet2:


Excel 2007
ABCD
1Product IDProd NameSell PriceUnit Price
21234Prod 1105
31238Prod 55040
41237Prod 44030
51236Prod 33020
6
Sheet2


Sample raw data on worksheet Sheet1 (the YELLOW cells depict an old Sell Price):


Excel 2007
ABCD
1Product IDProd NameSell PriceUnit Price
21234Prod 115
31235Prod 22010
41236Prod 3120
51237Prod 4130
6
7
Sheet1


After the macro in worksheet Sheet1:


Excel 2007
ABCD
1Product IDProd NameSell PriceUnit Price
21234Prod 1105
31235Prod 22010
41236Prod 33020
51237Prod 44030
61238Prod 55040
7
Sheet1


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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub UpdateSheet1_from_Sheet2()
' hiker95, 01/06/2014
' http://www.mrexcel.com/forum/excel-questions/748129-matching-data-one-sheet-another.html
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, frng As Range, nr As Long, lr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w2
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set frng = w1.Columns(1).Find(c, LookAt:=xlWhole)
    If Not frng Is Nothing Then
      w1.Range("C" & frng.Row).Value = c.Offset(, 2).Value
    ElseIf frng Is Nothing Then
      nr = w1.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      w1.Range("A" & nr).Resize(, 4).Value = c.Resize(, 4).Value
    End If
  Next c
End With
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:D" & lr).Sort key1:=.Range("A2"), order1:=1
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateSheet1_from_Sheet2 macro.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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