Concat 2 column to 1 column in first and second excel, then VLOOKUP

(0) Share

Report

Posted on by nraindmia

Hi, I'm quite new to Power Automate Desktop.

But need help with this situation:

This is the scenario,

I have this Excel1.

ID	Company Name	UniqueID
001	ABCD	(How to concat ID and Company and become like this?) 001ABCD
002	EFGH	002EFGH
003	IJKL	003IJKL

Excel 2

ID	Company Name	UniqueID	Status
001	ABCD	001ABCD	Matched
002	IJKL	002IJKL	Transferred
003	IJKL	003IJKL	Matched

Based on the table:

1) I need to CONCAT 2 column which is ID and company name into UniqueID column. How to concat the ID and Company and write the value to UniqueID?

2) After that, I want to vlookup based on the uniqueID in excel 1 and excel 2. If the uniqueID same in excel1 and excel 2, put "Matched" at status column
3) If the unique ID is not same, put "Transferred".

4) For now, the logic is there. But if you have another solution, you can come with it. But the most important is that i want to know how to combine ID and Company Name and write into UniqueID. Is it possible?

Thank you !

Categories:

Power Automate Desktop

I have the same question (0)

All responses (12)

Answers (1)

Sort by

Agnius Bartninkas Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

You will need to use Read from Excel worksheet to read all of the data from your sheet. Then use Loop to loop from 0 to %ExcelData.RowsCount-1% (assuming you read your data into a variable called %ExcelData%, then %ExcelData.RowsCount% will store the number of rows in the data table, and the minus 1 is needed because loop indexes are 0-based). You will then be able to use Write to Excel worksheet and write the value of %ExcelData[LoopIndex]['ID']%%[ExcelData[LoopIndex]['Company Name']% back to Excel.

Make sure that you set the advanced option to get the first row of data as headers in the Read from Excel worksheet for this to work. And, when you write the Unique Id, back to Excel, make sure you use %LoopIndex+2% as the row to write to. The +2 is needed, because loop indexes are 0-based, as mentioned, and also because you have a header row at row 1 in the file.

The above will solve the "concat" task.

For the lookup, once you have this done, you can use Find or replace in data table action to search for a value in a table. So, if you have two tables extracted from two Excel files, you need to loop through one of them (again, with a Loop action set up the same way as above) and search for %ExcelData[LoopIndex]['UniqueID']% (assuming you use the same variable names - they might be different at this point, but I hope you get the idea) in the other table. If there are any matches, the result will be a data table with the row and column indexes of where a match was found. You can use a condition to check if %DataTableMatches% is empty. If it is, you know the status should be 'Transferred'. If the matches is not empty, it should be 'Matched'.

You can then write the status back to Excel using Write to Excel worksheet using the same logic as with the IDs.

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

Was this reply helpful? Yes No
nraindmia on at

Like
a
(1)

Report
Copy link

Link copied!

Hii! It works for the Concat function. But it keeps running even though there's no data. How to make it stop when there's no data to concat? Thank you !

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Try using Get first free column/row from Excel worksheet and instead of reading all data in Read from Excel worksheet, read a range that starts at column A and row 1, and ends at column %FirstFreeColumn-1% and row %FirstFreeRow-1%.

Alternatively, you can add a condition inside the loop that checks if a value in one of the columns is empty. If empty, use Exit loop to stop looping.

-------------------------------------------------------------------------

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.

Was this reply helpful? Yes No
nraindmia on at

Like
a
(0)

Report
Copy link

Link copied!

Hii ! thank youu it works now for the Concat. Really appriciate it !
Now, I have problem at the if condition,

I got error for the condition. Do you have any idea what to put in first operand to compare if first excel in column Unique ID is the same as second excel for column UniqueID, i will write as "Matched?"

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

You do not need an AND operator there. Simply put %ExcelData['UniqueID']% in the first operand and %ExcelData2['UniqueID']% in the second operand.

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

Was this reply helpful? Yes No
nraindmia on at

Like
a
(0)

Report
Copy link

Link copied!

Tried that before but this error prompt:

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

This is because you need to read your %ExcelData% from Excel again after writing the UniqueID back to Excel. You are using the original data table that did not have the column yet.

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

Was this reply helpful? Yes No
nraindmia on at

Like
a
(0)

Report
Copy link

Link copied!

Understand, I tried to read the excel again but still got error, then i try to save the previous excel first then read again the excel, but still error. Am i doing it wrong?

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Oh, right, you are trying to compare an entire column at once. This will not work. You need to loop through those tables again and compare each row individually.

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

Was this reply helpful? Yes No
nraindmia on at

Like
a
(0)

Report
Copy link

Link copied!

Can I know how to get it done?

Was this reply helpful? Yes No