How to return first non-blank cell and if it's already a value before it, return the third non-blank and so on?

2

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.

Tusk

Posted 2012-02-21T11:02:15.737

Reputation: 123

1Do 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 – 2012-02-21T11:13:52.900

Yes for each row and that doesn't appear in EK3:EX3. – Tusk – 2012-02-21T11:21:20.050

Answers

5

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

barry houdini

Posted 2012-02-21T11:02:15.737

Reputation: 10 434

it returns 0 in every cell from EK3:EX3 range. Also shows an error message that it can't calculate it: "Excel cannot calculate a formula" – Tusk – 2012-02-21T12:07:26.857

1OK, 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 – 2012-02-21T12:22:00.053

It would go in every cell/row from EK3 to EX3 so it would get first, second, third cell values but never repeat the ones that's been resulted before it from EK3 through EX3. – Tusk – 2012-02-21T12:42:26.470

1@Tusk OK, I understand, I added a revised formula above – barry houdini – 2012-02-21T15:12:18.810

It's close, had to rearrange the tables (hidden fields and it was giving too much trouble for the formula.) So now it's a cleaner table now.

=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)),"") But this gives circular reference error, and although before the rearranging it was counting it, it repeated the first value it found all the 14 times :( For new path check the revision. – Tusk – 2012-02-22T09:08:54.360

1OK, 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 – 2012-02-22T09:37:13.780

It works like charm. You're a genius, with wings and a halo :) – Tusk – 2012-02-22T09:46:18.230

I thought it would be easy, but I can't find the way how to select the second value-pair, the same way like this, so thought if I +1 somewhere it would work but it didn't is there an easy way? Also it can be a repeatable value anywhere – Tusk – 2012-02-22T23:57:53.850

1Just 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 – 2012-02-23T00:01:57.193

that's works fine for the "ex3" part, but the "ey3" filters out repeating values, and copying the "ex3" to all cells makes it repeat the first value every time. – Tusk – 2012-02-23T09:16:48.167

1Works 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 – 2012-02-23T13:09:23.673

The revision for "second part" still doesn't let it has repeatable value. :( – Tusk – 2012-02-23T13:32:43.307

1Are you using exactly the formula I suggested? where are you putting it and how have you changed it (if at all)? Are you expecting not to repeat values from the "first pair"? That isn't how it's set up at the moment (although it could be), the two are separately self-contained (no repeats within each set) – barry houdini – 2012-02-23T14:41:35.937

I'm putting these in the place of the one before this, so EX3 through FK3. Using exactly your formula, and grabbing the left-bottom-dot to copy formula till FK3 and then grabbing it downwards. First pair should not repeat that's correct, but the second pair has three states, and each state can be the same state for every first-value. So the second can and should be able to repeat, but return blank if it's already blank. I hope it's understandable now :) – Tusk – 2012-02-23T15:00:54.180

1OK, I think I understood the requirement to be different - should this be selecting the second part of each pair that the previous formula returned, e.g. if the first formula returned vaklues from DN3 and DX3 this one should get the "complementary value" i.e. from DO3 and DY3? Are these all text values (or blanks)? – barry houdini – 2012-02-23T15:27:57.517

All text values if it has primary pair, and yes if first value is from DN3 then return DO3 :) If there's no primary value secondary value is blank of course – Tusk – 2012-02-23T16:22:42.663

1OK, I changed the last entry again, essentially the same formulas as originally, with a +1 in the MATCH to get the value from the next cell....and added &"" to make sure blanks don't show as zero – barry houdini – 2012-02-23T18:37:59.620

Still shows the last value instead of blanks :( – Tusk – 2012-02-25T07:54:36.277

Maybe I wasn't clear enough, three states are: easy, normal, hard. It can be easy again in the same line, but if it's blank originally, it shouldn't be easy, it should be blank. And now the last update, keeps repeating the the last real entry, easy through the whole line without making it blank. – Tusk – 2012-02-26T10:30:27.380

It keeps on repeating the first found value throughout the row, not the last. My mistake :) – Tusk – 2012-02-27T08:47:55.693

Asked: 2012-02-21T11:02:15.737

Viewed: 3 933 times

Active: 2016-03-01T08:56:35.677