excel - Give column number of first column with symbol -
so have sheet following:
row 1 2 3 4 5 6 1 x 2 x 3 x x 4 x 5 x x 6 x
and have sheet want encapsulate data with:
1st appeared 1 2 1 4 2 6
i'm trying construct second sheet. there way each row start @ column 2 , go ith column , display first 'x' appears. (note can assume every row have @ least 1 'x' when row , column meet forming diagonal down entire first sheet) [note first spreadsheet 5,000 x 5,000 hence why i'd nice formula instead of doing things hand =)]
thanks in advance!
the match function return relative position in either row or column. use formula in second worksheet.
=iferror(match("x", sheet1!1:1, 0) - 1, 0)
fill down retrieve correct column index or 0 if not found. 1 subtracted since hte want position relative column b. iferror function return default 0 no match found. substitute ""
in place of 0 if want nothing displayed.
Comments
Post a Comment