If else function and duplicate data did not appear

[nraindmia]

Hi all,

I'm new to Power Automate Desktop,

I created this if else flow. But the result in excel did not appear if the employee number is duplicate. Supposedly it will appear either "Matched", "Transferred" or "Terminated". why did in the excel did not appear any status if the number is duplicate? I want to make the status appear even though the data is duplicate.
eg:

1001: Matched

1001: Matched

1078: Transferred
1056: Matched
1078: Transfered

Thank you 🙂

[Nived_Nambiar]

Hi @nraindmia 

Could you show me the entire code for this and share what you are trying to do here to get an idea around the flow 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[nraindmia]

Sure !

For now, when they found the duplicate data, they will not put any status inside the column because the status has been write for the previous data. I want the status to be write for all even though it is duplicate data and has been written the status before. I don't want the status to be empty.

[Agnius]

That is because you are using the Find and replace cells in Excel worksheet to only return the first match. Enable the "All matches" toggle to find all duplicate values:

Note that this will return a data table of matches, called %Cells%, instead of two separate variables for indexes. You will need to loop through this data table to handle each match separately.

-------------------------------------------------------------------------
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.

[nraindmia]

can you show me which line to toggle it on for find and replace? I have 3 find and replace. do i need to toggle for all 3? 

[Agnius]

The way I understand your issue, is that you need it for the last line (line #20). Not sure if you need it for the others, as you're not really using the other indexes to write anything, and just to read data.

-------------------------------------------------------------------------
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.

[nraindmia]

I change into cells and it appear this error :

[Agnius]

As mentioned, %Cells% will now be a data table with multiple row and column indexes. You need to build a loop on it and process each value individually.

[nraindmia]

How to build a loop on it and process each value individually? sorry 😞

[Agnius]

Do a For each loop on %Cells% storing the current item in %CurrentCell%.

When you do that, you can use %CurrentCell['Row']% to get the row index and %CurrentCell['Column']% to get the column index. You might need to use Convert text to number to convert those indexes to integers, as they are usually stored as text by PAD in tables for some reason.

-------------------------------------------------------------------------
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.

[nraindmia]

I don't really get what to do actually, still got an error. or you can show me a simple example of how to do it? 

[nraindmia]

Hi, there's no error for this but the result is still the same, duplicate data is not written 😞 Did you guys have any idea about this? searched on youtube and google but there's no case that same as mine...

[Nived_Nambiar · Accepted solution]

Hi @nraindmia 

I have created an example for your question. 

Before that, i assumed that what you are trying to do is , bot pick up each row from ExcelPMCareInstance and checks whether the employee ID exists in ExcelStaffInstance, if yes then check for whether the given company for current row is in the ExcelStaffInstance, if yes update the status column of corresponding row as Matched- rowindex , else transferref and else emp id is not found in ExcelStaffInstance, then update status column as Terminated, hope i am on right track. if it has any correction, let me know 🙂

Here is the flow 

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\Data1.xlsx''' Visible: True ReadOnly: False Instance=> ExcelPMCareInstance
Excel.GetFirstFreeColumnRow Instance: ExcelPMCareInstance FirstFreeColumn=> FirstPMCareFreeColumn FirstFreeRow=> FirstPMCareFreeRow
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\Data2.xlsx''' Visible: True ReadOnly: False Instance=> ExcelStaffInstance
Excel.GetFirstFreeColumnRow Instance: ExcelStaffInstance FirstFreeColumn=> FirstStaffFreeColumn FirstFreeRow=> FirstStaffFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelStaffInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstStaffFreeColumn - 1 EndRow: FirstStaffFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelStaffData
Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Status''' Column: FirstPMCareFreeColumn Row: 1
Excel.SaveExcel.Save Instance: ExcelPMCareInstance
Excel.GetFirstFreeColumnRow Instance: ExcelPMCareInstance FirstFreeColumn=> FirstPMCareFreeColumn FirstFreeRow=> FirstPMCareFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelPMCareInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstPMCareFreeColumn - 1 EndRow: FirstPMCareFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelPMCareData
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelPMCareData ColumnNameOrIndex: 0 ColumnAsList=> EmpIDList
Variables.RemoveDuplicateItemsFromList List: EmpIDList IgnoreCase: False
LOOP FOREACH EmpID IN EmpIDList
    Excel.FindAndReplace.FindAll Instance: ExcelPMCareInstance TextToFind: EmpID MatchCase: False MatchEntireCellContents: True SearchBy: Excel.SearchOrder.Rows Cells=> Cells
    Excel.FindAndReplace.FindSingle Instance: ExcelStaffInstance TextToFind: EmpID MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex
    IF FoundRowIndex <> 0 THEN
        LOOP FOREACH row1 IN Cells
            IF ExcelPMCareData[row1['RowIndex'] - 2][1] = ExcelStaffData[FoundRowIndex - 2][1] THEN
                Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Matched-%FoundRowIndex%''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
            ELSE
                Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Transferred''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
            END
        END
    ELSE
        LOOP FOREACH row1 IN Cells
            Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Terminated''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
        END
    END
END
Excel.CloseExcel.Close Instance: ExcelStaffInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelPMCareInstance

Also here I am attaching two excel files with this post used for this , one is Data1.xlsx which resembles the for ExcelPMCareInstance and Data2.xlsx which resembles ExcelStaffInstance.

The excel Data1.xlsx has Status column which has required values updated as part of run. You can check the files and flow and see how you can effienctly design the solution 🙂

Also i think you can make flow more better by using some scripts in between, let me know your thoughts once you confirm the above one is a basic design of your flow 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[nraindmia]

Hii! Your flow really helpful! It works !!

Thank you!!

[nraindmia]

Hi, when i read again your flow, can you explain this if condition? I don't really understand the condition. But your flow is what im looking for !

[Nived_Nambiar]

Hi @nraindmia 

It checks whether the company name in ExcelPMCareData is equal to company name in ExcelStaffData corresponding to row where the employee id is found in excelstaffdata 🙂

Hope this helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[nraindmia]

I see, but i got error there 😞

Just to let you know that both excel file have a different column and name. Here i attach the file excel for your reference.

[Nived_Nambiar]

Hi @nraindmia 

Could you try this :

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\PMCare file.xlsx''' Visible: True ReadOnly: False Instance=> ExcelPMCareInstance
Excel.GetFirstFreeColumnRow Instance: ExcelPMCareInstance FirstFreeColumn=> FirstPMCareFreeColumn FirstFreeRow=> FirstPMCareFreeRow
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\StaffFile.xlsx''' Visible: True ReadOnly: False Instance=> ExcelStaffInstance
Excel.GetFirstFreeColumnRow Instance: ExcelStaffInstance FirstFreeColumn=> FirstStaffFreeColumn FirstFreeRow=> FirstStaffFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelStaffInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstStaffFreeColumn - 1 EndRow: FirstStaffFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelStaffData
Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Status''' Column: FirstPMCareFreeColumn Row: 1
Excel.SaveExcel.Save Instance: ExcelPMCareInstance
Excel.GetFirstFreeColumnRow Instance: ExcelPMCareInstance FirstFreeColumn=> FirstPMCareFreeColumn FirstFreeRow=> FirstPMCareFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelPMCareInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstPMCareFreeColumn - 1 EndRow: FirstPMCareFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelPMCareData
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelPMCareData ColumnNameOrIndex: 5 ColumnAsList=> EmpIDList
Variables.RemoveDuplicateItemsFromList List: EmpIDList IgnoreCase: False
LOOP FOREACH EmpID IN EmpIDList
    Excel.FindAndReplace.FindAll Instance: ExcelPMCareInstance TextToFind: EmpID MatchCase: False MatchEntireCellContents: True SearchBy: Excel.SearchOrder.Rows Cells=> Cells
    Excel.FindAndReplace.FindSingle Instance: ExcelStaffInstance TextToFind: EmpID MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex
    IF FoundRowIndex <> 0 THEN
        LOOP FOREACH row1 IN Cells
            IF ExcelPMCareData[row1['RowIndex'] - 2][7] = ExcelStaffData[FoundRowIndex - 2][0] THEN
                Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Matched-%FoundRowIndex%''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
            ELSE
                Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Transferred''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
            END
        END
    ELSE
        LOOP FOREACH row1 IN Cells
            Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Terminated''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
        END
    END
END
Excel.CloseExcel.Close Instance: ExcelStaffInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelPMCareInstance

I have changed the columns index in the flow, Hope it will work. I have some issues in my system while running the flow using your excel file, seems like my security system in my personal laptop is blocking me from accessing the excel 🙂

Let me know your update after trying this :

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[nraindmia]

Looks like it's not working. I only get the status "Transferred" for all 😞

[Nived_Nambiar]

Hi @nraindmia 

Try this with code and see whether it works for you or not 🙂

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\PMCare file.xlsx''' Visible: True ReadOnly: False Instance=> ExcelPMCareInstance
Excel.GetFirstFreeColumnRow Instance: ExcelPMCareInstance FirstFreeColumn=> FirstPMCareFreeColumn FirstFreeRow=> FirstPMCareFreeRow
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\StaffFile.xlsx''' Visible: True ReadOnly: False Instance=> ExcelStaffInstance
Excel.GetFirstFreeColumnRow Instance: ExcelStaffInstance FirstFreeColumn=> FirstStaffFreeColumn FirstFreeRow=> FirstStaffFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelStaffInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstStaffFreeColumn - 1 EndRow: FirstStaffFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelStaffData
Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Status''' Column: FirstPMCareFreeColumn Row: 1
Excel.SaveExcel.Save Instance: ExcelPMCareInstance
Excel.GetFirstFreeColumnRow Instance: ExcelPMCareInstance FirstFreeColumn=> FirstPMCareFreeColumn FirstFreeRow=> FirstPMCareFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelPMCareInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstPMCareFreeColumn - 1 EndRow: FirstPMCareFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelPMCareData
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelPMCareData ColumnNameOrIndex: 5 ColumnAsList=> EmpIDList
Variables.RemoveDuplicateItemsFromList List: EmpIDList IgnoreCase: False
LOOP FOREACH EmpID IN EmpIDList
Excel.FindAndReplace.FindAll Instance: ExcelPMCareInstance TextToFind: EmpID MatchCase: False MatchEntireCellContents: True SearchBy: Excel.SearchOrder.Rows Cells=> Cells
Excel.FindAndReplace.FindSingle Instance: ExcelStaffInstance TextToFind: EmpID MatchCase: False MatchEntireCellContents: True SearchBy: Excel.SearchOrder.Rows FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex
IF FoundRowIndex <> 0 THEN
LOOP FOREACH row1 IN Cells
IF ExcelPMCareData[row1['RowIndex'] - 2][7] = ExcelStaffData[FoundRowIndex - 2][0] THEN
Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Matched-%FoundRowIndex%''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
ELSE
Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Transferred''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
END
END
ELSE
LOOP FOREACH row1 IN Cells
Excel.WriteToExcel.WriteCell Instance: ExcelPMCareInstance Value: $'''Terminated''' Column: FirstPMCareFreeColumn - 1 Row: row1['RowIndex']
END
END
END
Excel.CloseExcel.Close Instance: ExcelStaffInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelPMCareInstance

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌