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

weilerc

New Member
Joined
Oct 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Formula:
=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


Excel.PNG
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

weilerc

New Member
Joined
Oct 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Maybe
Excel Formula:
=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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,711
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

New Member
Joined
Oct 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,148,006
Messages
5,744,315
Members
423,861
Latest member
Ka3EeM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top