put the QTY to adjacent ID based on arranged sheet when compare two sheets

[abdo meghari]

Hello

I would populate data in RESULT sheet based on arranged data in PR sheet . should brings data columns D,E from PR sheet and brings QTY from STOCK sheet and put to adjacent cell for brought QTY from PR sheet based on matching BRAND column between two sheets.
I have about 3000 items for each sheet .
this is simple data

av.xlsm
	A	B	C	D	E	F	G
1	DATE	CLIENT NO	INVOICE NO	BRAND	QTY	UNIT PRICE	TOTAL
2	01/05/2021	CUS-BS-1	INV-BS-1	1200R20 G580 JAP	1,000.00	2,500.00	2,500,000.00
3	01/06/2021	CUS-BS-1	INV-BS-1	1200R20 G580 THI	10.00	2,660.00	26,600.00
4	01/08/2021	CUS-BS-1	INV-BS-1	1200R20 R187 THI	10.00	2,450.00	24,500.00
5	01/09/2021	CUS-BS-2	INV-BS-2	1200R24 G580 JAP	5.00	3,000.00	15,000.00
6	01/12/2021	CUS-BS-2	INV-BS-2	1400R20 VSJ JAP	12.00	4,000.00	48,000.00
7	02/12/2021	CUS-BS-3	INV-BS-3	1400R20 R180 JAP	11.00	4,000.00	44,000.00
PR

Cell Formulas
Range	Formula
G2:G7	G2	=E2*F2

av.xlsm
	A	B	C	D	E
1	ITEM	BRAND	REF NO	BATCH	QTY
2	1	1200R20 R187 THI	REF D#LL	BVGT602	4,000.00
3	2	1400R20 VSJ JAP	REF D#SS	BVGT604	1,000.00
4	3	1200R24 G580 JAP	REF D#KK	BVGT603	5,000.00
5	4	1200R20 G580 JAP	REF D#NN	BVGT600	2,000.00
6	5	1200R20 G580 THI	REF D#MM	BVGT601	3,000.00
7	6	1200R24 G580 THI	REF D#KK	BVGT603	5,000.00
STOCK

should be

av.xlsm
	A	B	C	D
1	ITEM	BRAND	QTY	QTY1
2	1	1200R20 G580 JAP	1,000.00	2,000.00
3	2	1200R20 G580 THI	10.00	3,000.00
4	3	1200R20 R187 THI	10.00	4,000.00
5	4	1200R24 G580 JAP	5.00	5,000.00
6	5	1400R20 VSJ JAP	12.00	1,000.00
7	6	1400R20 R180 JAP	11.00	0.00
8	7	1200R24 G580 THI	0.00	5,000.00
RESULT

 

[alansidman]

A Power Query solution

let
    T1 = Excel.CurrentWorkbook(){[Name="PR"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    MQ = Table.NestedJoin(T1, {"BRAND"}, T2, {"BRAND"}, "Table2", JoinKind.FullOuter),
    #"Renamed Columns" = Table.RenameColumns(MQ,{{"QTY", "QTY1"}}),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Renamed Columns", "Table2", {"BRAND", "QTY"}, {"BRAND.1", "QTY"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Table2",{{"QTY", "QTY2"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"BRAND", "QTY1", "BRAND.1", "QTY2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Brand.2", each if[BRAND]= null then [BRAND.1] else [BRAND]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"QTY1", "QTY2", "Brand.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns1",{"Brand.2", "QTY1", "QTY2"})
in
    #"Reordered Columns"

Book1
	A	B	C
10	Brand.2	QTY1	QTY2
11	1200R20 G580 JAP	1000	2000
12	1200R20 R187 THI	10	4000
13	1200R20 G580 THI	10	3000
14	1400R20 VSJ JAP	12	1000
15	1200R24 G580 JAP	5	5000
16	1200R24 G580 THI		5000
17	1400R20 R180 JAP	11
Sheet1

 

[abdo meghari]

Thanks
I want vba.

 

[abdo meghari]

I look forrward from experts' vba to help me.

 

[alansidman]

FYI in case you are not familiar:

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[abdo meghari]

by the way not always use the same version office. that's why I asked for VBA.

FYI in case you are not familiar:

Ok I will chek it .

 

[DanteAmor]

Hi @abdo meghari

Try this:

Sub put_QTY()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim a As Variant, b As Variant, c As Variant
  Dim dic1 As Object, dic2 As Object
  Dim i As Long, n As Long
  
  Application.ScreenUpdating = False
  
  Set sh1 = Sheets("PR")
  Set sh2 = Sheets("STOCK")
  Set sh3 = Sheets("RESULT")
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  
  a = sh1.Range("A2", sh1.Range("E" & Rows.Count).End(3)).Value
  b = sh2.Range("A2", sh2.Range("E" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1) + UBound(b, 1), 1 To 4)
  
  For i = 1 To UBound(a, 1)   'PR
    dic1(a(i, 4)) = a(i, 5)
  Next
  
  For i = 1 To UBound(b, 1)   'STOCK
    dic2(b(i, 2)) = b(i, 5)
  Next
  
  n = 0
  For i = 1 To UBound(a, 1)   'PR
    n = n + 1
    c(n, 1) = n
    c(n, 2) = a(i, 4)
    c(n, 3) = a(i, 5)
    If Not dic2.exists(a(i, 4)) Then c(n, 4) = 0 Else c(n, 4) = dic2(a(i, 4))
  Next
  
  For i = 1 To UBound(b, 1)   'STOCK
    If Not dic1.exists(b(i, 2)) Then
      n = n + 1
      c(n, 1) = n
      c(n, 2) = b(i, 2)
      c(n, 3) = 0
      c(n, 4) = b(i, 5)
    End If
  Next
  
  sh3.Range("A1:D1").Value = Array("ITEM", "BRAND", "QTY", "QTY1")
  sh3.Range("A2:E" & Rows.Count).ClearContents
  sh3.Range("A2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  sh1.Range("E1").Copy
  sh3.Range("A1:D1").PasteSpecial xlPasteFormats
  sh1.Range("D2").Copy
  sh3.Range("A2:B" & n + 1).PasteSpecial xlPasteFormats
  sh1.Range("E2").Copy
  sh3.Range("C2:D" & n + 1).PasteSpecial xlPasteFormats
  
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[abdo meghari]

Wow !
this is my lucky day .
it works greatly.
many thanks Dante for every thing .