+ Reply to Thread
Results 1 to 45 of 45

Index Match that returns all non zero values in row

  1. #1
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Index Match that returns all non zero values in row

    I have a massive workbook that I've been tasked with creating a new sheet in that summarizes about a dozen other sheets worth of data.
    I've created a sort of compilation sheet that puts together the data, then it will go on the summary sheet that will show only the nonzero consolidated data.
    The problem I have is trying to only pull the nonzero columns into my array.
    Here's the table I've created pulling from all the other sheets, the dates of course extend much, much further out, but you get the idea:
    overview.jpg
    Each of the possible 8 build types (some will be all zeros) have been summarized with an array below that for example this one, any row where Build 1 is greater than zero returns the other data from the row that I need.
    build1.jpg
    What I need is next to each of those, I need a match to look for the rows that contain that data, there will never be exact duplicates. Then I need it to go down the rest of the row to the right and return only the non zero fields, there should only be 1-4 fields with a >0 value. And I will also need the corresponding date above them. But If I can just get the non zero values I can do another index match to find the column headers for those specific values.

    Any ideas on how to go about this?

  2. #2
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Index Match that returns all non zero values in row

    It would be a much better if you make up a sample workbook showing what you have & what you need. It's to much like hard work trying to it figure out from pictures

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Index Match that returns all non zero values in row

    May be try

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    have a look at attachment.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    Would any of the Builds 1 - 8 ever include multiple Build Lots (column B)?
    Dave

  5. #5
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    I can't attach the workbook as it is far too large, as well as being for work and containing sensitive information.
    Flame, yes, it is possible that a build will have more than one category from column B. But I account for that when I pull out the non zeros and create the table in the second picture. As it's basically just pulling those columns for anything >0 in that particular Build table. Which of course I have 8 of them.

    Thank you Shukla, that is basically exactly what I already have in the second image. And I have one of those for each of the 8 builds.
    What I need to do now is next to that, do the index match to find it in the larger table (1st image), and go down the row to the right, it goes to column CB, and return the data anywhere that it is greater than 0. It will be an array formula obviously. I'm just not sure how to get it to search horizontally instead of vertically.
    Last edited by kvarner; 02-10-2016 at 11:18 AM.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Match that returns all non zero values in row

    Quote Originally Posted by kvarner View Post
    I can't attach the workbook as it is far too large, as well as being for work and containing sensitive information.
    Just make up a SMALL sample file with dummy data.

    20 rows worth of data is plenty.

    An *.xlsx file with 20 rows and a few columns worth of data will be about 10kb in size.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    The only data that's relevant is the sheet I took the images of so I've attached a copy of that page.
    As I said, I just need and Index Match that will search horizontally down the row and return anything greater than 0. It seems like that's pretty straight forward and there should be a way to do it, but I could be wrong. It would just find the row using match from the data to the left in the build tables, start at column M and search down the row returning anything that's not zero. It would be nice to put the date next to it but I'm not sure how to format that so I'll just worry about finding the date information after I get over this hurdle.
    Attached Files Attached Files

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    kvarner,

    It would be nice to put the date next to it but I'm not sure how to format that so I'll just worry about finding the date information after I get over this hurdle.
    I am not seeing the relationship between the dates / builds / numbers<>0. Can you help us out?

  9. #9
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    I'll try!
    If you're looking at Sheet3, there are several types of BuildLot, for each build lot there are 7 possible Calculations (1 for each part of the vehicle basically). Then there are up to 8 possible builds, which are basically different versions of a car, like FWD, 4WD, Limited, etc. Then across row 1 the headers are dates and the data below them are percentages.
    So if you look at row 16, you have build lot OX-2, CBU...Build 1 has a quantity of 10 and Build 2 has a quantity of 4. If you scroll to the right you will see that this means that 50% of that quantity will be done 3/1/2018 and 50% in 4/1/18. This is of each build.
    Therefore in my finished product I will have, Under Build 1, then number 5 under the date 3/1/18 and the number 5 under 4/1/18. And under Build 2 I'll have 2 under each of those dates.
    In some cases it will be different than 50%, you could do 10% one month, 30% one month, etc. I've attached another Sample and added my Schedule output sheet. That should help clarify the relationship and where things are ending up. There's some example data in there showing what it should loook like for OX-2.
    And in all honesty, I'm beginning to think this may be a lot simpler to do in VBA. But I'm not certain yet.
    Attached Files Attached Files

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    Kvarner,

    Please see the attached.

    The heart of this arrangement is in a helper column in column J. For convenience and clarity the “Build” references are moved back to column D by themselves.

    The helper is there for 4 reasons:
    1. It returns the column numbers for each “Build” by MATCHing a continuous lookback in column D to E1:L1.
    2. It saves having to repeat this calculation in the main formulae. (columns E:H)
    3. It shortens the main formulae.
    4. But the main reason is that it allows us to reset the K argument of SMALL back to 1 with each change of “Build” by using a COUNTIF t
    hat references the helper values.

    The helper formula is entered with a simple Enter and filled down as far as needed
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula in E82 array entered, filled across to column G and down as far beyond Build 8 as needed is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    You will get blanks after Build 2 due to the IFERROR.
    If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    In H82 this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    filled down as far as you filled the previous array formula.

    RE: the date part. I must confess that I am having difficulty finding a way to tell Excel how to do those match-ups. Formula-wise this may require some hyper-advanced MATCH constructions that are beyond my horizon … unless I am missing something obvious. If this were only two dates relating to two Builds I might be able to pull this one off, but with 3 or more I am afraid I am down-for-the-count.

  11. #11
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    Flame,
    First, thank you so much for taking the time to help!

    The formula I currently have to return all the build info for each one is:
    Please Login or Register  to view this content.
    As an array of course. I can't really tell what the difference would be between using what you've provided and what I already had?

    There must be a way to return all the non zeros in the row so that I can get all the percentages down there that I need. Then maybe I could use your formula for returning the column number to determine where those numbers are and in turn use the column number to return the data in row 1? i.e. the date

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    The formula I currently have to return all the build info for each one is:
    Yes I see that. I also see that at each break in the Build sections you have to manually reset the ROW argument and manually change the column argument.

    When I see the layout and structure of your table A:M I see that you have a good thing going.

    What I did was to attempt to exploit those advantages so that there is no need to manually edit anything. Excel can do all the work and save you from the tedious task of editing each section / column. I failed to mention that the helper column also assigns the column argument to the inner-most INDEX function and automatically identifies the current Build .... Build1, Build2, .... Build-n.

    It's all automatic. The COUNTIF replaces the ROW argument in the SMALL function thereby resetting the count to 1 automatically at each Build change. This permits you to use just two (not including the helper column) formulas filled down (and one of then across 3 columns) to cover four columns and 8 Builds all at once rather than using what amount to 32 variations of the same formula.

    Looking forward it was also my hope that we might be able to exploit this setup to gather the dates associated with each Build. I still haven't come up with a satisfying approach to this, yet. I am still at the stage of identifying what the next questions are.

    Was there something about the proposal I uploaded that is problematic?
    Last edited by FlameRetired; 02-11-2016 at 11:32 PM.

  13. #13
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    No, no problem at all. I just couldn't tell what was different. But now I understand, thank you.

    And yes, having the column number returned seems like a definite start on the path to getting the date!
    If we apply that same formula indexing the entire range where the dates/quantities are, it should return the number anywhere there is an amount, and then the column number to the right of that. As an array of course. Then maybe create just a simple vlookup to get the date from the column number?
    It seems like a start anyway!

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    OK.

    I am going to be occupied for the weekend. It will be a few days before I can come back.

    I have a possible start on returning the dates. My solution is filled with assumptions. Before investing too heavily in those I need to map out a series of questions and how to ask them clearly. Please bear with me. This is likely to be tedious. The answers to each question will determine what the next questions are.

    Presently in your example there are only two Builds sharing the same rows with non-zero numbers (columns E:L). Is there any possibility there could be more than two Builds sharing the same row with non-zero values and therefore more than two percentages in columns M:CO in that same row?
    Last edited by FlameRetired; 02-12-2016 at 06:38 PM.

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    kvarner,

    Ignore my previous post. I found the answer.

    In the attached this array formula in L82 filled down makes an additional helper column that locates and counts the percentages.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in I82 this array entered formula filled down returns the dates.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    This is great! Definitely going in the right direction. Thank you again for taking the time!

    I've been playing around with what you came up with. I tried putting the date to the right of helper 2, column M. Thinking I could extend the array to the right to have it return all the percentages and dates in that row, but that didn't really work. What's interesting, if you look at how you have it right now, look at OX-2 CBU. In build 1 It returns the first date and in build 2 it returns the 2nd date in the row. Obviously I need both dates for both builds but I'm curious why it's returning different dates for each build. I would think it would just return the first possible date in both cases.

    And the CountIf doesn't seem to be working. For build 1 it returns all 1's and there are definitely more than one non zero in most of those.
    Last edited by kvarner; 02-16-2016 at 12:25 PM.

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    Yes. That is what I was trying to communicate in the post previous to my last.

    I don't know how to tell Excel (if it's possible) how to distinguish which of the multiple builds go with which of the multiple percentages / dates.

    Why my upload is returning different dates for each Build in OX-2 CBU is because I could not understand that you wanted anything different. I had to make some reasonable guesses ... for example Build1 goes with date 1 and Build2 goes with date 2. I can now see that isn't the case.

    In addition QC CBU (row 65) has two qualifying Builds but only one percentage and therefore one date.

    What happens in other cases? Three Builds with 2 percentages/dates ... four or more Builds with ?? percentages/dates?

    We can match percentages to dates. That part isn't a problem. But matching Builds to percentages is a problem especially when more Builds are introduced.

    Can you upload another workbook with:
    1. a more comprehensive representation of the combinations of Builds and their percentages you will be working with.
    2. a hand typed layout of the desired results to include intended dates corresponding with each intended Build.
    3. a description of the relationships ... ie why those percentages/dates match up with those particular Builds.
    4. Please reference episodes where there are more than two Builds and more than two percentages.
    5. Please also reference episodes were there are say three or more Builds and only two percentages
    .

    Also,

    And the CountIf doesn't seem to be working. For build 1 it returns all 1's and there are definitely more than one non zero in most of those.
    COUNTIF is not counting the non-zero numbers. It is uses the counts to determine the index numbers pairing the percentages with their respective Builds / BuildLots / Calculation Groups. The main formula uses the referenced indexes (the ones and twos) to return the dates. The ones are pointing to the first percentages ... the twos to the second. Notice in row 107 the COUNTIF returns one in the midst of twos. That is because there is only one percentage in row 65 (QC CBU).
    Last edited by FlameRetired; 02-17-2016 at 07:10 PM. Reason: oversight / clarity

  18. #18
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    I think I'm a little confused. I know my table is a little large, but I think it's pretty straight forward. If you follow a row across it will show the quantity being built for each build, and then what percentage of that quantity is being built in what month. So it doesn't matter if there is only one build or all 8 builds. It will always be quantity times percentage for that date.
    There can and are more than two percentages, or even just 1 percentage (100%) on the table I uploaded.
    The relationships are well above my paygrade, I get a spreadsheet that shows my what percentage are being done what months. That's what you're seeing in my upload. I assume someone in production and scheduling comes up with that.
    For this particular model of vehicle this is actually the correct data. There are only 2 builds and those are all the percentages. As in the example when I previously posted an explanation, for OX2, 50% is done in March and 50% is done in April for every single build where there is a quantity.

  19. #19
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    OK

    Please check the attached and see if I am up to speed.

    In column K another helper.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in N82 filled across column O and down to row 235 (covers all possible builds from the previous formulas) this non-array formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    then in P81 and filled across to W81 this array formula to generate headers re: the Builds that are active in the first set of formulas in columns D:H.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then to generate the dates in the layout I believe you are looking for this array entered in P82 filled down and across to W235.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    Okay, I think this will return all possible dates extending out to the columns to the right even if there are quite a few, so this should work.
    And yes, you definitely understand what's going on, I think. But the columns of dates aren't Build specific, it's going to be the same dates for all possible builds, just different quantities. But I can copy your same formula down so that it checks the dates for the data in Build 2, 3, etc. In some cases there will be a build lot/calc group that only shows up in one build. That's an easy adjustment. Thank you so much!!

    Only thing left now is to figure out how to get it into that scheduling output sheet! I'm pretty sure I can use the same basic formulas right? Index and search the row with dates to get the column number and then return the quantity*percent for that date. I'm wondering if there's a way to just put these formulas into that sheet and eliminate the need altogether for this "helper" sheet?

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    Not sure if I have communicated a point properly. Those latest formulas in P2:W235 will only handle 1 or 2 dates for 1 or 2 Builds. If there are more Builds / Dates in any given row that will be a different problem.

    Are we still OK?

  22. #22
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    Double posted.

  23. #23
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    Ah. Well then that doesn't work. I just assumed that since the array went all the way over to the right that it would return all possible dates in the additional columns. And since it's just using a match to find the row, I could just extend it down further to have it match for the other builds.
    But it is returning the dates so it's progress! Should be tweakable.

  24. #24
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    And I still can't figure out how to get the percentages that fall under those dates.
    This is proving to be a lot more complicated that I initially thought it would be!

  25. #25
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    Yes. It is complicated.

    Can you upload another workbook that shows us what you are now working with?

  26. #26
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    Let me explain a little about what is in this sample.
    The "Calculation" sheet is the one where all this data is coming from, or at least most of it. It's extremely complicated and hard to follow.
    Which is why I have the arrays at the top of Sheet3 to better consolidate the data I need and make it a little more readable.
    Sheet5 is the beginning of an attempt to use a few less formulas. Basically it creates the same lists for each build that you see on the lower part of Sheet3 but the formulas look for nonzero rows directly on the calculation sheet. I think this may speed things up a bit and make it even more readable since there aren't all the rows of data I don't need. Haven't tried retrieving dates directly from the Calculation sheet yet, but if I can figure out how to do it just with Sheet3 I'm sure I can adapt it.
    The schedule output sheet of course is still where everything needs to end up for reporting. Under the correct date, and the quantity divided by the percentage being done that month.
    Attached Files Attached Files

  27. #27
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    This is quite a bit different than what we started with structure-wise, and I can't tell what the formulas are trying to do. They have a lot of #REF! errors.

  28. #28
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    That sheet is just to see an example of where I'm getting the data. It does pull from a lot of different places so that's why there are errors. None of the formulas matter on the calculation page, those aren't even mine.
    If you look at the formulas that create the table on Sheet3 you can see where the data is coming from on the Calculation page.
    Sheet5 is just a formula that skips the need to create that table on Sheet3.
    The structure of Sheet3 is the same as it's always been, that's what we've been working with. And the schedule sheet that it's going to end up on is still the same.

  29. #29
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    So we're starting over again?

  30. #30
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    No not at all. Everything is the same. Sheet5 was just me playing around trying to use fewer formulas. I just included it because it's what I was playing around with at the time.

  31. #31
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    OK.
    So we should continue building the summaries in Sheet3.

    Do you have a sample that would generate more than two Builds in the summary columns D:H?

  32. #32
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    Correct.
    I don't have any actual data on hand that would do that. I just throw some random numbers in each of the builds and a few percentages over to the right. Doesn't really matter what they are. As long as the formulas work then they'll work when it's real data too.

  33. #33
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    OK. I'll try this question.

    If there were 3, 4 or more qualifying Builds crossing the same row at non-zero values how many percentages would be off to the right?

  34. #34
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    The number of builds or vehicle types there are doesn't effect what percentage is done in what month.
    So whether there is 1 build or 8, that doesn't change anything.
    But in this case all of these happen to be 50%, so 2 dates, half and half. It could be 33.3% across 3 dates or 25% across 4 dates. But the dates and percentages will be exactly the same for every build.
    It just goes across the row.

  35. #35
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    But the dates and percentages will be exactly the same for every build.
    It just goes across the row.
    That part was never a mystery.

    This part was.

    It could be 33.3% across 3 dates or 25% across 4 dates.
    The values of the percentages was never an issue that I could see. It was the number ie the actual count of the percentages that I wondered about.

    That's a different problem requiring a different formula than I previously submitted. That formula will only return dates for 1 or 2 Build percentages. More than that will return wrong answers.

    With the lone exceptions of the 2 Builds one percentage in the upload, can we now assume the relationships between the count of Builds and count of percentages (which in turn determine the dates) will always be one-to-one?
    Last edited by FlameRetired; 02-29-2016 at 07:17 PM.

  36. #36
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    There is no correlation between the number of models (builds) and how many months the work is split over. There could be only one model of vehicle and the work could be spread over 4 months. Or there could be 5 models with the work all done in one month.
    It seems like there's got to be a way to do an index of the whole area where the percentages are, a match to look for the correct row, then just return all non zeros in the entire row. Doing it as an array and increasing the column number seems like it would give you the next non zero in the row, spread that across far enough and you'd get them all no matter how many percentages there are.
    In my mind I'm picturing the same type of formula that's giving us the list of non zero quantities for each build (under the main table). Just instead of incrementing by row and extending the formula down, you increment by column and extend it to the right. Seems logical anyway, but I can't seem to make it happen.

  37. #37
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    There could be only one model of vehicle and the work could be spread over 4 months. Or there could be 5 models with the work all done in one month.
    Doing it as an array and increasing the column number seems like it would give you the next non zero in the row, spread that across far enough and you'd get them all no matter how many percentages there are.
    I still don't think I have clarified my question well enough.

    In my nightmares (LOL) I can already see 4 Builds with 3 percentages (as in the case of two Builds sharing one common percentage). Two of those 4 Builds would have to "share" a common percentage. Big problem. Do you see what I am talking about? In those cases which percentage(s) are assigned to which builds?
    Last edited by FlameRetired; 03-01-2016 at 03:16 PM. Reason: include additional quote

  38. #38
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    I guess I wasn't clear enough. No matter how many builds there are, they will all use whatever percentages are listed there. That's why they are all on the same row.

    So if a row has let's say 5 Builds and has 50% and 50%
    Then....Build 1 = 50% 50%
    Build 2 = 50% 50%
    Build 3 = 50% 50%
    Build 4 = 50% 50%
    Build 5 = 50% 50%

    If a row has 1 build and 3 percentages it would be
    Build 1 = 33.3% 33.3% 33.3%

    Or for your example, 4 builds and 3 percentages:
    Build 1 = 33.3% 33.3% 33.3%
    Build 2 = 33.3% 33.3% 33.3%
    Build 3 = 33.3% 33.3% 33.3%
    Build 4 = 33.3% 33.3% 33.3%

    And of course whatever date is above the percentage. You just read straight across the row, that's why it doesn't matter how many builds there are or how many percentages there are.
    All I really need is some kind of Index/Match combo that will match the row at the bottom of the sheet, let's say where it says Build 1. So the match will look for the row in the table that has that criteria, then return all the nonzeros to the right. Done as an array and extended out to the right to account for multiple percentages (or nonzeros). That's all, nothing more complicated than that. I think maybe you're reading more into this than there is.

  39. #39
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    Without a more complete data set I cannot be 100% certain how this will perform with more than 2 Builds or with more than 2 Builds w/ only one percentage/date.

    Again with helper in K82:K235
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    this array formula in Q81:X81 (relevant Build headers)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    this non-array formula in N82:N235
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    this non-array formula in O82:O235
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    this array entered helper formula P82:P235 (it is a necessary date counter)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    then the final array formula in Q82:X235
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The file is attached.

  40. #40
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    I just threw a few percentages in the table at random and it seems to work!! Yeah!! Thank you so much!
    I think to get the actual percentage numbers I'll just add a column in there before the dates with a simple formula of 100% divided by the date counter column, that way I don't have to actually pull down the numbers individually. That should work.
    I haven't thought about how to get it into the Schedule output sheet automatically yet, but I think matching the dates with an offset to put the number in should work and not be too complicated.

    Thank you again!

  41. #41
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    You're welcome.

  42. #42
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    Did find one small glitch.
    I think it's somewhere in this part of the formula that actually returns the dates:
    Please Login or Register  to view this content.
    It works fine if there are 2 or more dates to return, but for some reason if there is only 1 date it returns the same date twice instead of there being nothing in the second column.

    Any ideas why that would be?

  43. #43
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    That's because I somehow got the idea that is what you wanted, and you are quite correct about what the 'culprit' is.

    Try replacing that whole formula with this one.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  44. #44
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Index Match that returns all non zero values in row

    That works.
    I also got it to work with your original formula by changing the <= to just <

  45. #45
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Index Match that returns all non zero values in row

    That makes sense. Glad you found it.

+ 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. VBA to use evaluate with index match returns #VALUE! error
    By maym in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2015, 10:49 AM
  2. [SOLVED] Index+Match+Match doesnt returns right values
    By SwissExcel in forum Excel General
    Replies: 10
    Last Post: 07-21-2015, 08:39 AM
  3. Sum of multiple Index Match returns!?
    By Spicey_888 in forum Excel General
    Replies: 6
    Last Post: 04-25-2015, 05:30 AM
  4. [SOLVED] Index/Match returns #N/A result
    By pjkcpa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2014, 02:23 PM
  5. [SOLVED] INDEX MATCH function returns result #N/A
    By morrisondan in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-22-2013, 03:01 PM
  6. [SOLVED] MATCH/INDEX Formula Returns an Error Instead of 0
    By livifivil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2013, 04:18 PM
  7. Excel 2007 : Index Match returns 0
    By Martin Chamberlin in forum Excel General
    Replies: 6
    Last Post: 10-27-2011, 09:49 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