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

k03074

Board Regular
Joined
Mar 28, 2013
Messages
57
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
How about
Code:
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
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top