February 25, 2014 7:25 PM Subscribe

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.
posted by obiwanwasabi to Computers & Internet (10 answers total) 1 user marked this as a favorite

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.

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

posted by Mrs. Pterodactyl at 7:39 PM on February 25

posted by Mrs. Pterodactyl at 7:39 PM on February 25

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.

posted by leopard at 7:41 PM on February 25 [1 favorite]

=INDEX(A1:E1,COUNTIF(A1:E1,"<>"""))

I could be getting the quote marks wrong.

posted by leopard at 7:41 PM on February 25 [1 favorite]

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.

posted by leopard at 7:48 PM on February 25

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.

posted by leopard at 7:48 PM on February 25

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

posted by pompomtom at 8:00 PM on February 25

posted by pompomtom at 8:00 PM on February 25

I also can't read.. to include the date, maybe:

posted by pompomtom at 8:03 PM on February 25

=OFFSET(A2,-1*ROW(A2)+1,COUNTA(2:2)-1)&" "&TEXT(MAX(2:2),"yyyy-mm-dd")

posted by pompomtom at 8:03 PM on February 25

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:

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.

posted by ceribus peribus at 8:16 PM on February 25

Let's start with the example you provided:

row 1: A B C D E row 2: Jan1 Feb1 Mar1 row 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.

posted by ceribus peribus at 8:16 PM on February 25

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.

posted by Jasper Friendly Bear at 8:55 PM on February 25

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.

posted by Jasper Friendly Bear at 8:55 PM on February 25

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

posted by casual observer at 9:01 PM on February 25

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

posted by casual observer at 9:01 PM on February 25

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!

posted by obiwanwasabi at 3:42 PM on February 26

posted by obiwanwasabi at 3:42 PM on February 26

You are not logged in, either login or create an account to post comments

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

posted by RandallStanhope at 7:38 PM on February 25