Help me create this column, with macro or something

Sutemi

New Member
Joined
Nov 18, 2005
Messages
6
So here's the what i'm trying to do...

Table A has the following information:

Column K shows the time in seconds like this:

50539
50539,1
50539,2
50539,3
...

Column L shows the same information, but with whole seconds like this:
50539
50540
50541
50542
...

Kolumn AS shows the additional info, which is calculated only to the column L. It goes like this:
0
0
0
1181
0
...

And the problem is this:
I should create a new column, where the information on the AS is keyed to same rows on K-column as they are on L-column. So if L column has 50539 on it and in the same row on AS has 0, then all the columns on K with 50539 to 50539,9 should have 0 on the same row with this newly created column. Let's call it the AT-column.

Did any of this make sense?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Sutemi

New Member
Joined
Nov 18, 2005
Messages
6
Okay :(

Let me show you.

It's now like this:

K L AS
50539,1 50539 0
50539,2 50540 45
50539,3 50541 1212121
50539,4 50542 0
50539,5 50543 7

Rows in AS are synchronised with column L

What i'd to like to do is take the data from AS-column, and put it in the column AT where data is in the fitted to the right rows with column K ("right" is defined by L-column):

Code:
K                  L                        AS                AT
50539,1        50539                 0                  0
50539,2        50540                 45                 0
50539,3        50541                 1212121            0
50539,4        50542                 0                  0 
50539,5        50543                 7                  0
50539,6        50544                 9                  0
50539,7        50545                 1                  0
50540,2        50546                 231                45
50540,4        50547                 2311               45
50540,8        50548                 11                 45
50541,3        50549                 2                  1212121
50541,9        50550                 7                  1212121
50542,1        50551                 123445             0


Does it make anymore sense now? Come on, it's hard to explain :cry:
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
Assuming that Column L is in ascending order, try...

AT1, copied down:

=LOOKUP(INT(K1),$L$1:$L$13,$AS$1:$AS$13)

Otherwise, try...

=INDEX($AS$1:$AS$13,MATCH(INT(K1),$L$1:$L$13,0))

Hope this helps!
 

Sutemi

New Member
Joined
Nov 18, 2005
Messages
6
Yes it is in ascending order, just as it is on examples I showed.

I will try that but even if it doesn't work I still thank you :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,086
Messages
5,570,136
Members
412,305
Latest member
Mozz
Top