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

[i8ur4re]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]QOH[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1013[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2135[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]



Sheet2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]QOH[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1013[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2954
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Fluff]

Have a look at the vlookup function
https://exceljet.net/excel-functions/excel-vlookup-function

 

[i8ur4re]

Have a look at the vlookup function
https://exceljet.net/excel-functions/excel-vlookup-function

Any chance i can just create a script to automate this, i would need to do this 3x daily.

 

[Fluff]

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]

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]

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]

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]

You failed to put the formula in when you recorder the macro, but try this

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