How can i match data from one sheet to another to give me a result

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
I have two sheets, I would like to find and match both sku's on both sheets, if the sku from sheet1 matches the sku from sheet2, i want to extract a column from sheet 2.

Sheet1:
SKUQOH
10080
10110
10130
21350

<tbody>
</tbody>



Sheet2:

SKUQOH
10084
10116
101310
2954

<tbody>
</tbody>

Notice the last number, sheet1 will not have all of the sku's from sheet2, Sheet2 has over 5000 products, sheet1 has around 1000. So i would like to take the information from sheet2 to fill in sheet1 or create a new sheet that gives me the sku's and QOH.

Hope this makes sense.

Thank you in advance.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Any chance i can just create a script to automate this
I'm guessing the answer is no or you wouldn't be asking. ;)
Easiest way as you have given us virtually no information, is to turn on the macro recorder, put the formula into the sheet for all cells that need it & turn of the recorder. You then have the code, which can then be modified if needed.
 

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97

ADVERTISEMENT

I'm guessing the answer is no or you wouldn't be asking. ;)
Easiest way as you have given us virtually no information, is to turn on the macro recorder, put the formula into the sheet for all cells that need it & turn of the recorder. You then have the code, which can then be modified if needed.


I just dont know how to record macros or work on excel at that level, what i do know how to do is insert scripts. If you can help me out with that, it would be great.

I have two worksheets.

Main sheet - Daily Inventory-Amber POS

Secondary sheet - TradeGecko Inventory

Main sheet has all the records i need to update secondary sheet.

The only column i need from the "Main Sheet" is the QOH which is in column "P". This data will fill in the data on the "Secondary Sheet" column "AB".

The "Main Sheet" and the "Secondary sheet" have Sku's. I would like to match my current "Secondary Sheet" with the updated QOH (stock level) "Main Sheet".

I really hope this makes sense.

Sku's on the Main sheet are in column "A"
Sku's on the Secondary sheet are in column "Q"

TLDR: Take "Main Sheet" QOH and insert them into the "Secondary Sheet" using sku's to match whats current in the Secondary Sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
I just dont know how to record macros or work on excel at that level
It's very easy and something that you would be wise to learn.
On the Developer tab select "Record macro" then insert the formula you need into the first cell it's needed in & fill down to the last row of data. then select "Stop Recording" and yo have the code.
It's an excellent way of getting base code.
If you post the resultant code I will be happy to clean it up & make it dynamic.
 

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
It's very easy and something that you would be wise to learn.
On the Developer tab select "Record macro" then insert the formula you need into the first cell it's needed in & fill down to the last row of data. then select "Stop Recording" and yo have the code.
It's an excellent way of getting base code.
If you post the resultant code I will be happy to clean it up & make it dynamic.

Can you send me a link on how to record macros, i did what you explained to do, the code looked like this:

Sub Macro3()
'
' Macro3 Macro
'


'
Range("Q2").Select
Sheets("Daily Inventory-Amber POS").Select
Range("A6").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("P6").Select
Sheets("TradeGecko Inventory").Select
Range("AB2").Select
End Sub


I dont know what im doing. Im willing to learn.

Thank you,
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
You failed to put the formula in when you recorder the macro, but try this
Code:
Sub i8ur4re()
   Dim Lr As Long
   
   Lr = Sheets("Daily Inventory-Amber POS").Range("A" & Rows.Count).End(xlUp).Row
   With Sheets("TradeGecko Inventory")
      .Range("AB2:AB" & .Range("Q" & Rows.Count).End(xlUp).Row).Formula = _
         "=iferror(index('Daily Inventory-Amber POS'!p$2:P$" & Lr & ",match(Q2,'Daily Inventory-Amber POS'!A$2:A$" & Lr & ",0)),"""")"
   End With
End Sub
 

Forum statistics

Threads
1,141,298
Messages
5,705,565
Members
421,399
Latest member
hjweiss00

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
Top