Power Automate Desktop or Excel Macro Initiated by PAD?

(0) Share

Report

Posted on by MrLupin

My question has to do with identifying the best method to perform 2 tasks in Excel. I have the following data:

DATE Rate

11/30/2021	(0.00)
11/30/2021	(0.02)
11/30/2021	(0.01)
12/31/2021	0.00
12/31/2021	(0.01)
1/31/2022	(0.01)
5/31/2022	0.04
12/31/2022	0.13
1/15/2023	0.14
12/31/2023	0.30
12/31/2023	0.30
1/31/2024	0.32
6/30/2024	0.41
12/31/2024	0.51
12/31/2024	0.51
1/31/2025	0.54
1/31/2025	0.52
11/30/2025	0.68
12/31/2025	0.71
12/31/2025	0.70
1/31/2026	0.72
11/15/2026	0.80
11/30/2026	0.83
12/31/2026	0.83

I need to delete every row except for the last date of each year and format the rates as percentages:

12/31/2021	- .01%
12/31/2022	0.13%
12/31/2023	0.30%
12/31/2024	0.51%
12/31/2025	0.70%
12/31/2026	0.83%

Can this all be done with PAD or do I need to create a macro in Excel VB? I don't see any PAD actions that appear as if they will do this but I want to make sure I'm not missing anything. Any help would be appreciated.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
I’m a firm believer in writing the macro, and just using PAD to run it. It is insanely faster.

If you really just want to use PAD, here is the logic:

Get last row
create data table
set variable %datatablerowindex% to 0
Loop starting at %lastrow% to 1 (or 2 if you have a title row) increment -1
Read excel A and %loopindex%
if %loopindex% = %last row%
Add value to %DataTable[0][0]%
read excel B and %loopindex%
write to %Datatable[0][1]
next loop
#else if loopindex <> last row
Set %datatablerowindex% to %datatablerowindex+1%
read from A and %loopindex%
if %read% = %Datatable [datatablerowindex - 1][0]
next loop
#if %read% <> %Datatable [datatablerowindex - 1][0]
Add %read% to %DataTable[datatablerowindex][0]%
read excel B and %loopindex%
write to %Datatable[datatablerowindex][1]%
next loop
End Loop
The datable will be backwards, but you can sort it forward. If there is not a feature for sorting it, do a backwards “for each” and add it to another list making it go the right way. When writing to the datable, you should be able to say “write %read%%% to %Datatable[Datatablerowindex][1]%. The two extra %s should be a text % at the end.

Best of luck!

Was this reply helpful? Yes No
MrLupin 5 on at

Like
a
(0)

Report
Copy link

Link copied!

Wow! Thank you!! This is very very helpful!

Was this reply helpful? Yes No