+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 16 to 30 of 33

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

  1. #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

  2. #17
    Forum Moderator
    Join Date
    05-14-2009
    Location
    RDC
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,245

    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

  3. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,029

    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.
    Glenn



  4. #19
    Forum Moderator
    Join Date
    05-14-2009
    Location
    RDC
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,245

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

    @drocket
    Thank you for helping us help you

  5. #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.

  6. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,029

    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

  7. #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.

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

  9. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,280

    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?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #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.

  11. #26
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,029

    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...

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

  13. #28
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,029

    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))),"")

  14. #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.

  15. #30
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,029

    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

+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast

Thread Information

Users Browsing this Thread

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

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