web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / How to put values into...
Power Automate
Unanswered

How to put values into second excel sheet based on the first excel sheet?

(0) ShareShare
ReportReport
Posted on by Rampage 45

Rampage_0-1646650110880.png

I want to perform this example into PAD

Categories:
Power Automate Desktop
I have the same question (0)
  • Guriga Profile Picture
    Guriga 188 on at

    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\USERNAME\\Downloads\\test1.xlsx''' Visible: True ReadOnly: True Instance=> ExcelSource
    Excel.GetFirstFreeRowOnColumn Instance: ExcelSource Column: $'''a''' FirstFreeRowOnColumn=> FirstFreeRowOnColumnSource
    Excel.ReadFromExcel.ReadCells Instance: ExcelSource StartColumn: 1 StartRow: 1 EndColumn: 3 EndRow: FirstFreeRowOnColumnSource - 1 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelDataSource
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\USERNAME\\Downloads\\Book2.xlsx''' Visible: True ReadOnly: False Instance=> ExcelDestination
    Excel.GetFirstFreeRowOnColumn Instance: ExcelDestination Column: $'''a''' FirstFreeRowOnColumn=> FirstFreeRowOnColumnDestination
    Excel.ReadFromExcel.ReadCells Instance: ExcelDestination StartColumn: 1 StartRow: 2 EndColumn: 4 EndRow: FirstFreeRowOnColumnDestination - 1 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelDataDestination
    SET varrow TO 2
    LOOP FOREACH CurrentItemSource IN ExcelDataSource
    LOOP FOREACH CurrentItemDestination IN ExcelDataDestination
    IF CurrentItemSource['column1'] = CurrentItemDestination['column1'] THEN
    SWITCH CurrentItemSource['column2']
    CASE = $'''Cycling'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 3 Row: varrow
    CASE = $'''Running'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 2 Row: varrow
    CASE = $'''Strength'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 4 Row: varrow
    END
    END
    Variables.IncreaseVariable Value: varrow IncrementValue: 1 IncreasedValue=> varrow
    END
    SET varrow TO 2
    END
    Excel.CloseExcel.Close Instance: ExcelSource
    Excel.CloseExcel.CloseAndSave Instance: ExcelDestination

     

    Guriga_0-1646652552447.png

     

  • Rampage Profile Picture
    Rampage 45 on at

    What if i have multiple excel files with the similar type of data

    Rampage_0-1646653152522.png

    Want to insert the data according to their excel file name

    Rampage_1-1646653203359.png

    is this possible in PAD?

  • Guriga Profile Picture
    Guriga 188 on at

    Yes, it's possible.

    It's a very simple basic solution. It works until you don't change the name of the files.

    Otherwise, you need to update the flow.

    I made it for two names just for testing but you can add as many as you need.

     

    MAIN:

    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\USERNAME\\Downloads\\Book2.xlsx''' Visible: True ReadOnly: False Instance=> ExcelDestination
    Excel.GetFirstFreeRowOnColumn Instance: ExcelDestination Column: $'''a''' FirstFreeRowOnColumn=> FirstFreeRowOnColumnDestination
    Excel.ReadFromExcel.ReadCells Instance: ExcelDestination StartColumn: 1 StartRow: 3 EndColumn: 4 EndRow: FirstFreeRowOnColumnDestination - 1 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelDataDestination
    Display.SelectFileDialog.SelectFiles Title: $'''Open file''' InitialDirectory: $'''C:\\Users\\USERNAME\\Downloads''' FileFilter: $'''*.xlsx''' IsTopMost: True CheckIfFileExists: True SelectedFiles=> SelectedFiles ButtonPressed=> ButtonPressed
    LOOP FOREACH CurrentItemFiles IN SelectedFiles
    SWITCH CurrentItemFiles.NameWithoutExtension
    CASE = $'''Name1'''
    CALL Name1
    CASE = $'''Name2'''
    CALL Name2
    END
    END
    Excel.CloseExcel.CloseAndSave Instance: ExcelDestination

     

    Subflow Name1

    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItemFiles.FullName Visible: True ReadOnly: True Instance=> ExcelSource
    Excel.GetFirstFreeRowOnColumn Instance: ExcelSource Column: $'''a''' FirstFreeRowOnColumn=> FirstFreeRowOnColumnSource
    Excel.ReadFromExcel.ReadCells Instance: ExcelSource StartColumn: 1 StartRow: 1 EndColumn: 3 EndRow: FirstFreeRowOnColumnSource - 1 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelDataSource
    SET varrow TO 3
    LOOP FOREACH CurrentItemSource IN ExcelDataSource
    LOOP FOREACH CurrentItemDestination IN ExcelDataDestination
    IF CurrentItemSource['column1'] = CurrentItemDestination['column1'] THEN
    SWITCH CurrentItemSource['column2']
    CASE = $'''Cycling'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 3 Row: varrow
    CASE = $'''Running'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 2 Row: varrow
    CASE = $'''Strength'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 4 Row: varrow
    END
    END
    Variables.IncreaseVariable Value: varrow IncrementValue: 1 IncreasedValue=> varrow
    END
    SET varrow TO 3
    END
    Excel.CloseExcel.Close Instance: ExcelSource

     

    Subflow  Name2

    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItemFiles.FullName Visible: True ReadOnly: True Instance=> ExcelSource
    Excel.GetFirstFreeRowOnColumn Instance: ExcelSource Column: $'''a''' FirstFreeRowOnColumn=> FirstFreeRowOnColumnSource
    Excel.ReadFromExcel.ReadCells Instance: ExcelSource StartColumn: 1 StartRow: 1 EndColumn: 3 EndRow: FirstFreeRowOnColumnSource - 1 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelDataSource
    SET varrow TO 3
    LOOP FOREACH CurrentItemSource IN ExcelDataSource
    LOOP FOREACH CurrentItemDestination IN ExcelDataDestination
    IF CurrentItemSource['column1'] = CurrentItemDestination['column1'] THEN
    SWITCH CurrentItemSource['column2']
    CASE = $'''Cycling'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 6 Row: varrow
    CASE = $'''Running'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 5 Row: varrow
    CASE = $'''Strength'''
    Excel.WriteToExcel.WriteCell Instance: ExcelDestination Value: CurrentItemSource['column3'] Column: 7 Row: varrow
    END
    END
    Variables.IncreaseVariable Value: varrow IncrementValue: 1 IncreasedValue=> varrow
    END
    SET varrow TO 3
    END
    Excel.CloseExcel.Close Instance: ExcelSource

     

    Guriga_0-1646656345885.png

    Hope the content above may help you.

    Best Regards

    If my answer helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • MichaelAnnis Profile Picture
    MichaelAnnis 5,727 Moderator on at

    This will be 100 times faster as an Excel Macro in Excel VBA.  Then you can just call PAD to run the VBA.

  • Guriga Profile Picture
    Guriga 188 on at

    @MichaelAnnis 
    Hi, 

    Can you show me how, please?

    I'm not familiar with Excel Macro in Excel VBA but never too late to learn new things.

  • MichaelAnnis Profile Picture
    MichaelAnnis 5,727 Moderator on at

    Unfortunately, I don't have a lot of time this week.  I might have some on Wednesday.  Use the Macro Recorder and record yourself doing the following.

     

    On workbook1, Select column D, right click, Insert column

    In C1, enter formula =A1&B1

    Autofill the formula down

    In workbook2, in B2, enter the formula:  =iferror(vlookup($A2&B$1,workbook1$C:$D,2,false),"")

    Autofill the formula right

    Autofill the formula down

    Stop recording

     

    The formula concatenates the date (in column A to the left) and the activity (in row 1 above) and uses that as the criteria to lookup in column C returning the text value in column D.  If no match exists (iferror), then nothing is displayed (that is the "" part).

     

    Once the recording is done, post it here, and I can fix it so it works on any size spreadsheet by getting the last row/column.

     

    Best of luck!

  • Rampage Profile Picture
    Rampage 45 on at

    Yeah i tried with @Guriga method, and in my case there are 1000+ data and its taking too much time to do this work..... can you give me the example macro file for this ?

  • Guriga Profile Picture
    Guriga 188 on at

    I didn't do it with macro just simply added the formulas.

    It's working fine.

    Guriga_0-1646676458836.png

     

  • Rampage Profile Picture
    Rampage 45 on at

    can you share this two excel file with me?

  • MichaelAnnis Profile Picture
    MichaelAnnis 5,727 Moderator on at

    Congrats...glad it worked.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Valantis Profile Picture

Valantis 427

#2
Vish WR Profile Picture

Vish WR 316

#3
David_MA Profile Picture

David_MA 260 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

Known issue with browser automation in Power Automate for Desktop on Google Chrome 146 and 147
Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Flows are not visible for users who sign in with their personal accounts
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans