Identify first non-null value in a sheet

[gwiz]

Hi community,

I have multiple excel files that I am reading in. Per each excel file the first non-null field is the document title (what I want) but it is not always in A1, hence it is the first non-null field. All i would like to do is keep this document title and I don't need anything else. As previously alluded to it is not always in A1, it could be in B2 or F3, etc. so I am hoping for alteryx to be able to identify the first non-null field. Is this possible?

Thanks,

[cmcclellan]

Use a Filter tool and then Sample the first record and you'll have it :) 

[afv2688]

Something like this may work,

cheers

[danilang · Accepted solution]

Hi @gwiz 

The general way to solve a problem like this to transpose all the values into a single pair of columns and then find the 1st one.  Your main missing component is that you need to define what you mean by "1st".  Do you search in column order, row order or by applying a formula to determine the distance of every cell from cell A1.  In the following table, which is the first non-null value? 

	A	B	C	D
1			Value1C	asd
2		Value2B	asd	asd
3	Value3A	asd	asd	asd
4	asd	asd	asd	asd

Is it Value3A(1st in Column A), or Value1C(1st in row 1)?  Or maybe it's Value2B since that the's closest(depending on how you're measuring distance) to Cell 1A.

Once you define this, you'll be able to transpose all the cells in the sheet and sort them to find the 1st non-null.  

BTW:  @afv2688, your workflow is missing the input file.

Dan

[gwiz]

Yes a transpose worked! Thank you all for the advice