Vlookup and transpose the attributes as headers using UI Path

Hi Ui Path Forum Community,

I have the following excel Sheet …

idx |Group | Family | Quantity
1 |Apple | Fuji | 100 2 |Apple | Granny Smith| 20
3 |Apple | Gala |100
4 |Apple | Honeycrisp |88
5 |Apple | Brecheche | 60
6 |Apple | McIntosh |70
7 |Apple | Gala | 44

and another work book which consists of auditing attributes for each fruits category

Group audit Audit variations

Orange | Size
| Color
| Number_Of_Items

Apple | Quality
| Variations
| Family
| Defect

Pineapple | Size
| Special_Feature
| Family

Watermelon | Freshness
| Heaviness
| Family
| Weight

And I want to the following result into another excel sheet

idx |Group |Family | Quantity |Quality | variations| Family | Defect
1 |Apple | Fuji | 100
2 |Apple | Granny Smith |20
3 |Apple | Gala | 100
4 |Apple | Honeycrisp | 88
5 |Apple | Brecheche | 60
6 |Apple | McIntosh | 70 7 Apple Gala 44

Right now, I am manually doing a lookup on the group category of the first data frame with the "group Audit " column on the second. Afterward doing a transpose on the audit variation based on the Group Type. Since I am new to UI Path Automation, is there any way to automate this process? This would save a lot of time. Suggestions are welcomed.

Welcome to the UiPath Community @Parvathy_Menon!

I can’t see any Color column n the original excel data you mentioned above. How would you get it?

Solution can be a macro too as your steps are static.

However, there’s an marketplace activity to do transpose operation. First, see if this helps

Hi Rahul ,

Thank you for responding .
I didn’t get your question regarding the color attributes.

I wanted to do a lookup on the group attributes from table 1 with Audit group in table 2 and afterward transpose the attributes corresponding to the Audit Group (Orange , Apple etc) as the headers.

Suggestions are welcome on how to proceed . :slight_smile:

Can yous share some sample input data (e.g. as Excel) and the corrsponding expected output with us? base on this we will workout a solution suggestion. Thanks for support

Desired output need to be

For example:

If the sheet contains only apple Group then ,audit attributes corresponding to Apple should be coming up in output data.

Once again thank you for your response.

I am not able to upload excel files . Pls Consider this as in different sheet

This will be my auditing file and input file .Suggestions are welcome on how to proceed .

Your output seems pretty same as input excel file… can you explain littlie deep after applying vloop how output should be(like second excel date where it is adding in first excel).

Can you confirm following requirment understanding:

  • we do have input: with columns idx, Group, Familiy, Quantitity, Audit
  • based on the Group we have to add the relevant columns taken from sample sheet2


Yes @ppr . Thats the requirement. Need to add the audit attributes as headers in the final output.

a quick less complex approach could be:

  • filter sheet2 on the group values - filter DataTable Activity | output: dtFiltered
  • add the rows by itereating over dtFiltered with a for each row and using the add datacolumn activity

For sure we can setup other options loke LINQ / Dict Dynamics in case it is needed

1 Like

Hi @kalyanDev ,
I have to add the corresponding audit attributes(Size, defect ,etc ) as headers in the final output.

okay… if multiple groups exist then also you need to add?

yes, depends on the Group mentioned in the input sheet , we have to include the audit attributes as column headers.

Thank you so much , I will definitely try it .

1.)First get the unique groups dt using below syntax
udt=yourDT.DefaultView.ToTable(true, “ColumnName”).copytodatatable
2.)loop udt and inside take filter data table activity to filter with audit dt and then you will get that particular group audit columns dt(auditdt)
3.)loop auditdt and inside use add data column to the original input dt.

1 Like