Solved

How to vlookup for duplicates and display the results in a column

Posted on 2011-03-11
13
275 Views
Last Modified: 2012-05-11
Hi Experts,

I have a difficult task which I hope to automate as much as possible. I have attached a sample excel file. The actual file has about 30 columns and 50K+ rows.

I have a master list with columns "IC Number", "ADID" and "Role".

I am given a list with only the "IC Number" and I need to find the "ADID" that are active and disable. The same "IC Number" may have two or more "ADID", hence vlookup will not work. The worksheet tab "desire results" is what I hope to achieve.

How can I do this with the least amount of effort? I am willing to pick up any skills necessary.
Although I am currently using excel 2003, I am open to solutions in 2007. I will push to upgrade if it can dramitcally reduce my work load.

Thanks.
vlookup-ee.xls
0
Comment
Question by:Decarn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 8

Expert Comment

by:jawa29
ID: 35106603
Hi Decarn

This link looks like something to helop with this.

http://www.cpearson.com/excel/Duplicates.aspx

Jawa29

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35107706
How about a custom function, based on your data layout.

You enter in B2 and down: =LookUp2($A2,'Master List'!$A$2:$A$12)

In C2 and down: =LookUp2($A2,'Master List'!$A$2:$A$12,TRUE)

Function LookUp2(vItem, rData As Range, Optional bDisabled As Boolean = False) As Variant
 
Dim rFind As Range, s As String, sOut As String

With rData
    Set rFind = .Find(What:=vItem, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        s = rFind.Address
        Do
            If bDisabled Then
                If rFind.Offset(, 2) Like "DISABLED*" Then
                    sOut = sOut & ", " & rFind.Offset(, 1)
                End If
            Else
                sOut = sOut & ", " & rFind.Offset(, 1)
            End If
            Set rFind = .Find(What:=vItem, After:=rFind, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         Loop While rFind.Address <> s
    End If
End With
     
LookUp2 = Mid(sOut, 3)

End Function

Open in new window

0
 
LVL 4

Expert Comment

by:mawni
ID: 35107714
I am sorry, but I can't understand you, can you please give me an example with sheet names and cell index, and I will solve the file and send it back to you
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Decarn
ID: 35124762
Hi Guys,

Apologies for the late reply. It was the weekend.

Mawani,
I have reattached the excel file.
"Master List" worksheet: This is the list that I already have and it contains the IC Number, ADID and Role. If you notice, there are duplicate IC Numbers but different ADID and Role. i.e. each IC Number can have muplitle ADID and roles.

"List to match" worksheet: This is the list that was given to me to match against the "Master List". It only contains the IC Number and I have to extract all the ADIDs and Roles based on the IC Number.

"Desired Result" worksheet: This is what I want to acheive. Base on the IC Number, I extracted all the ADID and categorised them into Active and Disabled. Active and Disabled is based on the Role in the Master List. As long as there is a word "Disabled" in the Role column, the ADID is deemed as Disabled. Otherwise it is active.

I hope this is clearer and now that the weekend is over, I will respond asap.

Thanks.
vlookup-ee.xls
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35127431
Did you see my suggestion?
0
 

Author Comment

by:Decarn
ID: 35134683
Hi StephenJR,

Apologies, it has been some time since I studied VBA functions in excel. Trying to figure out how to put in the code you have given. Some guidance to kick start will be apprecaited.
0
 

Author Comment

by:Decarn
ID: 35134765
Hi  StephenJR,

I got the function to work. Active ADIDs looks good but disabled ADIDs are also showing up as active ADID. Can you also be kind enough to comment your code so that I can learn too.

Thanks.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35141645
No problem. Did you see my example?

For column B you need only the first two parameters, e.g.

B2 =LookUp2($A2,'Master List'!$A$2:$A$12)

For column C, you need to specify the third parameter as True (by default it is False so if omitted it will be False)

C2 =LookUp2($A2,'Master List'!$A$2:$A$12,TRUE)

It worked for your sample file - see attachment. If you are trying it on a different dataset perhaps that is causing a problem.
vlookup-ee.xls
0
 

Author Comment

by:Decarn
ID: 35144989
Hi Stephen,

I downloaded your file but I still see that in the "Given List to match" worksheet, disabled ADID is also flag as active. In the "desired result" workesheet, everything seems fine but there is no formula.
Lookup2.JPG
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 35146122
Apologies, a bit of it flew right over my head! I think this should do it now:
Function LookUp2(vItem, rData As Range, Optional bDisabled As Boolean = False) As Variant
 
Dim rFind As Range, s As String, sOut As String

With rData
    Set rFind = .Find(What:=vItem, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        s = rFind.Address
        Do
            If bDisabled Then
                If rFind.Offset(, 2) Like "DISABLED*" Then
                    sOut = sOut & ", " & rFind.Offset(, 1)
                End If
            Else
                If Not rFind.Offset(, 2) Like "DISABLED*" Then
                    sOut = sOut & ", " & rFind.Offset(, 1)
                End If
            End If
            Set rFind = .Find(What:=vItem, After:=rFind, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         Loop While rFind.Address <> s
    End If
End With
     
LookUp2 = Mid(sOut, 3)

End Function

Open in new window

0
 

Author Comment

by:Decarn
ID: 35187716
Hi Stephen,

It works. I'm still trying to understand each line of code because the actual spreedsheet has a lot of columns and data. Will appreciate if you could expalin each line of code.

Thanks a lot.
0
 

Author Comment

by:Decarn
ID: 35291064
Hi Stephen,

I have another query. Using back the same example, I now have the ADID and will need to distinguish between Active and Inactive. I have attached the sample worksheet - "Given List to match 2" and "Desire result 2".

Do you require me to log in as another question so that I can award more points to you.

Thanks.
vlookup-ee.xls
0
 

Author Closing Comment

by:Decarn
ID: 35338865
Thanks Stephen.

I will create a new question for my next question.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question