Searching through excel worksheet for a number

[bsheremeta]

Hey, can someone please assist with searching through the excel worksheet for a particular number? 

I input the worksheet and not sure which tool I can use

[mceleavey]

Hi @bsheremeta ,

Can you give us an example of what you're trying to do because this is not enough information.

For example, provide a mock sheet and let us know what you're trying to do.

M.

[Luke_C]

Hi @bsheremeta 

Tough to say without seeing some sample data. If you know the specific column you're searching you can use a filter tool to look for specific values. If it could be any column perhaps you can transpose the data and filter the resulting value column.

[bsheremeta]

I have a worksheet with probably 300 columns. I need to search for one particular number - let's say 27,740. I need to search through all the information and ideally obtain a reference (like B6 in excel) to that cell. 

[atcodedog05]

Hi @bsheremeta 

I dont think we can get a reference cell in Alteryx as easy we can get in excel.

[bsheremeta]

Here is what I'm trying to do and maybe there is an easier way out with some of the available tools as I'm just a beginner. I need to use search feature within the worksheet to find a number and respective reference. Then I need to use the second worksheet, use that exact reference and pull a number out of it. Do you know what tools I might use for something like this?

[danilang]

Hi @bsheremeta 

Here's how you can do it

Read in both the search and the target workbooks. Add row ids, transpose and then add column ids.  Join the search value to the search workbook on [Value] to find the cell reference(row id and column id).  Then join the output of that to the target workbook on [Row id] and [Column id] to get the value at that cell.

The two sample workbooks have the same value with the target being the negative of the source to make it easy to see if the proper match was made.

BTW @atcodedog05 you can build up the cell reference by using mod 26 math on the column id to build up the column name as letters and appending the row id

Dan

[TimN]

Hi,

The lazy way is to use Alteryx search.

Enter the value into the search box, search and then hit the green check mark.  This will create a formula and add the formula tool to you work flow.  Formula looks like this and searches every column.  It's not a dynamic solution though.  If you add more columns to the Excel file then you'll need to regenerate the full formula.

Regards,

Tim