Data Aggregation formulation

Excel Data.JPG

I am compiling data from school transcripts to import into a new database.

I need to assign a number that represents a term from a specific year to each grade. That way when I import it into my database it get assigned to the right term.

I have already used a Vlookup table to correlate the years, into a base number, which is represented in the R column.

term 1 of this year would need to have the value 2101
term 2 of this year needs to have the value 2102
term 3 of this year needs to have the value 2103
etc.

so, What I want to do is take the horizontal row, lets say row 2, and I want to say;

IF there is a value in Column L, output = 1
IF there is a value in Column M, output = 2
IF there is a value in Column N, output = 3
IF there is a value in Column O, output = 4
IF there is a value in Column P, output = 5
IF there is a value in Column Q, output = 6

If I can make these compile into a column, then I can sum the base identifier for the year, with the value for the term, and I will have the number I need to correlate the grades in my database.

I am sure there is a more graceful way to accomplish this, but I am trying to work through this so I "learn to fish" so to speak.

Thanks
Dave

Hi

Try this

S2: =MAX(IF(LEN(L2:Q2)>0,COLUMN(L2:Q2)-11))

This formula is array entered (ctrl, shift, enter).

Copy down as required.

rylo

rylo, thanks for the help!

it took me a bit to figure out what you meant by 'array entered' but once I did it worked perfectly.

Thanks a ton!