Replace a formula with numerous nested "IF" statements

**vtentarelli** · 02-15-2017, 01:47 AM

I have developed a formula using approximately 7 nested "IF" statements, each with the functions "CONCATENATE" "VLOOKUP," "LEFT," "RIGHT," "MID." However I was wondering if there were a shorter, simpler, more elegant approach. I don't know if it is appropriate to use this forum for help to suggest a better way to approach a problem that is effectively solved, but I was hoping someone would tackle this issue.

Application: collect water samples around the plant, and code them with a descriptive code. The code is an alpha-numeric code containing either 16 characters (including hyphens separating subcodes) or 18 characters. Example of a 16-character code: PW-021307-01-PLP; example of an 18-character code: MW-PJ020120-01-GRV.
Explanation: Prefix (PW/MW) represents the water system. PJ020120 represents the batch number of the product using the water; 021417 represents the date the sample was collected; PLP / GRV represent the source from which the sample was collected. Results of tests for all water systems are entered into a log, recording the date, sample code, and results of the tests (a numeric value). There are linked spreadsheets that capture the data from the main log specific to the year, water system and source. A statistical analysis is performed on this data to generate trending numbers.

The problem: Only certain 3-letter suffixes are appropriate for the specific water system. For example "GRV" is exclusive to MW; "PLP" is exclusive to PW. There are approximately 6 to 8 suffixes for each water system that are exclusive. The VLOOKUP function gathers the text description from the 3-character code. I want to develop a code on the main page to generate an error message if the analyst enters the wrong code. For example if the analyst enters the code "PW-021417-01-GRV" an error message should appear in the column notifying the analyst to correct the entry. If a wrong code is entered, the individual spreadsheets cannot capture the data.

There are 3 water systems each with approximately 6 unique sources. See attached for the formulas. There are approximately 1000 rows in the main page each with a sample code and data.

**FDibbins** · 02-15-2017, 01:54 AM

We always welcome any questions regarding excel, but I would ask why you upload a word file, when it seems your question is excel related?

**FDibbins** · 02-15-2017, 02:04 AM

hmm OK , I just opened your word doc and tried to look through that formula. Without seeing what you are referencing, it is hard to make out what is doing what, but there is some definite repetitions that could be removed. If you use line-feeds in your formula, it breaks down to this (and shows the duplicates well)...
=IF(AND(R3=18,LEFT(C3,2)="MW",MID(C3,13,2)="01"),
CONCATENATE(VLOOKUP(LEFT(C3,2),'Source Codes'!$C$4:$D$4,2,0)," ",VLOOKUP(MID(C3,13,2),'Source Codes'!$C$10:$D$10,2,0)," ",VLOOKUP(RIGHT(C3,3),'Source Codes'!$G$3:$H$7,2,0)),IF(AND(R3=16,LEFT(C3,2)="MW",MID(C3,11,2)<>"01"),
CONCATENATE(VLOOKUP(LEFT(C3,2),'Source Codes'!$C$4:$D$4,2,0)," ",VLOOKUP(MID(C3,11,2),'Source Codes'!$C$11:$D$14,2,0)," ",VLOOKUP(RIGHT(C3,3),'Source Codes'!$G$6:$H$15,2,0)),IF(AND(LEFT(C3,2)="PW",MID(C3,11,2)="01"),
CONCATENATE(VLOOKUP(LEFT(C3,2),'Source Codes'!$C$5:$D$5,2,0)," ",VLOOKUP(MID(C3,11,2),'Source Codes'!$C$10:$D$10,2,0)," ",VLOOKUP(RIGHT(C3,3),'Source Codes'!$G$16:$H$19,2,0)),IF(LEFT(C3,2)="LW",
CONCATENATE(VLOOKUP(LEFT(C3,2),'Source Codes'!$C$6:$D$6,2,0)," ",VLOOKUP(MID(C3,11,2),'Source Codes'!$C$11:$D$14,2,0)," ",VLOOKUP(RIGHT(C3,3),'Source Codes'!$G$22:$H$25,2,0)),
CONCATENATE(VLOOKUP(LEFT(C3,2),'Source Codes'!$C$4:$D$6,2,0)," ",VLOOKUP(MID(C3,11,2),'Source Codes'!$C$9:$D$14,2,0)," ",VLOOKUP(RIGHT(C3,3),'Source Codes'!$G$3:$H$25,2,0))))))

the bolded part looks common, and it may help if you added some helper columns to do the heavy lifting with the left/mid etc, you have a LOT of repetition there too

**vtentarelli** · 02-15-2017, 04:18 AM

I thought the size of the spreadsheet would be too large to upload, so I just copied the formula onto a Word doc for convenience. I have an abbreviated version of the Main Page with the formulas, and the Source Codes sheet as a reference, but I cannot see how to upload the file on a reply to a thread. Any suggestions?

**AliGW** · 02-15-2017, 04:20 AM

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**vtentarelli** · 02-15-2017, 04:24 AM

I thought that the spreadsheet would be too large to upload, so I copied the pertinent formula onto a Word doc for convenience. Attached is an abbreviated version of the Main page with formulas and the Source Codes Sheet for reference.
I hope this helps.

**vtentarelli** · 02-15-2017, 04:27 AM

I thought the spreadsheet would be too large to upload so I copied the pertinent formula onto a Word doc for convenience. I have created and uploaded an abbreviated version of the Main Page along with the Source Codes sheet for reference. I hope this helps.

**vtentarelli** · 02-15-2017, 03:45 PM

I thought the spreadsheet would be too large to upload so I copied the pertinent formula onto a Word doc for convenience. I have created and uploaded an abbreviated version of the Main Page along with the Source Codes sheet for reference. I hope this helps.

This may be a duplicate thread because of the message I received from forum moderator about a glitch in uploading Excel files. I tried to follow her instructions to upload the Excel file. Sorry for the confusion and inconvenience.

**AliGW** · 02-15-2017, 07:05 PM

You have uploaded your file now. All that is now needed is a little patience.

**FDibbins** · 02-15-2017, 08:00 PM

Thanks for the feedback

Based on your comment...

Your comment made me re-examine the formula. The only important elements were the prefix (water system) and the valid 3-code suffix. I trimmed down the formula to about 1/4 the lines and it works great.

do you still have a problem with this?

**vtentarelli** · 02-21-2017, 12:47 PM

I marked the thread as solved based upon your comments. Thanks

Replace a formula with numerous nested "IF" statements

LinkBack

Thread Tools

Rate This Thread

Display

Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Re: Replace a formula with numerous nested "IF" statements

Thread Information

Users Browsing this Thread

Similar Threads

"Large" Function w. Nested If Statements Error

[SOLVED] Validation Box error with "over the limit" Nested IF Statements

Need some help with "nested if statements function" & "classifiying the group category"

7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

[SOLVED] CPearson's "Assign Name" Workaround Solution for Too Many Nested IF Statements Not Working

How to replace numerous cells at 1 time to "a" within a specified area?

Excel 2007 : Nested "IF" statements to calculate marginal tax rates

Bookmarks

Bookmarks

Posting Permissions