Find column in row in excel/datatable that has matching value

Help Activities
,
1

I have an excel spreadsheet that is in essence a cross reference. Row A has 20 or so unique values. I need to find the column in which a particular value is a match, so I can grab the values from the same in column but in other rows. So if I am looking of “monkey” and I find it in row 1 and column F, then I want to be able to grab balls from row 2 and three in column F.

This should be easy I believe, but I am really struggling with this? I want to use a For Each column activity, or a datable lookup on the first row.

Any assistance would be greatly appreciated.

2

This requirement occurs in most of the cases.
I have created a template xaml that takes Excel OR DataTable And the searchString as Input.
This workflow searches the searchString across the excel and once found, gives the found row and column as output.
I will attach the xaml here if you have not found any solution until I reach home.

3

That would be fantastic. I would very much appreciate it.

4

could you please share some screenshot of your excel input file

5

ExtractCellPosition.xaml (12.3 KB)

Hey @Bryan_Schmiedeler Please check the xaml Attached.
Inputs Needed:
in_ExcelPath - Full path for the Excel
in_ExcelSheet - Sheet Name in the Excel
OR
in_ExcelDT - You can provide Datatable if you already have instead of above 2 arguments.
(Provide Either of the one from above)

in_DataLookingFor - Cell value you want to search the position for in Excel

Output:
out_RowIndex - Index of Row
out_ColumnIndex - Index of Column
out_CellPosition - Exact Cell position of the Cell (You can extract the Column-Alphabet from this output)

This was curated for one of my design.
Please make the changes according to your workflow if neeeded.

1 Like
6

This works great. The key was the line to load the values in the array and search the array.

1 Like
7

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