VBA Macros For Upgrade Or Downgrade Status Based On Multiple Conditions

[hecgroups]

Hello Again,

I have posted my question HERE but did not get response.

I have 13 networks and 7 classes as mentioned below.

Networks: VIP+, VIP, AAA, AA, A, BBB, BB, B, C+, CCC, CC, R & Gold.
Classes: Class VIP+, Class VIP, Class A, Class B and Class C+

I need to categorized the status whenever any class or network change. I am looking for a VBA Macros which can give me the out put as shown HERE.

Below is the explanation with example:-

If any network moves from higher network to lower network then it should categorized as "Upgrade".
If any network moves from lower to higher network then it should categorized as "Downgrade"
Same condition will be implemented for classes also.

Ex 1: In column C I have status From Class A To Class B & From NW A To NW AA for the status will be Class Downgrade in column D, Network Downgrade in column E, Dash (-) in column F and Class Downgrade & Network Downgrade in column G

Ex 2: In column C I have status From Class A To Class C / Add To Rabia / From NW A To NW CC for the status will be Class Downgrade in column D, Network Downgrade in column E, Plan Upgrade in column F and Class Downgrade, Plan Upgrade & Network Downgrade in column G

[dflak]

I would be reluctant to take this task on because of the "free form" data entry of column C.

For the most part, it looks like you could use the ampersand (&) as a delimiter between networks and classes and the keyword "Class" to determine if you are dealing with a class or a network. However, there are exceptions.

For example: From Class A To Class C / Add To Rabia / From NW A To NW CC - What is Add to Rabia? Is it a class or a network? Should it be ignored?

Then later: From NW BB To NW CCC & Add To Rabia - the last time we saw Rabia, it was delimited by a slash, now it's delimited by an ampersand.

We need to know the full set of rules and if you deviate from them, you will get an erroneous answer.

What I do suggest is 4 columns:

• Change from Network
• Change to Network
• Change from Class
• Change to Class

Each of these can be subjected to data validation. It would save on trying to parse the strings and that would make it easier for the programmer, but that's secondary: parsing consistent data is what makes the project challenging. What it does assure is consistency of the data and also enforces correct spelling.

[hecgroups]

Thanks DFLAk for responding to my request.

I can be with you if it meets my requirement, but the data which is am getting is more than 500 records per week is as shown in the link as well as in attachment.

You can consider Rabia as a network because some time they give me as network as rabia and sometime as gold (But both are the same). Mostly I am getting as rabia.

But for my suggestion can we get for 3 columns as like Class Network & Plan (Rabia/Gold) which I have submitted in my first post as an attachment.

[JBeaucaire]

I would be reluctant to take this task on because of the "free form" data entry of column C.

I started to examine this project as well until I realized the same thing. Column C is being used to record information like humans think, data is not being denoted consistently as individual pieces of data. Before I would spend any time on this, I would require constraints be imposed on every entry in column C.

Daflak's suggestions are good. The writers of this data need conform to entry parameters. I would recommend comma delimiting.

Class A to Class C, NW Add to Rabia, NW A to NW CC
Class B To Class A, NW BBB To NW AA, NW AA To NW AAA, AAA NW To AA NW

[hecgroups]

Yes I am agree with you JBeaucaire.

I can manage the characters like "/" or "&" anything. This data is used by previous staff. can be used as comma (,).

[dflak]

Dealing with a consistent delimiter like a comma makes the task go a lot easier. I could deal with that - it still leaves an issue as to the free entry between the commas, but it does solve a big problem.

Realistically, I am off for the holidays. I will look in on this problem next week.

[JBeaucaire]

Man o man o man, I should've trusted my instincts and stayed away.

It's a monster but it handles most of the oddities we find in column C, though not all of them. The macro will mark the rows that didn't work with "XXXXX". Fix those rows, then run it again.

Note the new sheet LISTS, those hold the ordered lists I created, top down, the macro uses them to determine UP or DOWN movement.

(flop) 3.5 hrs later....

Please Login or Register  to view this content.

[hecgroups]

Thanks for all your efforts. but I cannot download the file

[JBeaucaire]

Try putting your Internet Explorer browser into Compatibility View mode.

Tools > Compatibility View Settings > Websites you've added: excelforum.com

I emailed you the file as well.

[hecgroups]

A million thanks for your help. You have saved my hours of manual work. I will thoroughly test it on my original data and if I have any problem I will comeback to you. Right now it is working on my sample data.

But 1 question the sheet which (Lists) you have make it should be their in my original workbook right. If not the macros will not work.

[JBeaucaire]

Yes, there are some Named Ranges on that sheet, too, so move the whole sheet to your real workbook.

[hecgroups]

After testing on my original data it is working fine.

Topic marked as solved and reputation added.