VB Help looking up a matched id and selecting the latest note

[Minimorgie]

Hi,

I am an inexperienced VB user and I want to do something which may be simple but I can't work out how to do it. Here's the situation. In Excel I have 2 sheets, one called Information and the other called Notes. I want to copy from Notes to Information, I need to match an id from column A of Information to the id in column A of Notes (e.g. P2186), however, the id can be listed many times in Notes because it is set of diary notes listed by date, here's the layout :

id date notes text
P2186 01/01/2011 kgjkg dfkjg fkhkj
P2186 01/02/2011 kgkfg fjktggn tgkj ntgj

I need to match P2186 from Information to Notes and then select to copy the latest notes text from Notes to Information.

Can anyone please explain (simply!) how I can achieve this.

Many thanks

 

[SkipVought]

hi,

You do not need VBA to do this...
[tt]
=index(C:C,match(LookupRef,A:A,0)+countif(A:A,LookupRef)-1,1)
[/tt]
assuming that your list is sorted by id, date.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and assuming that your list is sorted ONLY by id...
[tt]
=INDEX(OFFSET($A$1,MATCH(LookupRef,A:A,0),2,COUNTIF(A:A,LookupRef),1),MATCH(MAX(OFFSET($A$1,MATCH(LookupRef,A:A,0),1,COUNTIF(A:A,LookupRef),1)),OFFSET($A$1,MATCH(LookupRef,A:A,0),1,COUNTIF(A:A,LookupRef),1),0),1)
[/tt]
it will return the FIRST occurrence of the MAX date for the given id referenced by LookupRef.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Always check the limits!
[tt]
=INDEX(OFFSET($A$1,MATCH(LookupRef,A:A,0)-1,2,COUNTIF(A:A,LookupRef),1),MATCH(MAX(OFFSET($A$1,MATCH(LookupRef,A:A,0)-1,1,COUNTIF(A:A,LookupRef),1)),OFFSET($A$1,MATCH(LookupRef,A:A,0)-1,1,COUNTIF(A:A,LookupRef),1),0),1)
[/tt]
Sorry!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Minimorgie]

Thanks for your help, I'll have a bash with your suggestions and let you know if it works out ok.

 

[Minimorgie]

I've been fiddling around with this for a while and I can't get it to work, obviously I'm doing something wrong, if you could provide an example dataset with this formula I think that would really help.
Many thanks.

 

[SkipVought]

My dataset starting in A1.
[tt]
id date notes text
P2186 01/01/2011 kgjkg dfkjg fkhkj
P2186 01/02/2011 kgkfg fjktggn tgkj ntgj
[/tt]

Please post EXACTLY, the formula that you are using.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Minimorgie]

Funnily enough I just got it working, but I also need to include the date column next to the note now, this is the formula that I used :

=INDEX(OFFSET($A$1,MATCH(F2,A:A,0)-1,2,COUNTIF(A:A,F2),1),MATCH(MAX(OFFSET($A$1,MATCH(F2,A:A,0)-1,1,COUNTIF(A:A,F2),1)),OFFSET($A$1,MATCH(F2,A:A,0)-1,1,COUNTIF(A:A,F2),1),0),1)

 

[Minimorgie]

Hi,
I've worked it all out now! Thanks very much for your help.

 

[SkipVought]

These are very powerful functions that you ought to get to know and use liberally as required:
[tt]
INDEX()

MATCH()

OFFSET()
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]