Compare Cells

[mikedf]

Hi,

We have over 1000s rows in two worksheets, we have matching SKUs and but in random order but of one SKU exist in both worksheet. And need compare entire cells from two different worksheet and and return the price to worksheet A

Please Advice


WorkSheet A

[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU (A)[/TD]
[TD]Price (B)[/TD]
[TD]Stock (C)[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD][/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD][/TD]
[TD]22[/TD]
[/TR]
</tbody>[/TABLE]


WorkSheet B

[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU (A)[/TD]
[TD]Price (B)[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Try putting this in Worksheet A cell B1 and dragging down.

=VLOOKUP(A1, 'Worksheet B'!A:B, 2, FALSE)

 

[hiker95]

mikedf,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


If you wanted to use a macro solution?

Sample raw data worksheets:

Excel 2007
	A	B
1	SKU	Price
2	46	21
3	12	10
4	45	13
5	23	12
6
B

Excel 2007
	A	B	C
1	SKU	Price	Stock
2	23		45
3	45		34
4	12		23
5	46		22
6
A

After the macro in worksheet A:

Excel 2007
	A	B	C
1	SKU	Price	Stock
2	23	12	45
3	45	13	34
4	12	10	23
5	46	21	22
6
A

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.

Sub GetPrice()
' hiker95, 06/14/2014, ME784527
Dim c As Range, prng As Range
Application.ScreenUpdating = False
With Sheets("A")
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set prng = Sheets("B").Columns(1).Find(c.Value, LookAt:=xlWhole)
    If Not prng Is Nothing Then
      c.Offset(, 1).Value = Sheets("B").Cells(prng.Row, 2).Value
      Set prng = Nothing
    End If
  Next c
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 GetPrice macro.