+ Reply to Thread
Results 1 to 33 of 33

looking at a row from right to left and finding matches to list

  1. #1
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    looking at a row from right to left and finding matches to list

    I have attached an example of a file i am working through. I have a company department hierarchy in rows and a separate list of important departments. I am looking from RIGHT to LEFT for a match to the "important list" and display that separately in yellow. Any help would be much appreciated, thanks.
    Attached Files Attached Files
    Last edited by drocket; 07-12-2019 at 09:28 AM.

  2. #2
    Registered User
    Join Date
    07-06-2019
    Location
    isle of arran, scotland
    MS-Off Ver
    excel since MSO98 to date
    Posts
    33

    Re: looking at a row from right to left and finding matches to list

    none of what you have supplied or said helps to get heads around your problem

    can you make a better fist of it?

  3. #3
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    I have updated the attachment with the current formula being used in E6 and E7. I then manually go through and look for D6 & C6 if they exist. I need to put something similar in D6 & E6 that gives me the next match when moving from right to left in row 2, but is not equal to the result found previously. Thanks for the help.

  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,403

    Re: looking at a row from right to left and finding matches to list

    I don't understand the results you are looking for - please explain in WORDS what your formula is doing and why.
    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,899

    Re: looking at a row from right to left and finding matches to list

    Still VERY confusing. I don't understand why 50 is most important, then 30, then 10. If the order of appearance isn't critical... but you're just looking to find which important values are present, then use:

    =IFERROR(INDEX($A2:$J2,AGGREGATE(15,6,COLUMN($A2:$J2)/(ISNUMBER(SEARCH($L$2:$L$4,$A2:$J2))),COLUMNS($C10:C10))),"")

    in C10, copied across and down...
    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

  6. #6
    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,899

    Re: looking at a row from right to left and finding matches to list

    Oh wait... I think I get it now...

  7. #7
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    Putting this into words in my head has been the hardest thing. The numbers are just dept numbers there are 1500 of them but only 87 are important and have data collection so we need to know which rows have data depts within them. Going right to left gives us the kids, parents, and grandparents. Its a company hierarchy but flipped on its side. The formula is nesting ifcounts that keeps going to the left until there is a hit on the list. I not married to that formula so if there is something simpler i'd appreciate that also.

    Something else i've tried but to many steps is adding a column next to each dept level and using a vlookup that would let me know if that dept shows up on the list. I then put that into a pivot by level and dept. My result gives me 5 columns for each level with a lot of blanks and i need to consolidate them into 3 columns removing the blanks.

  8. #8
    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,403

    Re: looking at a row from right to left and finding matches to list

    How do we know which of the (87) departments are important?

  9. #9
    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,899

    Re: looking at a row from right to left and finding matches to list

    I'm SURE that this is much easier than I have made it....

    =IFERROR(INDEX($A2:$J2,SMALL(IF(ISNUMBER(SEARCH($L$2:$L$4,$A2:$J2)),COLUMN($A2:$J2)),1/(1/(MAX(0,COUNT(IF($A2:$J2>0,IF(ISERROR(MATCH($A2:$J2,$L$2:$L$4,0)),"",$A2:$J2)))-(COLUMNS(C:$E)-1)))))),"")

    In C10, an array formula, copied across and down...

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    the important dept list is in column L

  11. #11
    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,403

    Re: looking at a row from right to left and finding matches to list

    Doh! So they are ... No wonder I don't get this!

  12. #12
    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,899

    Re: looking at a row from right to left and finding matches to list

    Slightly simpler:

    =IFERROR(INDEX($A2:$J2,SMALL(IF(ISNUMBER(SEARCH($L$2:$L$4,$A2:$J2)),COLUMN($A2:$J2)),COUNT(IF($A2:$J2>0,IF(ISERROR(MATCH($A2:$J2,$L$2:$L$4,0)),"",$A2:$J2)))-(COLUMNS(C:$E)-1))),"")

    still an array formula...

  13. #13
    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,899

    Re: looking at a row from right to left and finding matches to list

    Come on Ali.... I can't see the wood for the trees here. There must be an easier way. However, my head has seized up...

  14. #14
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    It works, thank you so much, I just hope nothing breaks because this is beyond my knowledge. I also realized i have to insert this in column C and copy it right and down to work as you said.

  15. #15
    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,899

    Re: looking at a row from right to left and finding matches to list

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  16. #16
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    Maybe i spoke to soon, it worked when i put it into the example but moving it to my actual file and changing cells in the formula my results are coming up 3 cells to the right as the dept names not numbers. For the result in E7 im getting H2 there.

    Important list is AQ3:AQ135
    levels are F3:O3
    Results fall into AI:AK

  17. #17
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: looking at a row from right to left and finding matches to list

    Hi and welcome
    although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
    So, please update your profile also adding your Excel version, some functions do not exist in earlier versions

  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,899

    Re: looking at a row from right to left and finding matches to list

    Assuming that the data, rather than the headers, startin row 3 in all cases...

    =IFERROR(INDEX($F3:$O3,SMALL(IF(ISNUMBER(SEARCH($AQ$3:$AQ$5,$F3:$O3)),COLUMN($F3:$O3)-COLUMN($F3)+1),COUNT(IF($F3:$O3>0,IF(ISERROR(MATCH($F3:$O3,$AQ$3:$AQ$135,0)),"",$F3:$O3)))-(COLUMNS(AI:$AK)-1))),"")


    array-entered.

  19. #19
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: looking at a row from right to left and finding matches to list

    @drocket
    Thank you for helping us help you

  20. #20
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    Row1 is blank
    Row2 is header

    For the first $AQ:$5 is that supposed to be $135?

    Something seems to be going funky now, it references column F a lot and seems to pick up any first number instead of numbers only in the list. If there are 2 results most of the time it puts column F for both. Even changing the $5 to $135 does the same thing.

  21. #21
    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,899

    Re: looking at a row from right to left and finding matches to list

    Yes it is. See sheet. Are there blanks in the list of important departments?
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    There are blanks figured future expansion, data is really $AQ$3:$AQ$89 I just changed that and sent the example file to my work computer and put all data into the example file. Same results, I'm also seeing things getting flipped where something that was supposed to be in AI is in AK but AI and AJ show the repeating dept. I'm also looking for spaces in the cells, im sure its something I'm doing or working off of. Would cell data type matter like changing from general to number, etc.

    Let me look at this a little more and see if there is a common theme to what's going on.

  23. #23
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    What im seeing is something doesn't work with the COLUMN($F3:$O3)-COLUMN($F3)+1 this seems to be static and doesn't work when multiple depts are present and out of order. I was able to replicate what I am seeing attached in blue. I think my example made it look like numbers will always be in order. Thank you for all your time and help.

    I can also get this to a pivot with more steps just need to consolidate it into 3 column removing blanks in the rows.
    Attached Files Attached Files

  24. #24
    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,403

    Re: looking at a row from right to left and finding matches to list

    Forgive me for saying this, but we are roughly twenty hours into this thread and now you are telling us that the sample data does not properly resemble the real data - that there will be gaps in the important departments list and that your departments may be out of order. Members invest a significant amount of their free time trying to help, but can only base their solutions on what they know that you have told them.

    Does the latest set of sample data properly reflect all eventualities?

  25. #25
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    Yes, I wasn't sure how this would turn out or how to describe it based on what would be a solution, didn't know being out of order and blanks at the end of the list would effect formula as original formula didn't have this.

  26. #26
    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,899

    Re: looking at a row from right to left and finding matches to list

    I know what the problem is... but I'm a bit frazzled now... and need to eat and have a beer. I'll think about this and get back to you...

  27. #27
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    Thank you, there is no rush on this, I had free time to work on automating this file that i just had to take over.

  28. #28
    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,899

    Re: looking at a row from right to left and finding matches to list

    One last go before I log out...


    =IFERROR(INDEX($F3:$O3,SMALL(IF(ISNUMBER(SEARCH($AQ$3:$AQ$135&" ",$F3:$O3&" ")),COLUMN($F3:$O3)-COLUMN($F3)+1),COUNT(IF($F3:$O3>0,IF(ISERROR(MATCH($F3:$O3,$AQ$3:$AQ$135,0)),"",$F3:$O3)))-(COLUMNS(AI:$AK)-1))),"")

  29. #29
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    Same results, is there a way to put in (not equal to) <>$AK3 for column AJ and <>$AK3&$AJ3. The only constant in this is that the result for AJ will always be left of where AK was found and AI will be left of where AJ & AK are found. Why looking right to left is constant.

  30. #30
    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,899

    Re: looking at a row from right to left and finding matches to list

    I cut your sample down a bit, stopping the keywords at row 10 - for ease of testing. However, to future-proof it, I replaced the fixed range with a Named range (called Import... CTRL-F3 to view it), which will expand as you add numbers -currently out to cell AQ500 - to your list of important sites.

    =Sheet1!$AQ$3:INDEX(Sheet1!$AQ$3:$AQ$500,COUNTA(Sheet1!$AQ$3:$AQ$500))

    The problem was that I had used SEARCH in the formula. So when I searched for 64 it would find 64, 640, 264, etc, etc. So I replaced it with a requirement to look for an exact match. Seems OK, now. Check it out. I'll be in-and-out of the forum from time to time today. I'm brewing beer today... 08:00 and I'm already covered with barley dust... much more productive than the EF...
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    11-29-2006
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: looking at a row from right to left and finding matches to list

    Sorry for the late reply, this worked!! Thank you so much for everything. Enjoy the beer.

  32. #32
    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,403

    Re: looking at a row from right to left and finding matches to list

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  33. #33
    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,899

    Re: looking at a row from right to left and finding matches to list

    Yuou're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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] Comparing two data sets and finding matches or non matches
    By b0bertini in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-18-2017, 09:34 AM
  2. [SOLVED] Summing the matches of one column only if its corresponding description matches a list
    By bishop-jese-erl in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-19-2015, 04:26 PM
  3. Replies: 4
    Last Post: 11-09-2014, 10:40 PM
  4. Copy a row if it matches a data from the left most column
    By Zamiur in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-25-2014, 10:09 AM
  5. Finding numbers left in list
    By khank in forum Excel General
    Replies: 2
    Last Post: 03-13-2011, 01:28 PM
  6. 2 column list box - finding matches to column 1
    By JamesPLile in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2011, 09:11 AM
  7. Replies: 10
    Last Post: 12-17-2009, 02:00 AM

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