0

I have an Excel sheet with over 100 dates as column names and hundreds of locations as row names.

There are numbers (could be any number) somewhere in each row that indicate that an event will occur in that location on that date.

I am trying to add a column that locates the first occurance of any number in each row so that it tells me the first date (column) that an event will occur in each building.

I cant provide a file but I can create a set up example if needed. Thanks!

2
  • What version of Excel are you using? Jun 11, 2020 at 21:40
  • I am using Excel 2016 Jun 15, 2020 at 16:54

3 Answers 3

0

For say row #2:

=MATCH(TRUE,INDEX(ISNUMBER(2:2),0),0)

will display the column number of the first column that contains a number.

(may need to be array-entered)

enter image description here

In the example above, the formula returns 4 for column D.

Edit#1:

In some versions of Excel, the formula must be array-entered. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

3
  • So this isnt working for me. Not sure what is the issue. This is returning the first exact number listed in each row which is close to what I want, but I do not need the number itself I need the column it appears in. Your question appears to answer this but it is not giving me the correct result. Jun 15, 2020 at 16:56
  • @AmericanAnalyst Please see my EDIT#1 Jun 15, 2020 at 17:44
  • @AmericanAnalyst,,, better share screen shot, will help us to use the formula to justify it ! Jun 15, 2020 at 17:48
0

You may use this formula:

enter image description here

  • Formula in cell F27:

    =IFERROR(CELL("col",INDEX(A27:D27,MATCH(TRUE,INDEX(ISNUMBER(A27:D27),0),0))),"")
    

N.B.

  • Excel considers DATE as sequential serial numbers.
  • You may adjust cell references in the formula as needed.
0

The following works:

=INDEX(A1:CX1,1,MIN(IF(ISNUMBER(C2:CX2)*COLUMN(C2:CX2)=0,"",ISNUMBER(C2:CX2)*COLUMN(C2:CX2))))

It figures the result going into column A and the data columns beginning in column C (the buildings/sites being in column B). Naturally, it can be placed anywhere, especially column B. Just figured you'd want it somewhere close to each row's building label. Just keeping it simple in the answer.

The first range, A1:CX1 starts in column A so that the formula can directly use the results of COLUMN(C2:CX2) rather than having to add some value to it to adjust for the fact that no matter what, the date headers for the columns do not begin in column A due to the building/site row labels. But if one prefers...

All parts of the formula are good for version 2007 and forward.

You must log in to answer this question.

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