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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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
20,222
Office Version
  1. 365
Platform
  1. Windows
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 :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,995
Messages
5,834,777
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top