Finding cell with specific text and then pasting that in to another cell in the same worksheet

(1) Share

Report

Posted on by NF-31071407-0

I need help telling power automate to find the cell that contains the Due By (date), which is always listed at the bottom of the data set with 2 blank rows in between. Once it finds that cell, I want to copy the entire contents and paste it in the column 'Due By' for the range of cells that contain data.

Here is an example of what the sheet looks like:

Name	Book	Due By
Joe F.	The Bee Sting
Lynn G.	The Color Purple


Due By 8/9/24

I want it to look like this:

Name	Book	Due By
Joe F.	The Bee Sting	Due By 8/9/24
Lynn G.	The Color Purple	Due By 8/9/24

Thank you for any help you can provide!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

NsL Coder 475 Super User 2025 Season 2 on at

Like (1)

Report
You have 2 options and both is not easy:

Graph API - https://learn.microsoft.com/en-us/graph/api/range-lastcolumn?view=graph-rest-1.0

You will need to learn how to issue Graph API call

You will need to use Graph API to determine the range used, which may or may not have the last row with Due by (you have to test it) and then add 3 rows or not depending on that

Office Script - https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel

You can invoke an Office Script from PA flow

You will need to learn "high code" as office script is TypeScript which is a stricter version of JavaScript

Same process though, use the scripting code to determine the used range and get the last row +3 or not depending on if the Due by row is included in used range

If I were to do this, I would use Office Script, slightly easier and you can actually "record" the script in Excel 365 by performing the action in excel which will turns into code.

Was this reply helpful? Yes No
Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (1)

Report

Hi @NF-31071407-0

Few things need to know

1. Should it be done using power automate desktop /cloud flow ?

2. Does the due by date text is always present at bottom of dataset under first column?

3. Once you find the value - do you need to remove that value Due By present in last row ?

Thanks & Regards,
Nived N
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs
Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you!

Was this reply helpful? Yes No
gabrielp 2 on at

Like (1)

Report

Using the loop condition, loop through the excel worksheet to look for your text.

In the example below, I looked for the text 'Total'

Was this reply helpful? Yes No
NF-31071407-0 4 on at

Like (1)

Report

Hi, @Nived_Nambiar,

To answer your questions:

1. Should it be done using power automate desktop /cloud flow ?

- Using power automate desktop preferably since there are additional steps I will need to do after this one every day when I receive a new file. I was trying to automate the entire process through PAD, but maybe there is a better way?

2. Does the due by date text is always present at bottom of dataset under first column?

- Yes, it is always present at the bottom of the dataset under the first column

3. Once you find the value - do you need to remove that value Due By present in last row ?

- Yes, I generally delete the row with the Due By text after it has been copied over to the Due By column for each record.

Was this reply helpful? Yes No
Suggested answer

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (3)

Report
Hi @

Try this approach

Copy the code as shown below

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Nived\\Downloads\\sampledata.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance2 Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance2 Name: $'''Sheet1''' Excel.GetFirstFreeColumnRow Instance: ExcelInstance2 FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.ReadFromExcel.ReadCells Instance: ExcelInstance2 StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: True FirstLineIsHeader: True RangeValue=> ExcelData2 Variables.FilterDataTable DataTable: ExcelData2 FilterParameters: { ^['RULE', 'COLUMN', 'OPERATOR', 'VALUE'], [$'''''', 0, $'''StartsWith''', $'''Due By'''] } FilteredDataTable=> FilteredDataTable2 SET DueByDate TO FilteredDataTable2[0][0] Variables.FilterDataTable DataTable: ExcelData2 FilterParameters: { ^['RULE', 'COLUMN', 'OPERATOR', 'VALUE'], [$'''''', 0, $'''NotStartsWith''', $'''Due By'''], [$'''And''', 0, $'''IsNotEmpty''', $''''''] } FilteredDataTable=> FilteredDataTable2 LOOP LoopIndex FROM 0 TO FilteredDataTable2.RowsCount - 1 STEP 1 SET FilteredDataTable2[LoopIndex][2] TO DueByDate END Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: FilteredDataTable2 Column: $'''A''' Row: 2 Excel.DeleteRow Instance: ExcelInstance2 Index: FirstFreeRow - 1 Excel.CloseExcel.CloseAndSave Instance: ExcelInstance2

See how the final excel data looks like

Thanks & Regards,
Nived N
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs
Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you!

Was this reply helpful? Yes No