My code works, but there has to be a better way

[weilerc]

Hi Everyone,

I find myself to be pretty good with excel, efficient maybe not, but I can get by without issue.

To give a little background, I work for a cabinet manufacturer that works on large condo and home developments.
We receive terribly formatted spreadsheets from the builders including hundreds, sometimes thousand of rows each containing 1 unit, and an assortment of customer selections beside it. In this one I am currently working on, there are 55 columns to the right of the unit number, all have some impact on the product I provide.
These spreadsheets have no universal formatting, and to protect our company I can make NO changes to the initial data. This poses an issue, as for example we run into this issue frequently:

CELL B1: No
CELL B2: No thanks
CELL B3:
CELL B4: Yes

I need to use an assortment of formulas to correct this data onto a new sheet so I can organize it for my shop.

Now, I have this spreadsheet working perfectly, there is no issue to it whatsoever, it does exactly what it is that I need which is:

1. Drop down list activates proper named range and fills values into table above. This provides all units of a specified floor. This named range has multiple upgrades, finishes and styles which I need to count.
2. I then need to count the quantity of each component in each individual colour. These components are based off a list of all possible variations, and automatically populates based off of a +1 or _+0 based on the information above.

AND NOW FOR THE ABOMINATION OF CODE IN JUST THE HIGHLIGHTED CELL OF THE IMAGE, ALL OTHER GREEN CELLS HAVE EQUALLY RIDICULOUS FORMULAS IN DIFFERENT VARIATIONS:

=IF(ISERROR(VLOOKUP($BX42,$BX$11,1,FALSE)),0,IF($BA$11="Y",1,IF($BA$11="Ye",0,IF($BA$11="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$12,1,FALSE)),0,IF($BA$12="Y",1,IF($BA$12="Ye",0,IF($BA$12="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$13,1,FALSE)),0,IF($BA$13="Y",1,IF($BA$13="Ye",0,IF($BA$13="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$14,1,FALSE)),0,IF($BA$14="Y",1,IF($BA$14="Ye",0,IF($BA$14="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$15,1,FALSE)),0,IF($BA$15="Y",1,IF($BA$15="Ye",0,IF($BA$15="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$16,1,FALSE)),0,IF($BA$16="Y",1,IF($BA$16="Ye",0,IF($BA$16="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$17,1,FALSE)),0,IF($BA$17="Y",1,IF($BA$17="Ye",0,IF($BA$17="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$18,1,FALSE)),0,IF($BA$18="Y",1,IF($BA$18="Ye",0,IF($BA$18="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$19,1,FALSE)),0,IF($BA$19="Y",1,IF($BA$19="Ye",0,IF($BA$19="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$20,1,FALSE)),0,IF($BA$20="Y",1,IF($BA$20="Ye",0,IF($BA$20="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$21,1,FALSE)),0,IF($BA$21="Y",1,IF($BA$21="Ye",0,IF($BA$21="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$22,1,FALSE)),0,IF($BA$22="Y",1,IF($BA$22="Ye",0,IF($BA$22="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$23,1,FALSE)),0,IF($BA$23="Y",1,IF($BA$23="Ye",0,IF($BA$23="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$24,1,FALSE)),0,IF($BA$24="Y",1,IF($BA$24="Ye",0,IF($BA$24="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$25,1,FALSE)),0,IF($BA$25="Y",1,IF($BA$25="Ye",0,IF($BA$25="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$26,1,FALSE)),0,IF($BA$26="Y",1,IF($BA$26="Ye",0,IF($BA$26="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$27,1,FALSE)),0,IF($BA$27="Y",1,IF($BA$27="Ye",0,IF($BA$27="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$28,1,FALSE)),0,IF($BA$28="Y",1,IF($BA$28="Ye",0,IF($BA$28="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$29,1,FALSE)),0,IF($BA$29="Y",1,IF($BA$29="Ye",0,IF($BA$29="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$30,1,FALSE)),0,IF($BA$30="Y",1,IF($BA$30="Ye",0,IF($BA$30="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$31,1,FALSE)),0,IF($BA$31="Y",1,IF($BA$31="Ye",0,IF($BA$31="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$32,1,FALSE)),0,IF($BA$32="Y",1,IF($BA$32="Ye",0,IF($BA$32="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$33,1,FALSE)),0,IF($BA$33="Y",1,IF($BA$33="Ye",0,IF($BA$33="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$34,1,FALSE)),0,IF($BA$34="Y",1,IF($BA$34="Ye",0,IF($BA$34="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$35,1,FALSE)),0,IF($BA$36="Y",1,IF($BA$35="Ye",0,IF($BA$35="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$36,1,FALSE)),0,IF($BA$36="Y",1,IF($BA$36="Ye",0,IF($BA$36="N",0,0))))
+IF(ISERROR(VLOOKUP($BX42,$BX$37,1,FALSE)),0,IF($BA$37="Y",1,IF($BA$37="Ye",0,IF($BA$37="N",0,0))))

Basically it determines if the cell contains Y, Ye or N and adds 1 or 0 to achieve what I need.
I really do not know any other way that this could possibly be explained without showing the actual excel sheet which is full of macros, 100+ images and 25 tabs.

I just cannot accept that the code I have created below is the most efficient way to deal with this, but it works.

Is there any way that all I have above written into 1 line by applying the exact same formula to a range and gathering the sum of that formula?

Thanks for any help.


Chris

 

[Tetra201]

Maybe

=COUNTIFS($BX$11:$BX$37,$BX42,$BA$11:$BA$37,"Y")

 

[weilerc]

Maybe

=COUNTIFS($BX$11:$BX$37,$BX42,$BA$11:$BA$37,"Y")

Thanks for the reply.

Unfortunately, without the code actively adding +1 or +0 based on the row it is not effective for me, in this cell specifically, Y=1, Ye=0 and N=0

This is why this has been a struggle for me to figure out, I cannot think of a way to run that same calculation (+1 or +0) across the "Unit Number" column array and return the sum of the whole array without writing as per above


Thanks again,

Chris

 

[Tetra201]

A couple of questions:
1) Have you tried using the suggested formula and comparing the results with those returned by your code?
2) Does your formula have a typo in the middle of the third row from the bottom ($BA$36="Y")?

 

[weilerc]

A couple of questions:
1) Have you tried using the suggested formula and comparing the results with those returned by your code?
2) Does your formula have a typo in the middle of the third row from the bottom ($BA$36="Y")?

My apologies, I just rechecked and was able to get it to work. It was conflicting with a macro in the cell I tried it in initially however it did work properly in the right place. As for the mistake, wow do you have an attention to detail! I originally had this essentially written as 1 line, and never really looked again once I broke it down to upload it, maybe I should write stuff that way from now on.

Thanks again for the help!


Chris

 

[Tetra201]

You are most welcome.