How to find the first and last non blank cells in a row, then pull the column names (month/year) to get first seen and last seen

[jholly1984]

Hi everyone.

I am a beginner with excel and could use some formula help if possible.

I am working on a document that tracks website trafffic by month for 5 years (each month is a column).

Some sites have no traffic for some of the months.

What i need to do is have the ability to pull in the FIRST and LAST non blank cell in a given row... then pull the column name (month/year) as the value.

The goal is to be able to say this site was first seen on {Month/Year) and Last Seen (Month/Year).

In some cases its a straight line... a site starts in a given month and dies at a specific time later.

In other cases a site is smaller and could have blanks in the middle of the data set... not just the beginning and end.

So i need a formula that only pulls the first month where a traffic number exists and the last month where traffic exists.

I was able to find one formula that seems to work that pulls in the actual traffic value for the first non blank cell in a row... but I actually need the column name as the output.. not the cell value itself.

=INDEX($B$2:$BE$2,SMALL(IF($B$2:$BE$2<>"",COLUMN($B$2:$BE$2)-COLUMN($B$2)+1),1))

I have been unable to find a similar formula for finding the last non-blank cell in a row.

Any help would be greatly appreciated. Thank you SO MUCH for your time.

 

[Peter_SSs]

each month is a column

I'm assuming those column headings are actual dates (but perhaps formatted to show month/year only)?

You would have to adjust the ranges for 5 years but see if this could help.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 01 14.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		First	Last		1/01/2022	1/02/2022	1/03/2022	1/04/2022	1/05/2022	1/06/2022	1/07/2022	1/08/2022	1/09/2022	1/10/2022	1/11/2022	1/12/2022
2	Site 1	03/2022	06/2022				x	x	x	x
3	Site 2
4	Site 3	08/2022	08/2022									x
5	Site 4	02/2022	10/2022			x	x			x	x			x
First Last

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(AGGREGATE(15,6,E$1:P$1/(E2:P2<>""),1),"")
C2:C5	C2	=IF(B2="","",AGGREGATE(14,6,E$1:P$1/(E2:P2<>""),1))