trying to find which row this specific date time is at in an excel sheet

hi,

i have two excel sheets that have been read into matlab. im trying to find out what row number has the same datetime in excel2 from excel1.

NumRows = size(data1,1);  ----> i have found the last row from excel 1
ValueAtNumRow = data1.TestTime(NumRows,1) -----> found the datetime value from excel2

[row, col] = find(P3.Time(end) - ValueAtNumRow) ----> now i want to find what row has this value in excel2

please help. im still learning and have gotten a lot of errors so far.

thank you!

Answer 1

[Star Strider]

The ismember function is an option —

ValueAtNumRow = datetime('11-Sep-2023 22:25:26', 'InputFormat','dd-MMM-yyyy HH:mm:ss')

ValueAtNumRow = datetime
   11-Sep-2023 22:25:26

dt1 = datetime('11-Sep-2023 22:25:10', 'InputFormat','dd-MMM-yyyy HH:mm:ss') + seconds(1:20).'

dt1 = 20×1 datetime array
   11-Sep-2023 22:25:11
   11-Sep-2023 22:25:12
   11-Sep-2023 22:25:13
   11-Sep-2023 22:25:14
   11-Sep-2023 22:25:15
   11-Sep-2023 22:25:16
   11-Sep-2023 22:25:17
   11-Sep-2023 22:25:18
   11-Sep-2023 22:25:19
   11-Sep-2023 22:25:20
   11-Sep-2023 22:25:21
   11-Sep-2023 22:25:22
   11-Sep-2023 22:25:23
   11-Sep-2023 22:25:24
   11-Sep-2023 22:25:25
   11-Sep-2023 22:25:26
   11-Sep-2023 22:25:27
   11-Sep-2023 22:25:28
   11-Sep-2023 22:25:29
   11-Sep-2023 22:25:30

Lv = ismember (dt1, ValueAtNumRow);
Result = dt1(Lv)

Result = datetime
   11-Sep-2023 22:25:26

RowNr = find(Lv)                              % Desired Result

RowNr = 16

EDIT — (14 Sep 2023 at 21:58)

It would help to have your files. My code should work if there is a match between the dates.

Any approach that creates a logical vector result will produce zeros for the entries that do not match. You need to test only the datetime variables — not the entire file — in both files, so using find with two outputs is probably not appropriate if you only want to find the row number.

.

[Kayla Garcia]

thank you!!

[Star Strider]

My pleasure!

Answer 2

[dpb]

Presuming (although you didn't tell/show us) from the "dot" notation you used readtable to load the data into MATLAB,

NumRows=height(data1);      % the last row from excel 1 -- tables have a more convenient function than using size()
ix=(data2.TestTime==data1.TestTime(NumRows));    % logical vector of matching indices (rows) in second

Now use ix to retrieve the rows of interest from the second table.

Given both have time data, you should consider using a timetable instead of a plain table; it has many additional features specifically builtiin/available for handling time-related processing that could be very helpful.

Also, just as syntax and shortcut in MATLAB, the above could also be written as

ix=(data2.TestTime==data1.TestTime(end));

using the builtin. More on <indexing expression> is at the link; time invested in going thru the early syntax sections of the doc or the online OnRamp tutorial will pay back the time invested multiple times over in time (and frustration) saved.

[Kayla Garcia]

thank you!