SOLVED

Giant =IF statement

Copper Contributor

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.

5 Replies

@EnvironmentalScientist 

 

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:

  • Does that target condition have to exist in those three rows and only those three rows in order for the consequences to follow?
  • Do the consequences take place in every row of columns B through Q?
  • Are they different consequences in the various rows of columns B-Q?
  • Or are the consequences only in the rows corresponding to those target conditions?
  •  
  • What about other condition in various rows of column A? How may different conditions do you envision?

 

ETC. And I say "ETC" in bold because I suspect those questions only scratch the surface.

@mathetes

Yes I do have a spreadsheet in which I want this to work, I will have to figure out how to share it.

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.

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.

@EnvironmentalScientist 

 

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

@mathetes

I am having to start over typing this due to internet explore crashing.

First thing is, you are a very smart individual. Much smarter than I; the questions you ask are a little over my head but I am trying to respond appropriately.

To give you a general idea, this is a database I am creating that I will copy into multiple documents. Each document will access different information separately. For now I only want to work on the single document in use.

The main benefit in this document is actually in the 'Shipment Tracking Log'. This gets updated every time something is shipped out (Daily). I want to be able to type in the Waste Profile Number and that's it. Everything else is populated.

To answer your first question no. It should only depend on 1 row. I'm not sure I understand where the 3 rows is coming from.

Yes I will have profiles repeated multiple times on the document. The target could appear in 1 row or 50 rows. It does not matter as long as the row has the proper information. For example,
The day is March 15th of 2017, I shipped off CCZJV-003, I type this in, in L48. Now I need F48 to reference the database and tell me exactly what the waste is, I want it to reference todays date in J48, I want it to reference the Transportation Company Name in N48, and so on. The next day the same waste is shipped again, now I want to input the waste profile in L49, the only thing I want to change is the date shipped.

I see it working as a nested if statement (I come form a C# background)

if ( x == CCZJV-001)
{
console.WriteLine(A14;"insert information here")
}
if x==CCZJV-002

and so on and on. This way just does not seem optimal to me, and I cannot get the code right in excel.

I.E.

=IF('Waste Profile Log'!A:Q=CCZJV-1,'Waste Profile Log'!A:Q=Sprint Waste, Error)
best response confirmed by EnvironmentalScientist (Copper Contributor)
Solution

@EnvironmentalScientist 

 

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

 

 

 

1 best response

Accepted Solutions
best response confirmed by EnvironmentalScientist (Copper Contributor)
Solution

@EnvironmentalScientist 

 

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

 

 

 

View solution in original post