0

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:

example table

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.

3
  • would a simple IF work: =IF(AND(B2<>"",SUMIFS($C$1:C1,$A$1:A1,A2)=0),B2,"")? Sep 11, 2019 at 21:36
  • @ScottCraner Why don't you post that as an answer? It explicitly says in the comment placeholder text "Avoid answering questions in comments."
    – teylyn
    Sep 11, 2019 at 23:03
  • @teylyn on this one I am not sure that would solve the problem being asked. It is the reason it is a question. Though it satisfies the picture, I have reservations as to if it would solve the larger problem. I was hoping for feed back. Sep 12, 2019 at 1:28

2 Answers 2

1

I would like to suggest two possible methods:

Method 1:

enter image description here

  • Formula in Cell C81:

    =IF(AND(COUNTIFS($A$81:A81,A81,$B$81:B81,"<>0")=1),INDEX(B81:B88,MATCH(TRUE,INDEX((B81:B88<>0),0),0)),"")
    

Fill the formula down.

Method 2 (Summary Style):

enter image description here

  • Put IDs (works as Criteria) in range D81:D83.
  • Array (CSE) formula in Cell E81, finish with Ctrl+Shift+Enter and fill down.

    =IFERROR(INDEX(B$81:B$88,MATCH(1,($D81 = $A$81:$A$88)*(B$81:B$88<>""),0)),"")
    

Adjust cell references in the formula as needed.

0

Try this formula:

=IF(AND(COUNTIFS($A$2:A2,A2,$B$2:B2,">0")=1,(B2<>"")),B2,"")

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .