+ Reply to Thread
Results 1 to 23 of 23

List of names to find in another list

  1. #1
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    List of names to find in another list

    Friends!

    I hope everyone's happy and feeling good!

    So, I have a list of names, which I have named, Fruits.

    Then, I have another list of names, which I have named, Seller.

    I would like to count how many Fruits has Seller and Seller has Fruits.

    But the issue is that basically "Apple" would be concealed in the form of "123POlAPPLE090kljkl".

    Therefore it is hard to use the simple function of COUNTIFS.

    I have attached the file to have a better understanding.

    I have inserted the formula of SUMPRODUCT to calculate but of course there is something not right.

    I would like to thank you all for the help!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of names to find in another list

    Maybe this, E2:
    =COUNTIFS($A$2:$A$76,D2)

    H2:
    =SUMPRODUCT(--ISNUMBER(SEARCH(G2,$B$2:$B$76)))

    both copied down.

    If not correct, show EXPECTED answers.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    Thank you very much for the answer.

    Yes, it is correct.

    My concern is this:

    I have named these lists.

    When you were about to enter the function in the cell, was it not possible to use the list names that I have created?

    I have attached the file where you have inserted the SUMPRODUCT function with my amendment, of course, it doesn't work.

    Could you please make it work the way I amended it?

    Basically, instead of selecting G2 cell, I have used Fruits instead, which consists of the whole list. Then it could be dragged down, giving the same answer.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: List of names to find in another list

    No new workbook attached ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of names to find in another list

    No. It doesn't work that way.

  6. #6
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    I am trying to attach but this thing keeps logging me out. :'(
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: List of names to find in another list

    Tick the button to keep yourself logged in next time you log in.

  8. #8
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    Alright, then, let us suppose, which, by the way, I need.

    Basically, you have counted the list of fruits, according to your formula, but what if I need to count for some but not for all?

    For example, there is a list of Seller, which includes all, but suppose, I need only for:

    - John
    - Alfred
    - Carl
    - Brian

    For the above, I may need to calculate, how many Apple John has, Alred, Carl, or Brian?

    Furthermore, Orange for John, Alfred, or Carl?

    Please see the attached file.
    Attached Files Attached Files
    Last edited by major_johnson; 10-19-2022 at 04:41 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: List of names to find in another list

    See if this does what you want (untested):

    =SUMPRODUCT(--ISNUMBER(SEARCH(Fruits,$B$2:$B$76))*--ISNUMBER(SEARCH($A$2:$A$76,{"Brian","John","Peter"})))

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of names to find in another list

    This is completely baffling. Please upload a file with EXPECTED answers, not with a non-working formula. How/where do youe define John, Brain & Peter for apple, etc?

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of names to find in another list

    Also confirm that you're using Excel 2010.

  12. #12
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    Yes, this is the one!

    I had to remove Fruits in order to adjust it and make it work.

    It worked!

    But, again, what would be perfect is that instead of using names, such as, {"Brian","John","Peter"}, I would really like to plugin lists that I would create.

    For example, instead of, {"Brian","John","Peter"}, I would like to create a list and name it, BJP.

    Would that be possible?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: List of names to find in another list

    What happened when you tried???

    The array could just as easily be a cell range, so it could just as easily be a named range. Let us know what happens once you have tried it for yourself.

  14. #14
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    I tried before messaging.

    But it didn't work.

    See, I created a range and then tried to plug into the function but it didn't work.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: List of names to find in another list

    OK - show us!!! Workbook, please ...

    You asked if it would be possible - no hint that you had tried it.

  16. #16
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    Please see attached.
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: List of names to find in another list

    This is of little use, since you still haven't told us the results you are expecting!!! Please add a column with your expected results.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of names to find in another list

    While I look at this, how is H3 meant to pick up Amy, Abraham, and Lenny

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: List of names to find in another list

    That's what I was thinking, Glenn - I'm doing no more until we are given expected results, mocked up manually.

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of names to find in another list

    In H2:

    =SUMPRODUCT(--ISNUMBER(SEARCH(G2,$B$2:$B$76))*(ISNUMBER(MATCH(T(INDEX(+A2:A76,)),JBP,0))))

    However, I still need an answer to my last Q.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    Mr. Glenn Kennedy,

    I don't know how to thank you, man!

    How are you just the best?!

    The file you shared is out of this world!

    However, I had to adjust the formula to get the correct answer. I had to keep +A2:A76 for all to get it right.

    You could see it, as attached.

    But this is the best thing I have seen so far!
    Attached Files Attached Files

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: List of names to find in another list

    Change to :

    =SUMPRODUCT(--ISNUMBER(SEARCH(G2,$B$2:$B$76))*(ISNUMBER(MATCH(T(INDEX(+$A$2:$A$76,)),JBP,0))))

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  23. #23
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: List of names to find in another list

    Friends,

    Yes, I am confusing and I really don't know how to explain.

    Please accept my sincere apologies.

    I don't know where I would turn to if it hasn't been for you guys!

    Seriously, you are all Aces!

    Mr. Glenn Kennedy, you just know you are the one and only!

+ 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. list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Ed in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  2. [SOLVED] list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Max in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 05:05 PM
  3. list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Ed in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  4. list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Ed in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  5. list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Max in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 11:05 AM
  6. list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Max in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 08:05 AM
  7. [SOLVED] list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Ed in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  8. [SOLVED] list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
    By Ed in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2005, 09:05 AM

Tags for this Thread

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