Find the row index based on column combination

Hi all, I have an excel file which I need to update. Basically there are 3 columns which are:
MonthYear, Day, SalesData

Based on today’s date, I need to find the row index so that I can write the Sales Data into the correct row. In order to do this, I need to check two column which are MonthYear and Day. I saw there is Find/Replace function in the Studio but it only works for a single column. The reason why I don’t want to read the file and filter it by using Filter Datatable is because the number of rows is huge and it may impact the performance of the machine.

Any suggestion on this?

Thanks,
Mirai

pls send the sample file, input and output result you want.

Hi,

For example, let’s assume your sheet is as the following.

image

Then, the following expression will return index of datatable. Please note that we need to offset +2 to get excel row index because there is header and datatable 0 based index.

dt.Rows.IndexOf(dt.AsEnumerable.Where(Function(r) r("MonthYear").ToString=Now.ToString("MMyyyy") AndAlso r("Day").ToString=Now.ToString("dd")).FirstOrDefault)

Sample20220822-3.zip (8.7 KB)

Hope this helps you.

Regards,

Hi Mr.H,

Here is a sample file with each sheet containing the input file, output file and the desired output sheet.

Sample File.xlsx (12.0 KB)

As you can see in the file, there are 2 sample data for 22nd of August and September in Input File sheet. Based on that date, I want to find the correct row in Output File sheet based on column MonthYear and Day and write the into the SalesData column.

Thanks,
Mirai

Hi Yoichi,

Thank you for the sample code. If we go with your way, I will need to read the whole excel file which can be quite risky as the number of rows there is huge and may causes some performance issue on the machine. Would 10k rows will be a problem if we go with your solution?

Thanks,
Mirai

HI,

10k rows

Around 10k rows is no problem to handle. Can you try the following sample?
Compatibility of this sample project is Windows( .net6) to enable to handle huge data just to be sure. However, it will work even if compatibility is Windows-Legacy, I think.

Sample20220822-2net.zip (21.9 KB)

Regards,

Another way you can check.

  1. Edit the Input_File into same format with Output_file.
  2. Join DataTable to find the match value.

Find match.zip (78.1 KB)

Another Linq way
Find match.zip (78.4 KB)

I have customized code into the short way as attached file.
Find match_Final.zip (77.8 KB)