Performing vlookup with Combining the data within cell

[nirkisna65]

I have two files which would need Vlookup to be performed to update the fields for other, the twist is i need the information of the columns to be joined/combined while performing vlookup. (Col. B and F) highlighted in Yellow.

Unique number is Asset #

I attached the an example of files. First one is the Old, and second Import 2 - update is recent one with updated information.

[Qiu]

@nirkisna65 
I am sorry but I did not quite get what your desire.
But check this and it is just simple.

Spoiler

[nirkisna65]

Sorry, let me rephrase it. I want to combine the data of both the columns in to the updated export. For Eg: Spreadsheet, Import - Old, Col. B4 has "XYZ" where as 2nd spreadsheet Import 2 updated - report, Col. B4 has "Zee"... I want work flow result to give Col. B4 as "XYZ. Zee"  

same to be performed for Col. F.

I could open the workflow share earlier. 

Thanks for your help @Qiu 

[TomWelgemoed · Accepted solution]

Hi @nirkisna65 ,

I think you just need to join the data and then concatenate some of the columns?

Please see an image and an example (please add your files to the workflow) attached.Please note that there are some duplicates due to duplicates in the Excel itself. This can be handled of course. You may also need some formatting improvement on the formulas (e.g. only concatenate the values if they differ?), but wasn't sure if that's needed.

Formulas are both following this style: 

if !IsEmpty([US Response])

then [US Response]
+
if !IsEmpty([Right_US Response])
then '.'+[Right_US Response]
else ''
endif
else
[Right_US Response]
endif

Regards,

Tom

[nirkisna65]

Thank you! @TomWelgemoed 

[TomWelgemoed]

Happy that helped! 

Don't forget to watch out for duplicates in the 2 data sets - but hopefully this gets you on your way.

[nirkisna65]

I have a unique ID as asset number in both the data, do I have change the "Unique" tool for it? 

[TomWelgemoed · Accepted solution]

Hi @nirkisna65 ,

We're already joining on the Asset ID (think that's your only unique number?), but that doesn't appear to be Unique.So you have duplicates in the source data (rightly or wrongly). Try to see if there is not another field that can uniquely identify each row?

Regards,

Tom

[nirkisna65]

@TomWelgemoed @Qiu Thank you this is awesome. I have another query if you could help, I need to run the conditional formatting to the attached example sheet " Conditional formatting". 

I want Color GREEN Col A, B and C if the Col. E is Y, and color RED if Col E. is N and leave out the blank in Col. E as is.

Also, the font of the whole worksheet should be Arial 8 as standard

Could you please help, I tried the rendered tool was not successful to export. 

[TomWelgemoed]

Hi @nirkisna65,

Thanks for the question, I don't play with the report settings often so it forced me to do that a bit.

See the image below (I'm a bit busy, so only did it for column A), but you can easily apply the rule to each column where you want it. The key is to use the table tool and then select each field and click the "Column Rules" button on the right (Another image attached, together with the workflow). Then you simply select how you want the rule to go (like in Excel) and how you want to format the column. Also, there is a "Default Table Settings" in this tool, where you can set the font & size. After this you can render the output to Excel.

Hope that helps!

Best,

Tom