Help required to Identify Cell reference (Dynamic) from big Excel file with multiple headers an 100's of rows, columns

Help Studio
, ,
1

Hi All,

As a new developer, I am having difficulty to locate the Cell reference of EG134 which is dynamic every year as the report has multiple repetitive headers and 100’s of rows&cols. Kindly help.

• First we need to filter A and B cols with highlighted values below,
• then locate header “(Unit) Cost”,
• then under it, locate Year(2023) based on current date,
• then locate -1 month “Jul” month based on Current date(note: considering current month is Aug),
• then identify the cell EG134 and write data(assume value from an array or Datarow) there,
• and till year end Dec month of that current year i have to write data in loop.

The same i have to do for “Group (Unit) Cost” located at “GU” column.
Note: The years and months headers are repetitive under each section. i have hide many columns and rows in below image for better understanding.

image
image1868×271 36.3 KB

Additional question: I have to repeat this process for 100’s of similar reports(~5MB). i know the Read range for such individual big file take a lot of time. Is there a way we can do this faster? pls help.

2

Hi @sivakumar.a ,
I think filter data get only column need get and write data
In this data is A,B, EG,GU
for loop, you can create a variable index (int32) =1 (or start cell need write)
each loop assign index = index+1
Regards,
LNV

3

Hi @Nguyen_Van_Luong1

Columns EG, GU are not fixed, in future, the data on these columns may move to different columns. Even the row number of “Others” and “CBF” might change. So we have to identify the cell based on the headers “(Unit) Cost”, current year “2023”, current month -1 >> “Jul” month, and get the cell index of 4,624 value. Then i will use write cell to overwrite this number with updated numbers. similarly for Aug, Sep till Dec of current year one by one.

This whole step again for “Group(Unit)Cost” column>> 2023>> Jul and write the number there, till Dec.