vlookup, INDEX, or SUMPRODUCT???

R

revampeng

New Member
Joined
Mar 7, 2024
Messages
6
Office Version
  1. 365
Platform
  1. Windows
This hopefully comes through okay. I am looking to automate the information going into columns G and H. I highlighted the date ranges to make this a bit clearer. In the real world I will have "Source 2" with samples every 10 minutes. "Source 1" is just whenever a fault occurs. The goal is to move the temperature and humidity that matches the date and time (within the 10 minute sample window) into those column G and H cells. For example if we look at March 1, 2024 on Source 2 I would want the 74.31 to go to G8 and 43.4 to go to H8. I have been toying with vlookup and I am struggling since the data sets are different sizes and the times will not line up due to the 10 minute sampling.

Hopefully this makes sense; let me know if it doesn't.

Question Example.xlsx
ABCDEFGHIJKLMNO
1Source 1Source 2
2
3F770 :Barcode Printer Error(14ST) and F773 :Printer Cycle Time Over(14ST)
4F-CodeF-Code Counts
5dateTime770773770 Count773 CountTemp °FHumidity %No.TimeTemperature°FHumidity%
6Friday, March 1, 202410:25:47 AM7707730012024-03-01 17:20:0073.8146.0
7Friday, March 1, 202412:30:38 PM7707730022024-03-01 17:30:0073.9245.8
8Friday, March 1, 20245:49:49 PM7707731032024-03-01 17:40:0074.3143.4
9Friday, March 1, 20246:40:30 PM7707731042024-03-01 17:50:0074.1044.4
10Friday, March 1, 20248:45:18 PM770773002202024-03-02 23:10:0073.8046.0
11Friday, March 1, 20249:05:26 PM770773102212024-03-02 23:20:0073.9045.8
12Friday, March 1, 202410:22:50 PM770773002222024-03-02 23:30:0074.4043.5
13Saturday, March 2, 202411:13:19 PM770773102232024-03-02 23:40:0074.3044.4
14Saturday, March 2, 202411:21:17 PM770773107512024-03-04 05:20:0074.2543.1
15Saturday, March 2, 202411:47:34 PM770773107522024-03-04 05:30:0074.3642.4
16Saturday, March 2, 202411:51:58 PM770773107532024-03-04 05:40:0074.4741.7
17Saturday, March 2, 20241:10:38 AM770773107542024-03-04 05:50:0074.5840.9
18Monday, March 4, 20245:14:03 AM7707730012102024-03-05 06:10:0074.6940.2
19Monday, March 4, 20245:21:30 AM7707731012112024-03-05 06:20:0073.8339.5
20Monday, March 4, 20245:30:15 AM7707731012122024-03-05 06:30:0073.9538.8
21Monday, March 4, 20245:33:19 AM7707731012132024-03-05 06:40:0075.0238.1
22Monday, March 4, 20245:40:51 AM7707730013202024-03-06 07:10:0075.1337.3
23Monday, March 4, 20245:44:28 AM7707731013212024-03-06 07:20:0075.2436.6
24Monday, March 4, 20246:04:54 AM7707730013222024-03-06 07:30:0073.5035.9
25Monday, March 4, 20246:07:06 AM7707731013252024-03-06 07:40:0072.7035.2
26Tuesday, March 5, 20246:17:46 AM77077310
27Tuesday, March 5, 20246:29:23 AM77077310
28Tuesday, March 5, 20246:35:09 AM77077310
29Tuesday, March 5, 20247:19:49 AM77077310
30Wednesday, March 6, 20247:21:33 AM77077310
31Wednesday, March 6, 20247:23:19 AM77077310
32Wednesday, March 6, 20247:24:01 AM77077310
33Wednesday, March 6, 20247:25:16 AM77077300
34Thursday, March 7, 20247:28:54 AM77077310
35Thursday, March 7, 20247:43:11 AM77077310
36Friday, March 8, 20247:50:22 AM77077300
37Friday, March 8, 20248:04:17 AM77077310
38Friday, March 8, 20248:17:45 AM77077310
39Saturday, March 9, 20248:18:47 AM77077300
40Saturday, March 9, 20248:22:13 AM77077310
Sheet1
 

Attachments

  • Question Example.PNG
    Question Example.PNG
    96.4 KB · Views: 26
Sort by date Sort by votes
Sounds good. And if the performance is too slow, have a look at the options implemented in my last post where the source range is trimmed down and COUNTA is used (but confirm intervening blank cells won't cause a problem).
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
138
SFCChase
SFCChase
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
118
Fluff
Fluff

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

Share this page

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