Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How to make a function which would return the first non-blank cell value but if it's defined in the left cells next to it, it would jump 2 cells and look for the next value.

Table:  
1 | 2 | 3 | 4 | 5 | 6 | 7 || 29 | 30 | 31 | 32...
a | b |   |   | c | d |   || a  | c  |    |
  |   |   | a | b |   |   || a  |    |    |
a | b | c | d | e | f | g || a  | c  | e  | g

from DL3:EW4873 is the values, always in pairs, but I need only the first value-piece and only if it's not already a value in the EX to EK range.

Also the EX:FK cells are the place where formulas will go.

share|improve this question
1  
Do you want to do this for each row? You can find first text value in row 3 with this formula =INDEX(AO3:EU3,MATCH("*",AO3:EU3,0)) but that doesn't address the second part, do you mean you want to find the first value that appears that isn't in EK3:EX3? –  barry houdini Feb 21 '12 at 11:13
    
Yes for each row and that doesn't appear in EK3:EX3. –  Tusk Feb 21 '12 at 11:21

1 Answer 1

up vote 5 down vote accepted

OK don't those ranges overlap? In any case try this version, adjust ranges as required

=INDEX(AO3:EU3,MATCH(1,INDEX(ISNA(MATCH(AO3:EU3,EK3:EX3,0))*(MOD(COLUMN(AO3:EU3)-COLUMN(D3),2)=0)*(AO3:EU3<>""),0),0))

That looks for the first non-blank value in alternate cells, AO3, AQ3, AS3 etc. that doesn't match any value found in EK3:EX3

Revised suggestion:

OK based on your comments, I'm assuming that you still want non-blank data from alternate cells so with data in AO3:EI3 and with EJ3 blank try this formula in EK3 copied across to get the next different value

=IFERROR(INDEX($AO3:$EH3,MATCH(1,INDEX((COUNTIF($EJ3:EJ3,$AO3:$EH3)=0)*(MOD(COLUMN($AO3:$EH3)-COLUMN($AO3),2)=0)*($AO3:$EH3<>""),0),0)),"")

when values run out you get blanks.....

Revised suggestion 2:

Try this formula in EX3 only

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)),"")

and then this one in EY3 copied across

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((COUNTIF($EX3:EX3,$DL3:$EW3)=0)*(MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)),"")

Revised version for "second pair" - this should just pick up the associated values from the first formula

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)+1)&"","")

and this one in EY3 copied across

=IFERROR(INDEX($DL3:$EW3,MATCH(1,INDEX((COUNTIF($EX3:EX3,$DL3:$EW3)=0)*(MOD(COLUMN($DL3:$EW3)-COLUMN($DL3),2)=0)*($DL3:$EW3<>""),0),0)+1)&"","")

change the $EX3:EX3 part depending on location of formulas.....

share|improve this answer
1  
OK, I must be missing something, where are you putting the formula? I assumed it would only go once in each row, and not in any cells that are referenced by the formula –  barry houdini Feb 21 '12 at 12:22
1  
@Tusk OK, I understand, I added a revised formula above –  barry houdini Feb 21 '12 at 15:12
1  
OK, I was hoping you would have a blank cell between the data range and your formulas, if not then I think you need a slightly different formula for the first cell, see Revised suggestion 2 above –  barry houdini Feb 22 '12 at 9:37
1  
Just change the range so that it starts with the second pair, i.e. change instances of $DL3:$EW3 to $DM3:$EW3....and where there is $DL3 on it's own that should become $DM3..... –  barry houdini Feb 23 '12 at 0:01
1  
Works OK for me :).....I realise you can't have both lots of formulas in the same cells but assuming you want the "second pair" version in EX3 across see revised answer above....... –  barry houdini Feb 23 '12 at 13:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.