Find solution
What is an op-ed column?

Conditional Excel formula to identify right-most column with a value?

  • I'm grappling with an Excel formula. Given a bunch of columns, I'd like to to (a) identify the right-most column that contains a value, then (b) fill a cell with that value and the value in the column heading. I have a spreadsheet with columns (each one is a project milestone). As the project progresses, a date is entered in the next column. For example: A          B          C          D          E 1 Jan 1 Feb 1 Mar 1 Jan 1 Feb 1 Mar 1 Apr 1 May The first project has completed milestones A, B, and C; the second has completed milestones A, B, C, D, and E. I need a formula that looks across the row, identifies the most recent milestone, and then fills a cell with the name of the milestone (eg A, B, or C), and the date that milestone was reached. (If it matters, in real life the dates use a dd-mm-yyyy format, and the column heading is a text value, like 'Launch Approval'.) For example, for the first project, the value would be "C;1 Mar" (the most recent milestone was C, and that happened on 1 March). I've done this before (it's a UNIX system! I know this!), but I can't for the life of me remember how. I'm pretty sure I worked from right to left, and used a bunch of nested IF and ISBLANK statements: if column E is blank, then check column D; if that's blank, check column C; and so on - once it found a non-blank cell, insert the heading for that column plus the date as a text value. For whatever reason, I can't get this working this time. I don't need any fancy error checking (eg, 'well, there's a value in the column, but not the one to the left of it' or 'but the date in this column is earlier than the one to the left!'). Just the right-most date and the appropriate column heading. I'd settle for each being in a separate cell (eg C in one cell, the date in the next) if that's any easier. My Google Fu has proven weak. Save me, Excel wizards.

  • Answer:

    If you never have a gap between columns, you can use a COUNTIF statement on each row to see how many cells are filled in, and then use an INDEX statement on each row that uses the result. =INDEX(A1:E1,COUNTIF(A1:E1,"""")) I could be getting the quote marks wrong.>

obiwanwasabi at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

Assuming that the titles are in row 1, this should do the trick. Enter this in cell F2 and ctrl+d down the rows. if(isblank(E2), if(isblank(D2), if(isblank(C2), if(isblank(B2), concatenate(A$1,";",A2), concatenate(B$1,";",B2)), concatenate(C$1,";",C2)), concatenate(D$1,";",D2)), concatenate(E$1,";",E2))

RandallStanhope

If it's in dd/mm/yyyy format, could you find the relevant date with "=Max"?

Mrs. Pterodactyl

Oh, sorry, if you want the milestone as well you would need to do some concatenation. I would recommend: In column F use the COUNTIF statement to figure out the appropriate column for that row (starting with the first row of data, not the header row) In column G concatenate (using &) two INDEX statements, one referring to the header row and one referring to the data row, using the value in column F.

leopard

(COUNTA() counts non-empty cells, so it will go wonky if you've got gaps - but I just hate the quotes you need for COUNTIF)

pompomtom

I also can't read.. to include the date, maybe: =OFFSET(A2,-1*ROW(A2)+1,COUNTA(2:2)-1)&" "&TEXT(MAX(2:2),"yyyy-mm-dd")

pompomtom

Here's a kludgey approach that uses many cells but is a) simple b) fast to implement and c) you can visually see it working. And d) is easy to extend. It's probably not even that much bigger on disk. Let's start with the example you provided: row 1: A B C D Erow 2: Jan1 Feb1 Mar1row 3: Jan1 Feb1 Mar1 Apr1 May1Way out on the right, somewhere around column AA, enter the formula: Cell AA2: =if(isblank(a2),z2,a2) And then copy that formula into AB2 through AE2 Further out (it doesn't matter where, just using the BA column for convenience): Cell BA2: =if(isblank(a2),az2,a$1) And copy BA-2's formula into BB2 through BE2. Let's make column G your "stage complete" column, and column H the completion date. Cell G2 = ae2 Cell H2 = be2 And we're done. You can now copy the entire row 2 formulas down for as many rows as you need. If you want to show the stage and the date in the same cell, I'm sure there's some simple "contatenate ae2 + " ; " + be2" syntax that's easy to figure out. Personally I don't spend time working out how to express everything in a single cell if I don't have to.

ceribus peribus

As long as no milestones occur on the same date for any one project, you can use the max function to find the most recent date and then feed that into the match and index functions to get the project milestone from the first row. Here’s an example of such a formula (You can put this formula in the same row as your data, such as cell G2 and then copy it down): = INDEX($A$1:$E$1,1,MATCH(MAX(A2:E2),A2:E2,0)) & ";" & TEXT(MAX(A2:E2),"dd-mm-yyyy") The first part INDEX($A$1:$E$1,1,MATCH(MAX(A2:E2),A2:E2,0)) is looking at the range $A$1:$E$1 and finding the column to grab by finding the position of the most recent date in cells A2:E2. The second part of the formula TEXT(MAX(A2:E2),"dd-mm-yyyy") is finding the most recent date and formatting it as a date.

Jasper Friendly Bear

    A     B                C               D                 E 1         Find Thing Buy Thing Ship Thing Blah Thing 2 Date 1/1/14      1/2/14       1/3/14        1/4/14 =CONCATENATE(INDEX(B1:E1,MATCH(MAX(B2:E2),B2:E2))," - ",TEXT(MAX(B2:E2),"DD-MM-YYYY")) Yields: Blah Thing - 04-01-2014

casual observer

I'm reminded of the time I proudly showed a mate a linear dungeon explorer I'd written on the C64 in BASIC using GOTO statements, only for him to create it in in a quarter of the space and make it freely navigable using an array. Thanks all!

obiwanwasabi

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.