Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

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