Hi everyone,

"C"
"J" "K"
14/10/2008 01/10/2008 1
01/10/2008 06/10/2008 2
14/10/2008 07/10/2008 3
07/10/2008 13/10/2008 4
07/10/2008 14/10/2008 5
06/10/2008 18/10/2008 6
01/10/2008
13/10/2008
18/10/2008



Column "B" is a list of dates in no particular order.
Column "J" is a filtered list of those dates
Column "K" is a list of values assigned to the different dates.
How do I write the code to assign those values in column "K" to the
dates in column "C"
(they would be returned to the adjacent column "D")

thank you for your help, your time & your and expertise.

On 18 Oct, 11:45, ChipButtyMan <colingra...@btinternet.com> wrote:
> Hi everyone,
>
> * * *
"C" J" * *
* * *"K"
> 14/10/2008 * * * * * * * * * * * * * * * * * * *01/10/2008 * * *1
> 01/10/2008 * * * * * * * * * * * * * * * * * * *06/10/2008 * * *2
> 14/10/2008 * * * * * * * * * * * * * * * * * * *07/10/2008 * * *3
> 07/10/2008 * * * * * * * * * * * * * * * * * * *13/10/2008 * * *4
> 07/10/2008 * * * * * * * * * * * * * * * * * * *14/10/2008 * * *5
> 06/10/2008 * * * * * * * * * * * * * * * * * * *18/10/2008 * * *6
> 01/10/2008
> 13/10/2008
> 18/10/2008
>
> Column "B" is a list of dates in no particular order.
> Column "J" is a filtered list of those dates
> Column "K" is a list of values assigned to the different dates.
> How do I write the code to assign those values in column "K" to the
> dates in column "C"
> (they would be returned to the adjacent column "D")
>
> thank you for your help, your time & your and expertise.

I have repositioned the column labels!

Hi,

Try this in C1 and drag down

=VLOOKUP(B1,$J$1:$K$6,2,FALSE)

Mike

"ChipButtyMan" wrote:

> On 18 Oct, 11:45, ChipButtyMan <colingra...@btinternet.com> wrote:
> > Hi everyone,
> >
> >
> "C" J"
> "K"
> > 14/10/2008 01/10/2008 1
> > 01/10/2008 06/10/2008 2
> > 14/10/2008 07/10/2008 3
> > 07/10/2008 13/10/2008 4
> > 07/10/2008 14/10/2008 5
> > 06/10/2008 18/10/2008 6
> > 01/10/2008
> > 13/10/2008
> > 18/10/2008
> >
> > Column "B" is a list of dates in no particular order.
> > Column "J" is a filtered list of those dates
> > Column "K" is a list of values assigned to the different dates.
> > How do I write the code to assign those values in column "K" to the
> > dates in column "C"
> > (they would be returned to the adjacent column "D")
> >
> > thank you for your help, your time & your and expertise.
>
> I have repositioned the column labels!
>

Apologies, You wanted code

Right click your sheet tab, view code and paste this in and run it.

Sub DateCodes()
Dim MyRangeB As Range, MyRangeJ As Range , B As Range, J As Range
Dim LastRowB As Long, LastrowJ As Long
LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
LastrowJ = Cells(Rows.Count, "J").End(xlUp).Row
Set MyRangeJ = Range("J1:J" & LastrowJ)
Set MyRangeB = Range("B1:B" & LastRowB)
For Each B In MyRangeB
For Each J In MyRangeJ
If B.Value = J.Value Then
B.Offset(, 1).Value = J.Offset(, 1).Value
End If
Next
Next
End Sub


Mike

"ChipButtyMan" wrote:

> On 18 Oct, 11:45, ChipButtyMan <colingra...@btinternet.com> wrote:
> > Hi everyone,
> >
> >
> "C" J"
> "K"
> > 14/10/2008 01/10/2008 1
> > 01/10/2008 06/10/2008 2
> > 14/10/2008 07/10/2008 3
> > 07/10/2008 13/10/2008 4
> > 07/10/2008 14/10/2008 5
> > 06/10/2008 18/10/2008 6
> > 01/10/2008
> > 13/10/2008
> > 18/10/2008
> >
> > Column "B" is a list of dates in no particular order.
> > Column "J" is a filtered list of those dates
> > Column "K" is a list of values assigned to the different dates.
> > How do I write the code to assign those values in column "K" to the
> > dates in column "C"
> > (they would be returned to the adjacent column "D")
> >
> > thank you for your help, your time & your and expertise.
>
> I have repositioned the column labels!
>

On 18 Oct, 13:04, Mike H <Mi...@discussions.microsoft.com> wrote:
> Apologies, You wanted code
>
> Right click your sheet tab, view code and paste this in and run it.
>
> Sub DateCodes()
> Dim MyRangeB As Range, MyRangeJ As Range , B As Range, J As Range
> Dim LastRowB As Long, LastrowJ As Long
> LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
> LastrowJ = Cells(Rows.Count, "J").End(xlUp).Row
> Set MyRangeJ = Range("J1:J" & LastrowJ)
> Set MyRangeB = Range("B1:B" & LastRowB)
> For Each B In MyRangeB
> * * For Each J In MyRangeJ
> * * * * If B.Value = J.Value Then
> * * * * B.Offset(, 1).Value = J.Offset(, 1).Value
> * * * * End If
> * * Next
> Next
> End Sub
>
> Mike
>
>
>
> "ChipButtyMan" wrote:
> > On 18 Oct, 11:45, ChipButtyMan <colingra...@btinternet.com> wrote:
> > > Hi everyone,
>
> > * * * * *"C" * * * * * * * * * * * * * * * * * * * * * * * * * J" * *
> > * * *"K"
> > > 14/10/2008 * * * * * * * * * * * * * * * * * * *01/10/2008 * * *1
> > > 01/10/2008 * * * * * * * * * * * * * * * * * * *06/10/2008 * * *2
> > > 14/10/2008 * * * * * * * * * * * * * * * * * * *07/10/2008 * * *3
> > > 07/10/2008 * * * * * * * * * * * * * * * * * * *13/10/2008 * * *4
> > > 07/10/2008 * * * * * * * * * * * * * * * * * * *14/10/2008 * * *5
> > > 06/10/2008 * * * * * * * * * * * * * * * * * * *18/10/2008 * * *6
> > > 01/10/2008
> > > 13/10/2008
> > > 18/10/2008
>
> > > Column "B" is a list of dates in no particular order.
> > > Column "J" is a filtered list of those dates
> > > Column "K" is a list of values assigned to the different dates.
> > > How do I write the code to assign those values in column "K" to the
> > > dates in column "C"
> > > (they would be returned to the adjacent column "D")
>
> > > thank you for your help, your time & your and expertise.
>
> > I have repositioned the column labels!- Hide quoted text -
>
> - Show quoted text -


Wow! Thanks ever so much for your time Mike. Works perfectly.
I spent all last night trying to work it out & failed miserably.
Thanks again. You made my day :-)

Thanks ever so much Mike.
You just made my day.
Spent an age trying to work it out and failed miserably.
Thanks again.

On Oct 18, 3:45*pm, ChipButtyMan <colingra...@btinternet.com> wrote:
> Hi everyone,
>
> * * *"C"
> "J" * * * * *"K"
> 14/10/2008 * * * * * * * * * * * * * * * * * * *01/10/2008 * * *1
> 01/10/2008 * * * * * * * * * * * * * * * * * * *06/10/2008 * * *2
> 14/10/2008 * * * * * * * * * * * * * * * * * * *07/10/2008 * * *3
> 07/10/2008 * * * * * * * * * * * * * * * * * * *13/10/2008 * * *4
> 07/10/2008 * * * * * * * * * * * * * * * * * * *14/10/2008 * * *5
> 06/10/2008 * * * * * * * * * * * * * * * * * * *18/10/2008 * * *6
> 01/10/2008
> 13/10/2008
> 18/10/2008
>
> Column "B" is a list of dates in no particular order.
> Column "J" is a filtered list of those dates
> Column "K" is a list of values assigned to the different dates.
> How do I write the code to assign those values in column "K" to the
> dates in column "C"
> (they would be returned to the adjacent column "D")
>
> thank you for your help, your time & your and expertise.

Hi Paul

You need to use the vlookup function to get this done .
Here is how to use it
=VLOOKUP(C1,$J$1:$K$7,2,FALSE)
C1 -> Refers to the value which is in column "C"
$J$1:$K$7 -> Refers to the table array from where you need to choose
the value
2 -> is the column number in the table
Just let me know if you need any more help

Hi,

Since you didn't specify the where you data starts in column C or whether it
is solid or whether there is data below it with is not part of the data, here
is a really simple, and extremely fast macro:

Sub LookupStuff()
Selection = "=VLOOKUP(C2,K$2:J$10,2,FALSE)"
Selection = Selection.Value
End Sub

In this case the first cell to be looked up is in C2 and the lookup range is
in K2:J10, you can adjust the code to meet your needs. To use this just
select the cells in column D which you want populated and run the macro.

--
Thanks,
Shane Devenshire


"ChipButtyMan" wrote:

> Hi everyone,
>
> "C"
> "J" "K"
> 14/10/2008 01/10/2008 1
> 01/10/2008 06/10/2008 2
> 14/10/2008 07/10/2008 3
> 07/10/2008 13/10/2008 4
> 07/10/2008 14/10/2008 5
> 06/10/2008 18/10/2008 6
> 01/10/2008
> 13/10/2008
> 18/10/2008
>
>
>
> Column "B" is a list of dates in no particular order.
> Column "J" is a filtered list of those dates
> Column "K" is a list of values assigned to the different dates.
> How do I write the code to assign those values in column "K" to the
> dates in column "C"
> (they would be returned to the adjacent column "D")
>
> thank you for your help, your time & your and expertise.
>