Vlookup duplicates 6

[Epsilon101]

Hi,
Got a vlookup problem in excel

I have checked out previous threads for this, i have seen Zathras' vlookupnext vba code as well, i havent had time to check it out fully.

I was trying something else and wondering if its possible.

ive sorted my numbers and used =countif($A$2:$A2,A2) in column B to give my numbers a sequence digit.

For example, data on sheet 1

IDNumber Sequence Date
9395839093 1 31/08/2004
9395839093 2 31/08/2004
9395839093 3 01/09/2004
6873859394 1 28/08/2004
5678902949 1 19/08/2004
2928384829 1 10/08/2004
2928384829 2 01/09/2004

Sheet2 , has a list of IDNumbers only once

what i wanted to try and do was, example:

IDNumber First Date Second Date third date
9395839093 31/08/2004 31/08/2004 01/09/2004
2928384829 10/08/2004 01/09/2004 N/A

and so on..
I cant remember exactly what ive tried because ive deleted it, something along these lines, put in sheet2

=IF((VLOOKUP(A2,Sheet1!A:C,2,"FALSE")=1),(VLOOKUP(A2,Sheet1!A:C,3,"FALSE")),"N/A")

i can see that its still no use for the 2 and 3, because the VLOOKUP part will still only look for first occurance, so one above will return 1 for the sequence everytime, and ill only ever find the first date and nothing else.

I cant see another way at the moment, anyone got any ideas.

---------------------------------------

Neil

 

[dk87]

Since you have 3 different columns, can you set up a different formula for each column and concatenate the sequence number?

for example:
sheet 1 data:

93958390931 31/08/2004
93958390932 31/08/2004
93958390933 01/09/2004
68738593941 28/08/2004
56789029491 19/08/2004
29283848291 10/08/2004
29283848292 01/09/2004

First Date:
=VLOOKUP(VALUE(CONCATENATE($D2,1)),$A$1:$C$6,2,FALSE)

Second Date:
=VLOOKUP(VALUE(CONCATENATE($D2,2)),$A$1:$C$6,2,FALSE)

Third Date:
=VLOOKUP(VALUE(CONCATENATE($D2,3)),$A$1:$C$6,2,FALSE)

 

[GlennUK]

You could use the MATCH function to find the row of the first occurence, and use INDEX to get the date, and then use a bit of arithmetic to test the rows after the first one.

First Date
=INDEX(Sheet1!C2:C99,MATCH(A2,Sheet1!A2:A99,0))

Second Date
=IF(INDEX(Sheet1!B2:B99,MATCH(A2,Sheet1!A2:A99,0)+1)<>2,NA(),INDEX(Sheet1!C2:C99,MATCH(A2,Sheet1!A2:A99,0)+1))

Third Date
=IF(INDEX(Sheet1!B2:B99,MATCH(A2,Sheet1!A2:A99,0)+2)<>3,NA(),INDEX(Sheet1!C2:C99,MATCH(A2,Sheet1!A2:A99,0)+2))

Good luck,
Cheers, Glenn.

 

[macropod]

Hi Neil,

Reminds me of something I've done before. Using named ranges and the ID you're wanting to match to in D3, the first match can be achieved with:
=INDEX(Date,MATCH($D3,ID_Number,0))
in E3 and all the others can be achieved with:
=IF(OR(E3="",COUNTA($E3:E3)=COUNTIF(ID_Number,$D3)),"",INDEX(Date,MATCH($D3,OFFSET(ID_Number,MATCH(E3,Date,0),0,COUNT(ID_Number)+1-MATCH(E3,Date,0),1),0)+MATCH(E3,Date,0)))
in F3 and copied across as far as you want/need. The formula returns nuls in any columns for which there aren't enough data.

Oh, and your data don't need to be sorted either!

Cheers

 

[KenWright]

Assuming your data is in A1:C8, in say E3:E6 drop a unique list of your IDs (Data Filter / Advanced Filter can give you this). then in F2:J2 put 1,2,3,4,5 ie:-

F G H I J
2 1 2 3 4 5
3
4
5
6

Then in cell F3 put the following formula, format the cell as a date and then copy down and across to J6:-

=IF(SUMPRODUCT(($A$2:$A$8=$E3)*($B$2:$B$8=F$2)*($C$2:$C$8))=0,"",SUMPRODUCT(($A$2:$A$8=$E3)*($B$2:$B$8=F$2)*($C$2:$C$8)))

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

How about you gadd an extra column in which concatenates your ID No. and Sequence
Your formulae then become very easy

=vlookup(SequenceNo & "1",lookuprange,2 false)

=if(isna(vlookup(SequenceNo & "2",lookuprange,2 false)),"N/A",vlookup(SequenceNo & "2",lookuprange,2 false))

=if(isna(vlookup(SequenceNo & "3",lookuprange,2 false)),"N/A",vlookup(SequenceNo & "2",lookuprange,2 false))

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Epsilon101]

Cheers guyz!!

All great stuff, ill have a good look at all your formulae and get back to you when ive finished it, thanks for all the replies.

---------------------------------------

Neil

 

[Jeterdawg]

I have a similar issue with VLOOKUP...I want VLOOKUP to return a value based on TWO fields. Here's the scenario: I have a user listing, and there are Application IDs and User IDs. I want VLOOKUP to be able to return Y or N based on if some individual has access to some application. Using just VLOOKUP for an application will only bring back the Y or N for the first user that it comes to. Is there a way to rig VLOOKUP (using other functions or writing a macro) to lookup the value based on two fields?

The only thing I've thought of is to sort the report I got by app ID, and perform the VLOOKUP based on the user ID, but write some macro that will only look at the range related to the app ID, and look at the range related to the next app ID, and so on. I saw above the idea for naming ranges, which seems like a good idea, but since I have 500+ application identifiers and 500+ users, I wondered if there was a better way to automate it. MS needs to make a new function for dual-referenced VLOOKUPS!

 

[xlbo]

Same answer as before CONCATENATE your userID and AppID in both the lookup range and formula eg if userID in col A and AppID in col B, in ColC have
=A2&B2

and copy it down

your lookup is then
=if(isna(vlookup(userID&AppID,LookupRange,1,false)),"N","Y")

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Epsilon101]

Hi Guyz, sorry for the late reply, i have tried all the formulae, all good stuff, incase anyone was in competition for that kiss solution, Ken & Geoff (yet again ) where the quickest for me to layout and complete.
As i didnt want to concatenate it though, i have finished with using Ken's layout. Never the less it was all good info and i know a little more about match and index functions now.

---------------------------------------

Neil