I am trying to pull the first non-blank 'location' associated with each id and place that in the "initial location" column. Right now, I just have ID and Location, I am trying to create the Initial Location column below. Can I do this with an Index Match of some kind?
Here is a simplified version of the spreadsheet that I am working with:
I would like to pull the "Location" with reference to the ID and put as "Initial Location". As you can see, the ID numbers are on there multiple times, and sometimes the first corresponding variable cell in Location is blank. What type of formula can be created that will reference ID 1 (for example) and pull the first non-blank Location attached to ID 1 (which would be "20" in this case) and put that in the "Initial Location" column? I tried using an Index function but it is not working for me. Here is what I attempted to use in the initial location column:
=IFERROR(INDEX(B:B,MIN(IF(($C1:C39)*(B1:B39<>""),ROW(A1:A39)))),"No Match")
I appreciate any help.
=IF(AND(B2<>"",SUMIFS($C$1:C1,$A$1:A1,A2)=0),B2,"")
?