I am trying to find the cell address of the first row that contains the letters "H" "A" "B" "C" "T" separately.
at the moment I have formulas to find the last cell in the column that contains each letter respectively, they look like this:
="B"&SUMPRODUCT(MAX(--(ISNUMBER(SEARCH("H",$B$9:$B$500)))*ROW($B$9:$B$500)))
="B"&SUMPRODUCT(MAX(--(ISNUMBER(SEARCH("A",$B$9:$B$500)))*ROW($B$9:$B$500)))
="B"&SUMPRODUCT(MAX(--(ISNUMBER(SEARCH("B",$B$9:$B$500)))*ROW($B$9:$B$500)))
="B"&SUMPRODUCT(MAX(--(ISNUMBER(SEARCH("C",$B$9:$B$500)))*ROW($B$9:$B$500)))
="B"&SUMPRODUCT(MAX(--(ISNUMBER(SEARCH("T",$B$9:$B$500)))*ROW($B$9:$B$500)))
the data that I am searching through is always in column B starting at B9 and finishing at B500 these formulas return the very last cell that contains the respective letter in range B9:B500
I have tried using min instead of max but that returns "0"
the data that I am searching through looks like this: A2558,H4392,B1225,C724,B1227,H4393,A2559,C725,T332 and so on.
every time I search for something to help me with this on this site or on google it doesn't provide an answer that works for contains "H" they are all set up for 'find string in range B9:B500' and so wouldn't work in my use case.
all of the data in column B has a second set of data in column A that is linked to it and that data is a date. So the reason I need to find the first cell that contains these letters is when I update the sheet at the minute if a date has changed it doesn't then change the date it only adds in new entry's from the last cell (hence why my formulas are currently set up to find the last cell that contains) I am looking to find the first cell in the sheet and then copy and paste this data into another sheet and then update the dates and paste it back in. all of this I will do using vba but I need a start point to copy paste from in the original sheet hence the need for the formula.