Jul 25 2021 11:30 AM - edited Jul 26 2021 04:46 AM
Excel Professionals,
I am having trouble solving a certain function using excel. My end goal is to due the following,
IF, any row in the A column has X then in row 2 A through Q shall have the following values.
This is a very simple explanation of the whole problem. To be more specific, I want this formula to scan all of the A column looking for a certain value. The rows that pertain to the value in column A I want it to output something different for each column in that row based on the value.
Exmaple,
Column A row 7, 32, and 56 all have the value CCZJV-001. Based on this value I need columns B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q to all output different information. Yes I know all the values that need to go into those columns. Having this as a feature would save me over 60 hours a month.
Thank you.
Jul 25 2021 02:54 PM
Do you have the spreadsheet in which you want this to work? Is it a spreadsheet you can share? Not an image please; the actual spreadsheet. Or, if the actual contains proprietary data, then a mockup that doesn't.
Your example and explanation are incomplete as things stand now. For example:
ETC. And I say "ETC" in bold because I suspect those questions only scratch the surface.
Jul 26 2021 04:45 AM
Jul 26 2021 05:22 AM
Thank you for posting the spreadsheet. It's helpful to be able to see what you're working with.
Now, I have to tell you too that your answers to my first set of questions still evoke more questions. In order to create the spreadsheet with all the functionality you are pointing toward we're going to have to get a LOT more precise and thorough. Again, these questions are just "for example" questions. If we were sitting down face to face, we'd need to be spending a LOT of time, I suspect, just spelling out the test or target conditions, how we need to recognize them, and then the consequences row-by-row and column-by-column.
For example:
You wrote: The target condition has to exist in one column and one column only, I need excel scan all of Column A for the target condition. If it see's the target in the cell then the consequences follow in the row that the target was identified in.
My original question, however, had been:
Does that target condition have to exist in those three rows and only those three rows in order for the consequences to follow? I understood that the conditions were in one column. That was not the question. So please spell out more whether the row in which the target conditions appears is significant, whether the number of times the target condition appears is significant (and, in each case, what the significance is; for example, is something more serious if it appears two times rather than one, more serious if it's four times, ETC; "ETC" meaning "spell this out fully". It's clear in your mind, no doubt, but if you want help that is truly helpful, you'll need to articulate these conditions thoroughly.)
You wrote: The consequences do take place in every row eventually, for now some are blank until I gather that information.
Each consequence is different for columns B-Q for the row that the original target was identified on for example. If target is found on A14, the B14 needs to say 'X' and C14 needs to say 'Y' and so on.
There will be different consequences for different targets for example if A5 is X and X=13, but A6 is Y and Y=12 then I need the rest of the row to be different based on what is in A.
I envision 1 condition for 1 waste stream I am looking at having around 20-25 waste streams.
And I can barely begin to ask the clarifying questions needed here. Especially given your last comment about envisioning 20 to 25 waste streams. Perhaps what you need to do is create a table that illustrates all of this. Excel has excellent tools for parsing tables of data, and that appears to be what's needed. Something that shows condition in A, consequences in B-Q, row by row....whatever. But you're going to need to spell them out.
We can't write specific Excel formulas based on general concepts. The specific condition:consequence connections need to be spelled out, in this case in detail. You might begin with an example or two, but those examples will need to be clear and complete. All you've done so far, even with the spreadsheet available, is pretty general (unless I'm missing something, which I suppose is possible).
Jul 26 2021 06:54 AM
Jul 26 2021 10:06 AM
Solution
FYI: The "3 condition condition", and my questions about it, came from your first post, where you gave this as your example:
Column A row 7, 32, and 56 all have the value CCZJV-001. Based on this value I need columns B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q to all output different information.
Anyway, based on what you're now saying, I will suggest that you work to rid yourself of your C++ background---one of the biggest hindrances to learning Excel can be a background in a programming language; it tempts you (I have experienced it, and seen it many times) to think in programming/procedural terms and overlook the power of Excel.'s built-in functions.
From how you're now describing things, I'm going to suggest that you look into VLOOKUP, XLOOKUP, the combination of INDEX and MATCH--any one of those methods being a possible solution. These are ways to access a table and extract information from it. What you've described sounds like an ideal application for one of those, and a LOT easier to write than a complex IF conditional.
To take your partial example from the most recent post, you could get CCZJV-003 from column A, and then use it as the reference to a table and get the values for columns B through Q from that table.
Here's a good link--there are also many YouTube videos--on VLOOKUP and how to use it. https://exceljet.net/excel-functions/excel-vlookup-function
Jul 26 2021 10:06 AM
Solution
FYI: The "3 condition condition", and my questions about it, came from your first post, where you gave this as your example:
Column A row 7, 32, and 56 all have the value CCZJV-001. Based on this value I need columns B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q to all output different information.
Anyway, based on what you're now saying, I will suggest that you work to rid yourself of your C++ background---one of the biggest hindrances to learning Excel can be a background in a programming language; it tempts you (I have experienced it, and seen it many times) to think in programming/procedural terms and overlook the power of Excel.'s built-in functions.
From how you're now describing things, I'm going to suggest that you look into VLOOKUP, XLOOKUP, the combination of INDEX and MATCH--any one of those methods being a possible solution. These are ways to access a table and extract information from it. What you've described sounds like an ideal application for one of those, and a LOT easier to write than a complex IF conditional.
To take your partial example from the most recent post, you could get CCZJV-003 from column A, and then use it as the reference to a table and get the values for columns B through Q from that table.
Here's a good link--there are also many YouTube videos--on VLOOKUP and how to use it. https://exceljet.net/excel-functions/excel-vlookup-function