Help me create this column, with macro or something

Sutemi

New Member
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
no

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

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!

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

Replies
2
Views
200
Replies
3
Views
398
Replies
11
Views
472
Replies
5
Views
407
Replies
8
Views
361

1,217,440
Messages
6,136,628
Members
450,022
Latest member
Joel1122331

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.

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

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