Lookup value in sheet 1 transpose value from sheet 2 & the value in next column to sheet 1

[BoyBoy]

Hello,
I am trying to update one of my worksheet using VBA Code, by look up value from sheet 1 in column A, search the same value in sheet 2 column A (there will be duplicates), and return the value from column A and the associate value in sheet 2 column B to sheet 1 from column F to column AJ+ (depend how many storage in sheet 2 column B, could be 50+ storages)
Sheet 2 is somewhat look like this:

following is the Sheet 1 and the result data (yellow highlighted)

A simple working VBA code would be highly appreciated.
Thanks.
BoyBoy

 

[BoyBoy]

If that can happen, then try this version.

Sub Rearrange_v2()
  Dim a As Variant, b As Variant, c As Variant, j As Variant
  Dim i As Long, k As Long
 
  Const MaxStorages As Long = 100 '<- Edit this if required
 
  With Sheets("Sheet2")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp).Offset(1)).Value
    c = Application.Index(a, 0, 1)
  End With
  With Sheets("Sheet1")
    b = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
    ReDim Preserve b(1 To UBound(b), 1 To MaxStorages + 1)
    For i = 1 To UBound(b)
      j = Application.Match(b(i, 1), c, 0)
      If IsNumeric(j) Then
        k = 2
        Do
          b(i, k) = a(j, 2)
          k = k + 1
          j = j + 1
        Loop Until a(j, 1) <> b(i, 1)
      End If
    Next i
    .Range("F2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End With
End Sub

My sample data

BoyBoy.xlsm
	A	B
1
2	No 9	A
3	No 9	B
4	No 9	C
5	No 9	D
6	No 9	E
7	No 9	F
8	No 9	G
9	No 9	H
10	No 9	I
11	No 8	J
12	No 8	K
13	No 8	L
14	No 8	M
15	No 4	N
16	No 4	O
17	No 4	P
18	No 4	Q
19	No 4	R
20	No 4	S
21	No 4	T
22	No 4	U
23	No 4	V
24	No 4	W
25	No 4	X
Sheet2

.. and results. Note that No 44 does not appear in Sheet2 above.

BoyBoy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	No 9					No 9	A	B	C	D	E	F	G	H	I
3	No 44					No 44
4	No 8					No 8	J	K	L	M
Sheet1

Yes, you are right, the example I used to try on the first code did have the model that's not in sheet 2.
Thank you so much, the v2 works like a charm!
Have a nice day
BoyBoy

 

[Peter_SSs]

Cheers. Glad to help.