+ Reply to Thread
Results 1 to 21 of 21

To find the cells in column A not existing in column C and display them in rank order

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    To find the cells in column A not existing in column C and display them in rank order

    Hi everyone, I'm very new to VBA and look for help for a practical Excel example. I've got it work in Excel but it will be much easier if using a VBA function. Sample attached.

    Column A: company name, column B: related weightings.
    Column C: company name.

    The expected results are shown in column G:

    Names in column G are those that: exist in column A but do not exist in column C, and they are ranked based on the related weightings (shown in column B).


    How can I get that output? Assume column F is the ID of rankings 1, 2, 3.... I can type the UDF in each cell of column G as:

    In cell G2: =Function_Port(A1:A20,B1:B20,C1:C20,F2), this will give me the company which exists in column A, but does not exist in column C and has the largest weight;
    In cell G3: =Function_Port(A1:A20,B1:B20,C1:C20,F3), this will give me the company which exists in column A, but does not exist in column C and has the 2nd largest weight;
    ...

    The expected results are shown in column G of the sample file.
    Attached Files Attached Files
    Last edited by alice2011; 02-15-2015 at 09:14 PM.

  2. #2
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    I just edited my question to make it easier to understand. So it may be quicker for a solution
    Last edited by alice2011; 02-15-2015 at 04:58 PM.

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi Alice

    I've looked at your Thread several times. I'm a bit confused about your desired output.

    Your Sample File shows this

    1 7.2% Microsoft
    2 7.1% Nike
    3 7.0% Caterpillar
    4 7.0% Chevron Corp
    5 6.6% JPMorgan Chase
    6 6.0% AT&T
    7 5.5% Johnson & Johnson
    8 5.0% Cisco Systems
    9 4.2% Merck
    10 2.1% Walt Disney

    But this
    Function_Port(A1:A20,B1:B20,C1:C20,2)
    would indicate you wish only to see this

    2 7.1% Nike

    Please clarify.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi, John, thanks for your kind help.

    I will have a column with the RankID such as 1, 2, 3, ..., and then next to it, I will use the Function_Port individually, for each RankID, to get the individual company names.

    So the return of Function_Port will be a single company name corresponding to the RankID based on the weighting. When I get the list of names, I can then simply use VLOOKUP to get the related weightings.

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi Alice

    Can you show me this...expected output...
    I will have a column with the RankID such as 1, 2, 3, ..., and then next to it, I will use the Function_Port individually, for each RankID, to get the individual company names.

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi John,

    I use some example below. Assume RankID is in column F, next to it in column G, I type the VBA function for each RankID. As expected, the formula =Function_Port(A1:A20,B1:B20,C1:C20,F2) will be evaluated to be 'Microsoft'.


    RankID Company
    1 =Function_Port(A1:A20,B1:B20,C1:C20,F2)
    2 =Function_Port(A1:A20,B1:B20,C1:C20,F3)
    3 =Function_Port(A1:A20,B1:B20,C1:C20,F4)
    4 =Function_Port(A1:A20,B1:B20,C1:C20,F5)
    5 =Function_Port(A1:A20,B1:B20,C1:C20,F6)
    6 =Function_Port(A1:A20,B1:B20,C1:C20,F7)
    7 =Function_Port(A1:A20,B1:B20,C1:C20,F8)

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Img.jpg

    The image here may be better to explain. Sorry for the confusion.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi Alice

    We're beginning to communicate...I still don't understand your desired OUTPUT...please try again...in the Workbook...can't work with pictures.

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi John, sorry for the confusion. I just amended the original post to make it more clear. Hope this time it is all clear.

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi Alice

    I'm at a loss...I've ask for help...

    http://www.excelforum.com/the-water-...ml#post3990340

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: To find the cells in column A not existing in column C and display them in rank order

    Try and see if this is how you wanted
    1) Select G2:H2
    2) enter formula
    =IF(ROW(A1)<=$F$1,IFERROR(Function_Port($A$2:$B$17,$C$2:$D$17,ROW(A1)),""),"")
    Confirm with Ctrl + Shift + Enter (Array forumla entry)
    3) Copy down as much as you want
    4) format Col.H as "%"
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Many thanks, John. Very appreciate it.

    I guess the tricky thing is about: how to deal with the case when two companies meeting the criteria have exactly the same weight, for example, Caterpillar, and Chevron Corp.

    I will wait to see if anyone can come up with a nice VBA function

  13. #13
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi, jindon, your code worked. Thank you. It is really too advanced for me .

  14. #14
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: To find the cells in column A not existing in column C and display them in rank order

    The code below will work. But it cannot deal with cases when two companies have exactly the same weight in column A. Anyone can improve it?

    Please Login or Register  to view this content.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: To find the cells in column A not existing in column C and display them in rank order

    Quote Originally Posted by alice2011 View Post
    Hi, jindon, your code worked. Thank you. It is really too advanced for me .
    Is this easier to understand?
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi jindon, the new code is really easier for me to understand. It worked perfectly. Thank you so much for your time

    I very appreciate the knowledge learned from the example.

  17. #17
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi, Jindon, can I ask again? I realised that it will be better to allow the flexibility: when the company name and weight are NOT in adjacent columns. In this case, I would need to specify the function as:

    Please Login or Register  to view this content.
    Here, Range1 is for company name, Range2 is the weight, and Range3 is for the company name to be matched. For example, I will specify it as:

    =IF(ROW(A1)<=$F$1,IFERROR(Function_Port($A$2:$A$17,$B$2:$B$17,$C$2:$C$17,ROW(A1)),""),"")

    For this case, how to adapt your code? Thank you.

  18. #18
    Registered User
    Join Date
    02-15-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2007
    Posts
    3

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi there,

    I would just do a little loop & sort approach, might be a bit easier to understand.

    Sub Analysing_Table()

    Range("G2:H50000").ClearContents
    Application.ScreenUpdating = False
    Rec = 1
    LR_A = Range("A1").End(xlDown).Row
    LR_C = Range("C1").End(xlDown).Row

    For a = 2 To LR_A
    For c = 2 To LR_C
    If Cells(a, 1) = Cells(c, 3) Then GoTo 5
    Next

    Rec = Rec + 1
    Cells(Rec, 7) = Cells(a, 1)
    Cells(Rec, 8) = Cells(a, 2)
    5 Next
    Range("G1:H" & Rec).Sort Key1:=Range("H1"), Order1:=xlDescending, Key2:=Range("G1"), Order2:=xlAscending, Header:=xlYes
    Application.ScreenUpdating = True
    End Sub

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: To find the cells in column A not existing in column C and display them in rank order

    Do you mean
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    01-16-2014
    Location
    London
    MS-Off Ver
    Excel 2003 Excel 2010
    Posts
    189

    Re: To find the cells in column A not existing in column C and display them in rank order

    Hi, Jindon, that's exactly what I need. Thank you so much. It is really useful to me. I'm learning from these useful helps.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: To find the cells in column A not existing in column C and display them in rank order

    You are welcome and thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Rank set of numbers order within respective Group ID in another column
    By Brawnystaff in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-28-2014, 04:08 PM
  2. Replies: 2
    Last Post: 09-04-2014, 02:08 PM
  3. Find value of a column in another column an display results
    By s_anr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2013, 09:43 AM
  4. Replies: 1
    Last Post: 12-21-2011, 02:31 PM
  5. Combination chart - area and column - order display?
    By Gary King in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-04-2006, 04:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1