Look up and copy data against one value which has values against multiple rows in excel

Feedback Studio
1

If you look at attached impact, sheet 1 has multiple links in a column and this links have multiple values against them in different columns.

In my use case, there might be few links which are exactly same in different rows but have unique values against them in those different rows. I want the links along with all values against them which show ‘Active’ against them to be sorted and displayed in another sheet.

Example, in the image, under sheet 1, there are 6 rows which show ‘Active’ under column Q.

Out of all these, row 2 and 9 have the same url links which I am showing in the Sheet 2 and the values showing against it are the ones which which are combination of values against it in sheet 1 in row 2 and 9

Same applies to row 5 and 13

There are also links which are in only one row example, row 7

Now sure how a formula can be written to get this data. Can any one help?

Test 7
Test 71917×422 100 KB

3

Hi,

Can you share input sample as a file?

Regards,

4

Sure, Attached the same. Also I know that I can use formula "
=TOROW(FILTER(B2:I13,(Q2:Q13=U2)*(P2:P13=“active”),“”),1,FALSE)
But unfortunately the Torow function is only available in Excel 365 which is not my version, so wanted to try using UiPath
Transpose test.xlsx (11.2 KB)

5

Hi,

Can you try the following sample?

image
image713×551 25.3 KB 

dt = dt.AsEnumerable.GroupBy(Function(r) r("Links").ToString).Select(Function(g) dt.Clone.LoadDataRow(({g.Key}.Concat(g.SelectMany(Function(r) r.ItemArray.Skip(1).Take(7)).Select(Function(o) o.ToString).Where(Function(s) not String.IsNullOrEmpty(s)).Take(7))).ToArray,False)).CopyToDataTable.DefaultView.ToTable(False,{"Groups","Text 1","Text 2","Text 3","Text 4","Text 5","Text 6","Text 7"})

Sample20230526-1aL.zip (8.7 KB)

Regards,

6

Not sure why but I get the attached error

error
error1034×405 40.9 KB

7

Remove the other data (expected output) as this is taking all data links column is duplicated

or try reading only the first data with defined range something like (A1:Q13)

Better is remove the expected output you have from U1

Regards

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.