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: 4
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 change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
102
etaf
etaf
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
311
awoohaw
awoohaw
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
566
juanam
J
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
373
kevin9999
K
M
  • Solved
Formula Help.
Replies
3
Views
63
Fluff
Fluff

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

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