If statement check

Hello Royalmathmtcs,

Welcome to the Forum!

To get an accurate and quick solution to your problem, it would help to know how the data is laid out in the workbook. The easiest way to do this is to post your workbook for review. If you are using Excel 2007 then you should also post a copy in Excel 2003 format also.

Sincerely,
Leith Ross

Thanks for the reply Leith, I attached an example.

Hello Royalmathmtcs,

Will you be adding to the list in the future?

Sincerely,
Leith Ross

I posted an small example of what I have, as there are about 1000 entries. To answer your question, no I will not have to add any more entries to this list.

That looks like it might work. The sample you have provided outputs everything to the same column it is gathering the information from to check within the array, erasing data that needs to stay in that column. How do I move the output from this code into column N, starting at the same row as column M?

Nevermind about finding the correct column to output to, time to see if this works for the entire workbook!

I added a few more arrays and have the code working, but it doesnt add a comma to the end of the previous entry in the new column. It is overwriting anything in the column that should be kept. For example, if there is a scenerio where there is more than one match is found in the column the array is checking, it will output the match from the array further down in the code, rather than placing a comma behind the first entry and then adding the second match. What needs to be modified?

Hello Royalmathmtcs,

I have found you have duplicate entries for the following types:

UL 1863 Twice in ITE
UL 1450 Twice in Appliances
UL 1678 Twice in Consumer Electronics

These occur in both HVAC and Insulating Materials

UL 2353
UL 1692
UL 224
UL 1441
UL 1696
UL 969

What do you want to do with these?

Sincerely,
Leith Ross

Leith,

I might have misunderstood your question about adding to the list. The list of entries in the macro are not complete and I am currently adding more. As for the excel list, that is complete, except for what needs to be added under this macro. Thanks for pointing out those duplicates. It would be desirable for the macro to output both when there is a duplicate i.e if UL 2353 exists under HVAC and Insulating Materials, then then column N should be filled with HVAC, Insulating Materials.

I think that Krishnakumar's example is heading in the correct direction, but the problem it is having is that if column M contains an entry in a single cell that has UL 1863 under ITE, and UL 1450 under Appliances, it will only output Appliances in column N, instead of ITE, Appliances. I think this has something to do with the placement of the split fuction, but I am only speculating.

Hello Royalmathmtcs,

I have been using some other techniques to handle the data. Currently, I am employing a collection object to hold all the industry types, which alerted me to the duplication. It works well with the exception each entry must be unique.

The collection allows for a UDF (User Defined Function) to placed on the worksheet. It is called just like a regular worksheet function.

Please Login or Register  to view this content.

It returns the types in comma separated list in cell "N". I'll keep working on it.

Sincerely,
Leith Ross

Krishnakumar, I really appreciate your help!

I changed that segment that you posted, and now the code properly implements the comma that was missing before. I added more arrays and For Next statements and now I am running into the problem of not getting the correct output from the for next statement. I think this is because the way that you showed me only cosiders a scenerio with two arrays.

An example would be for UL 489. When the code looks for this it should see that its in the power distributions array, and output power distributions in the N column. Instead, the code is outputing ITE, Consumer Electronics, Power Distribution, Security & Signal in the N column. In one case I found with multiple entries in the M column, it didn't even output Power Distribution when UL 489 was present. Here is the code that I have now. I cut out most of the array section, as that has been entered correctly. What do I need to do from here?

Please Login or Register  to view this content.

Ok, I figured it out...The For Next statement for ITE needs to have the is else, ifempty check in it as well, not just for the sequential statements.

Please Login or Register  to view this content.

Thank you for the help, I couldn't have done this without you!!!!

Hello Royalmathmtcs,

This version loads each industry type from its own array into a collection object. The macro uses the collection to recall the industry type name for the standard. No duplicates are listed in column "N". If any standard has not been entered, you will see #Value! displayed in the cell. Have look and let me know if you have any questions.

Sincerely,
Leith Ross

This is working very well now, is there any way to modify it so that #Value! only shows up when there isn't a single match in the standards column? The way it is right now, #Value! shows even when there is a matching standard available along with a standard that hasn't been defined yet.

Hello Royalmathmtcs,

You are correct about the macro. Since it is a UDF, it is called just like a standard worksheet formula. If you have more than 100 Standards per Industry name then you will need to increase the Keys array value. I was thinking that it might be good to load the standards from a worksheet. It would be easier to maintain and allow you to easily search for a standard, or sort them.

Sincerely,
Leith Ross

Hello Royalmathmtcs,

I can change the macro not to error on a non existent standard. I left it that way as an alert to the user that something wasn't right. What would you like it do?

Sincerely,
Leith Ross

Hello Royalmathmtcs,

I could display the missing standards in a message box. Would that work?

Sincerely,
Leith Ross

Yes, that would be great, as long as it still displays the industry for the standards that have been defined, even if they are in the same cell as a non defined standard.

I really appreciate your time and patience...

Hello Royalmathmtcs,

I have added the message box to alert you which standards are missing. All other standards will have the industry to which they belong listed. The standards havae been removed from the body of the macro and placed on a separate worksheet. This will make it easier to update the standards and industries.

Sincerely,
Leith Ross