VBA Code needed look up data from one sheet and pasting on another sheet

[k03074]

Hello,

Here is my problem that I can't find a solution for, I looking for VBA code that will search for sheet 1, column E (Dock Assignment) and match it to sheet 2, column A4 or F4 or K4 or P4 and if found then copy Sheet 1 Column A row that matches dock to Sheet 2 either column A, F, K or P. Then repeat to Column B and C (Sheet 2) from data from Sheet 1.

Here is my data from sheet 1

Customer	Expect Time IN	Expect Time Out	Description	Dock Assignment
Customer 1	16:45	17:45	SCAC TR4N	Dock 2
Customer 2	3:00	4:00	SCAC KRMX	Dock 3
Customer 15	6:00	7:00	TNAP 02462 (Dock 5C) SCAC AUBA	Dock 3
Customer 12	8:45	9:30	SCAC HLGI--Pool 00143	Dock 3
Customer 6	11:00	12:00	SCAC WKTH CT/PAT	Dock 3
Customer 4s	12:00	13:00	CANAL	Dock 3
Customer 8	14:00	14:30	09126G (GS / GEIB)	Dock 3
Customer 3	15:30	16:30	SCAC GUOO (ALL PLANTS---except 48065/48075)	Dock 3
Customer 11	18:00	19:00		Dock 3
Customer 31	23:00	23:59	18171 (23:00-23:59) SCAC SYFN	Dock 3
Customer 12	23:00	0:00	SCAC RHT PU Sunday night	Dock 3
Customer 7	9:35	10:25	RTE SRCE-01	Dock 4
Customer 5	11:30	12:00	ROUTE M7018 (PSKL / C/ DDCS) ALL ASSEMBLY PLANTS	Dock 4
Customer 23	14:00	14:30	48065 SCAC CMFH	Dock 4
Customer 18	15:00	16:00	48075 T1XX SCAC GEIB	Dock 4
Customer 6	16:00	17:00	SCAC WKTH CT/PAT	Dock 4
Customer 10	19:30	20:30	MILKRUN SCAC CNAL	Dock 4
Customer 1	1:00	2:00	SCAC TR4N	Dock 5

<tbody>
</tbody>

And Sheet 2

Dock 2

Dock 3

Dock 4

Dock 5

Customer

Expected Arrived Time

Departure Time

Time In

Time Out

Customer

Expected Arrived Time

Departure Time

Time In

Time Out

Customer

Expected Arrived Time

Departure Time

Time In

Time Out

Customer

Expected Arrived Time

Departure Time

Time In

Time Out

Customer 1

16:45

17:45

<tbody>
</tbody>

Any suggestions would help and appreciate.

Thank you,
Gerald

 

[D_Mon]

Hi Gerald,

you might wat to tae a look at the Range.Find Method.

HTH
D.Mon

 

[Fluff]

How about

Sub k03074()
   Dim Cl As Range, Fnd As Range
   Dim Ky As Variant
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
   With CreateObject("Scripting.dictionary")
      For Each Cl In Ws1.Range("E2", Ws1.Range("E" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, -4).Resize(, 3)
         Else
            Set .item(Cl.Value) = Union(.item(Cl.Value), Cl.Offset(, -4).Resize(, 3))
         End If
      Next Cl
      For Each Ky In .Keys
         Set Fnd = Ws2.Range("4:4").Find(Ky, , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            .item(Ky).Copy Fnd.End(xlDown).Offset(1)
         End If
      Next Ky
   End With
End Sub

 

[k03074]

Hi Gerald,

you might wat to tae a look at the Range.Find Method.

HTH
D.Mon

Thank you I will look into this.

Cheers,
Gerald

 

[k03074]

Hello Fluff

I will also try this out.

Thank you,
Gerald

 

[k03074]

Hello Fluff,

Thank you for you code here. Work like a charm. I would have never got this in a million years.

Thank you so much.

Cheers,
Gerald

 

[Fluff]

You're welcome & thanks for the feedback