|
Lookup first value in row of cells
Hi
I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
=MIN(IF(A2:X2<"",COLUMN(A2:X2)))
This is an array formula so commit it with CTRL+****F+ENTER not just ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Ops, I had not finished !
=INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Hi Benard
Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
The formula does not look for the smallest value but the smallest (ie the
first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Hi Bernard
It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Since you are now starting in column E, we need to subtract 4 from the
column number =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))-4) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Some non-array formulas:
Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Much better than mine - need more coffee!
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "RagDyeR" wrote in message ... Some non-array formulas: Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Great thanks RagDyeR and Bernard.
one last thing if there is no data in the row I need a blank cell or a zero David "RagDyeR" wrote: Some non-array formulas: Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Try this:
=IF(COUNTA(E16:X16)=0,INDEX(E16:X16,MATCH(TRUE,IND EX(E16:X16<0,),)),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "daveprospects180" wrote in message ... Great thanks RagDyeR and Bernard. one last thing if there is no data in the row I need a blank cell or a zero David "RagDyeR" wrote: Some non-array formulas: Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
TYPO !
Try this instead: =IF(COUNTA(E16:X16)<0,INDEX(E16:X16,MATCH(TRUE,IN DEX(E16:X16<0,),)),"") -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... Try this: =IF(COUNTA(E16:X16)=0,INDEX(E16:X16,MATCH(TRUE,IND EX(E16:X16<0,),)),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "daveprospects180" wrote in message ... Great thanks RagDyeR and Bernard. one last thing if there is no data in the row I need a blank cell or a zero David "RagDyeR" wrote: Some non-array formulas: Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Your input much appreciated though, thanks
David "Bernard Liengme" wrote: Much better than mine - need more coffee! -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "RagDyeR" wrote in message ... Some non-array formulas: Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
Don't worry about last comment, have now got solution.
Many thanks for your input RagDyeR, yours wins the day. David "daveprospects180" wrote: Great thanks RagDyeR and Bernard. one last thing if there is no data in the row I need a blank cell or a zero David "RagDyeR" wrote: Some non-array formulas: Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
Lookup first value in row of cells
You're welcome, and appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "daveprospects180" wrote in message ... Don't worry about last comment, have now got solution. Many thanks for your input RagDyeR, yours wins the day. David "daveprospects180" wrote: Great thanks RagDyeR and Bernard. one last thing if there is no data in the row I need a blank cell or a zero David "RagDyeR" wrote: Some non-array formulas: Will display the first text *or* numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<0,),)) Will display the first numeric value in the range: =INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16), ),)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "daveprospects180" wrote in message ... Hi Bernard It works great but only if there is no data or columns to the left of the row. My sheet has some columns to the left of the data I want to refer to and it does not work if it is there. I am using =INDEX(E16:X16,,MIN(IF(E16:X16<"",COLUMN(E16:X16) ))) Columns A-D have text, text, number, text in their cells David "Bernard Liengme" wrote: The formula does not look for the smallest value but the smallest (ie the first) column number with a value in it, then INDEX gets the value from that column Give it a try and let me know what you think, or send me private email and I will send you a sample file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi Benard Thanks, however the first value may be not be the smallest of the values A2:X2. eg values could be 9,10,12, 2 in row or 2, 6, 7 need the 9 and 2 to appear in Z2 Thanks David "Bernard Liengme" wrote: Ops, I had not finished ! =INDEX(A2:X2,,MIN(IF(A2:X2<"",COLUMN(A2:X2)))) this is also an array formula best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "daveprospects180" wrote in message ... Hi I have a row of data (A2:X2) which contain some blank and some non-blank cells, I want to put the first (left to right) value in a new cell on same row Z. The value of cells are numerical to indicate a month (1 for Jan, 12 for Dec etc). Any help would be greatly appreciated. Thanks David |
| All times are GMT +1. The time now is 12:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com