Get info from the same row diffrent column

(0) Share

Report

Posted on by GiorgioG

So I have 2 excels

First one has col 1 row 1 Name1 , row 2 Name2 , row 3 Name3

Second one has col 1 row1 Name3, row 2 Name2 , row 3 Name1 and same more info in every row on col5

and I want to get the info from the same row col5 when the names match

like if Name3(first excel)=Name3(second excel) get same row Name3(second excel) col5

I don't know how to get the same row as the name

Thank for help

Categories:

Power Automate Desktop

All responses (8)

Answers (1)

Sort by

UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Could you please provide some screen shots of sample data and required output data, so that we can give a try....
GiorgioG 14 on at

Like
a
(0)

Report
Copy link

Link copied!

If name3(in first excel)=name3(second excel) output info3
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Got it will give a try and get back to you
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

ike if Name3(first excel)=Name3(second excel) get same row Name3(second excel) col5
this means if name matches in both excels then you need the value in info column right?
GiorgioG 14 on at

Like
a
(0)

Report
Copy link

Link copied!

That is right
Verified answer

UshaJyothiKasibhotla 225 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @GiorgioG

I have created a sample flow once you try it tell me if it meets your requirements. copy and paste the following code into the PAD designer

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\cogni\\OneDrive\\Desktop\\File1.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData1
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\cogni\\OneDrive\\Desktop\\File2.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance2
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance2 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData2
Variables.CreateNewList List=> List
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData1 ColumnNameOrIndex: $'''Name''' ColumnAsList=> ColumnAsList
LOOP FOREACH CurrentItem IN ColumnAsList
LOOP FOREACH CurrentItem2 IN ExcelData2
IF CurrentItem = CurrentItem2[0] THEN
Display.ShowMessageDialog.ShowMessage Title: $'''Result''' Message: CurrentItem2[4] Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
END
END
END

Hope this helps,
Usha.
GiorgioG 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you!!
Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

I would really suggest avoiding nested loops, as it's extremely inefficient to do that. Especially with larger files to be matched. Reading the data from both Excel files and then using Find or replace in data table is a much more efficient approach - this will return the row index for a matched row that can then be used to retrieve the values from other columns, too.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.