[VBA Help] - Logic Based Values in Cells

[maddykhan]

Dear All,

Apologies for the novice level of skills i have for excel which is the reason im looking to this forum for help.

First i want a suggestion that should i use a macro or not for the below mentioned task.

Now the question is i have a database which looks like

Accounts</SPAN>	Jan-11</SPAN>	Feb-11</SPAN>	Mar-11</SPAN>	Apr-11</SPAN>	May-11</SPAN>	June-11</SPAN>	July-11</SPAN>	August-11</SPAN>
40011300</SPAN>	0</SPAN>	1</SPAN>	2</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>	0</SPAN>
40011313</SPAN>	8</SPAN>	8</SPAN>	8</SPAN>	0</SPAN>	1</SPAN>	2</SPAN>	3</SPAN>	0</SPAN>
40011326</SPAN>	0</SPAN>	1</SPAN>	1</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>	5</SPAN>	3</SPAN>
40011339</SPAN>	3</SPAN>	4</SPAN>	5</SPAN>	6</SPAN>	7</SPAN>	8</SPAN>	9</SPAN>	10</SPAN>
40011342</SPAN>	3</SPAN>	4</SPAN>	5</SPAN>	6</SPAN>	7</SPAN>	8</SPAN>	9</SPAN>	10</SPAN>
40011355</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
40011368</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
40011371</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
40011384</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
40011397</SPAN>	4</SPAN>	5</SPAN>	4</SPAN>	5</SPAN>	6</SPAN>	7</SPAN>	8</SPAN>	9</SPAN>
40011407</SPAN>	4</SPAN>	5</SPAN>	4</SPAN>	5</SPAN>	6</SPAN>	7</SPAN>	8</SPAN>	9</SPAN>
40011410</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>	2</SPAN>	0</SPAN>
40011423</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>
40011436</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>
40011449</SPAN>	0</SPAN>	3</SPAN>	4</SPAN>	3</SPAN>	4</SPAN>	3</SPAN>	3</SPAN>	1</SPAN>
40011452</SPAN>	0</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	0</SPAN>
40011465</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
40011478</SPAN>	5</SPAN>	4</SPAN>	5</SPAN>	6</SPAN>	6</SPAN>	4</SPAN>	3</SPAN>	4</SPAN>
40011481</SPAN>	5</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
40011494</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>
40011504</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>	1</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>	5</SPAN>
40011517</SPAN>	4</SPAN>	0</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

Now i want to calculate the status of these accounts on monthly basis by comparing it with the last month status.


If account number delinquency status increased then "FF"
If account number delinquency status decreased then "RB"
If account number delinquency status is same then "STB"

e.g. incase of Feb-11 it should be

Accounts</SPAN>	Jan-11</SPAN>	Feb-11</SPAN>
40011300</SPAN>		FF</SPAN>
40011313</SPAN>		STB</SPAN>
40011326</SPAN>		FF</SPAN>
40011339</SPAN>		FF</SPAN>
40011342</SPAN>		FF</SPAN>
40011355</SPAN>		STB</SPAN>
40011368</SPAN>		STB</SPAN>
40011371</SPAN>		STB</SPAN>
40011384</SPAN>		STB</SPAN>
40011397</SPAN>		FF</SPAN>
40011407</SPAN>		FF</SPAN>
40011410</SPAN>		STB</SPAN>
40011423</SPAN>		STB</SPAN>
40011436</SPAN>		STB</SPAN>
40011449</SPAN>		FF</SPAN>
40011452</SPAN>		FF</SPAN>
40011465</SPAN>		STB</SPAN>
40011478</SPAN>		RB</SPAN>
40011481</SPAN>		RB</SPAN>
40011494</SPAN>		STB</SPAN>
40011504</SPAN>		FF</SPAN>
40011517</SPAN>		RB</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

I have a huge amount of data which means thousands of accounts with years of monthly data.

I am doing it manually by putting in simple IF formula but i wish to make it automated in a vba.

 

[venkat1926]

see the sheet and formula in L2 which is copied down and to the right.

Sheet1

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Accounts	11-Jan	11-Feb	11-Mar	11-Apr	11-May	11-Jun	11-Jul	11-Aug	*	11-Jan	11-Feb	11-Mar	11-Apr	11-May	11-Jun	11-Jul	11-Aug
2	40011300	0	1	2	0	0	0	1	0	*	*	FF	FF	RB	STB	STB	FF	RB
3	40011313	8	8	8	0	1	2	3	0	*	*	STB	STB	RB	FF	FF	FF	RB
4	40011326	0	1	1	2	3	4	5	3	*	*	FF	STB	FF	FF	FF	FF	RB
5	40011339	3	4	5	6	7	8	9	10	*	*	FF	FF	FF	FF	FF	FF	FF
6	40011342	3	4	5	6	7	8	9	10	*	*	FF	FF	FF	FF	FF	FF	FF
7	40011355	0	0	0	0	0	0	0	0	*	*	STB	STB	STB	STB	STB	STB	STB
8	40011368	0	0	0	0	0	0	0	0	*	*	STB	STB	STB	STB	STB	STB	STB
9	40011371	0	0	0	0	0	0	0	0	*	*	STB	STB	STB	STB	STB	STB	STB
10	40011384	0	0	0	0	0	0	0	0	*	*	STB	STB	STB	STB	STB	STB	STB
11	40011397	4	5	4	5	6	7	8	9	*	*	FF	RB	FF	FF	FF	FF	FF
12	40011407	4	5	4	5	6	7	8	9	*	*	FF	RB	FF	FF	FF	FF	FF
13	40011410	0	0	0	0	0	1	2	0	*	*	STB	STB	STB	STB	FF	FF	RB
14	40011423	0	0	0	0	0	1	1	1	*	*	STB	STB	STB	STB	FF	STB	STB
15	40011436	0	0	0	0	0	1	1	1	*	*	STB	STB	STB	STB	FF	STB	STB
16	40011449	0	3	4	3	4	3	3	1	*	*	FF	FF	RB	FF	RB	STB	RB
17	40011452	0	1	0	1	1	1	0	0	*	*	FF	RB	FF	STB	STB	RB	STB
18	40011465	0	0	0	0	0	0	0	0	*	*	STB	STB	STB	STB	STB	STB	STB
19	40011478	5	4	5	6	6	4	3	4	*	*	RB	FF	FF	STB	RB	RB	FF
20	40011481	5	0	0	0	0	0	0	0	*	*	RB	STB	STB	STB	STB	STB	STB
21	40011494	1	1	1	0	1	1	0	1	*	*	STB	STB	RB	FF	STB	RB	FF
22	40011504	2	3	4	1	2	3	4	5	*	*	FF	FF	RB	FF	FF	FF	FF
23	40011517	4	0	1	0	1	1	0	0	*	*	RB	FF	RB	FF	STB	RB	STB

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula L2 =IF(C2>B2,"FF",IF<span style=" color:008000; ">(C2<b2,"rb",if<span style=" color:#0000ff; ">(C2=B2,"STB")</b2,"rb",if)) <tbody> </tbody>

<tbody>
</tbody>

 

[maddykhan]

Thank you so much Venkat1926... Appreciate your help...

My mistake that maybe i didnt explain my question properly...

Actually i am doing kind of same thing currently by using IF statement in each cell...

But the problem arise when i have more scenarios than just FF,RB,STB...

like,

If its a blank after having any of above codes, account is closed "CL"

and if a account is blank and first time having a delinquency indicator like 1,2,3, ... account is New "New"

Is there anyway i can use a nested IF loop by a macro or vba or something...?



Thank you for your help!

 

[maddykhan]

Just for an example...

Following scenarios may occur..

Option	Logic </SPAN>	Result </SPAN>
1</SPAN>	B2</SPAN>	=</SPAN>	Blank</SPAN>	Option 2</SPAN>
2</SPAN>	C2</SPAN>	=</SPAN>	Blank</SPAN>	Blank</SPAN>
3</SPAN>	C2</SPAN>	<></SPAN>	Blank</SPAN>	New</SPAN>
4</SPAN>	C2</SPAN>	=</SPAN>	B2</SPAN>	STB</SPAN>
5</SPAN>	C2</SPAN>	></SPAN>	B2</SPAN>	FF</SPAN>
6</SPAN>	C2</SPAN>	<</SPAN>	B2</SPAN>	RB</SPAN>
7</SPAN>	B2</SPAN>	<></SPAN>	0</SPAN>	Option 8</SPAN>
8</SPAN>	C2</SPAN>	=</SPAN>	0</SPAN>	NRM</SPAN>
9</SPAN>	B2</SPAN>	<></SPAN>	Blank</SPAN>	Option 10</SPAN>
10</SPAN>	C2</SPAN>	=</SPAN>	Blank</SPAN>	CLS</SPAN>

<TBODY>
</TBODY>

 

[venkat1926]

what is the difference between <>0 and <>blank

 

[Peter_SSs]

@Venkat

When using Excel jeanie, please use the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.

Post #2 actually only contained one formula, to be copied across and down as you described so you really only needed to show that one in L2.

You will see that I have removed the excess formulas in this case.

As it happens, your formula did not post correctly anyway so if it is still relevant, you may want to post it again, noting the information provided here.

 

[Peter_SSs]

@maddykhan

Welcome to the MrExcel board!

Could we have another small set of dummy sample data and expected results that contains examples of all the possible situations?
If you want to post a screen shot that includes row/column information (like Venkat's) then see my signature block for suggestions.

 

[venkat1926]

Peter
thank u. shall keep this in mind

 

[Peter_SSs]

Peter
thank u. shall keep this in mind

No problem. Two other jeanie suggestions that you may consider.

1. Displaying gridlines makes it much clearer what cell an entry is in. For example, looking at cell H11 in your earlier screen shot, it isn't immediately clear to me whether it contains 7 or 8.

2. If you preview or edit your post, it is a good idea to re-paste the jeanie code (it is usually still on your clipboard) over the top of what you see in your reply window. Doing that will stop those asterisks appearing where empty cells should be.

 

[venkat1926]

Peter
Thak you for suggestios. one learans everyday a new thing. that is the attraction of learning