Using formulas to name non-unique rows and execute IF function

**bernborough** · 08-29-2010, 11:58 AM

Hi there,

I have written the following in relation to the attached spreadsheet (not VBA Code - If someone can convert it to VBA, then great.)

The structure is
a) Give a name to the instructions that follow under steps (b) to (e)
b) Find a row with particular cell contents and NAME it.
c) Find rows with particular cell contents that pass comparison rule#1 and NAME them.
d) Find rows with particular cell contents that pass comparison rule#2 and NAME them.
e) Use IF function based on result of Counting No. of rows from c) or d) to enter result
f) Loop instructions until type (b) rows no longer exist.

Three questions I have are:
1. What happens if there are no rows able to be named under step (b) which is the row type that everything else depends on? What instruction and where do I put it to get the macro move on to a completely new set of instructions?
2. Can a single row be able to be referred to by different names? ie. the name in step(c) and the name in step(d). I need this to be the case for my macro to work effectively.
3. If there are no rows found under (c), does the second part of the nested IF function from step(e) work? Is there a way of still allowing that second part of the nested IF function to work if the first part can't operate?

Macro is as follows:
NAME the instructions that follow as FIRSTFINALRATING

FIND the 1st row from the top
WHERE columnH=S & columnI=NB & columnJ=” ”[ie. blank]
NAME this row FirstNBRow

FIND the row(s) NOT FirstNBRow
WHERE (columnA, the row)=(columnA,FirstNBRow)
AND (columnB, the row)=(columnB.FirstBNRow)
AND (columnE, the row)=(columnE.FirstBNRow)
AND (columnI, the row)=”S”
AND (columnC, the row) < (columnC, FirstNBRow)
NAME this row(s) SlowFasterOT

FIND the row(s) NOT FirstNBRow
WHERE (columnA,the row)=(columnA,FirstNBRow)
AND (columnB,the row)=(columnB.FirstBNRow)
AND (columnH, the row)=”S”
AND (columnE, the row) => (columnF,FirstNBRow)
AND (columnD, the row) < (columnD, FirstNBRow)
NAME this row(s) SlowBandFasterST

FIND the row(s) NOT FirstNBRow
WHERE (columnA, the row)=(columnA,FirstNBRow)
AND (columnB, the row)=(columnB.FirstBNRow)
AND (columnH, the row)=”S”
AND (columnE, the row) => (columnF,FirstNBRow)
AND (columnC, the row) < (columnC, FirstNBRow)
NAME this row(s) SlowBandFasterOT

IF COUNTROWS(SlowFasterOT) >0,
AND
COUNTROWS(SlowBandFasterST) >1
THEN Select (columnJ,FirstNBRow) and enter “S”
OTHERWISE,
IF COUNTROWS(SlowBandFasterOT) >1
AND
COUNTROWS(SlowBandFasterOT) >1
THEN Select (columnJ,FirstNBRow) and enter “S”
OTHERWISE Select (columnJ,FirstNBRow) and enter “NB”

LOOP FIRSTFINALRATING UNTIL COUNTROWS(FirstNBRow) = 0

**Richard Buttrey** · 08-29-2010, 01:48 PM

Hi,

What you've provided is just a table of data. There are no named ranges and neither is it clear what you mean by Rule 1, Rule 2. Nor indeed what you mean by find a row with particular cell contents. What cell contents?

But before we go down the road of attempting to write a macro, can you explain in narrative form exactly what you are trying to achieve and the calculation process? It's quite possible that you don't need to resort to macros and standard Excel functionality may be sufficient.

We've got your table of data, now add the results you expect to see in presumably column J's Final rating, (or if more results are required add a new Results sheet) and populate it with the results you want. Annotate it with notes where it's not obvious how you've arrived at your result.

Regards

**bernborough** · 08-30-2010, 12:38 AM

Hi,

OK. To explain how that Final Rating is derived, I have made a note on cells J5 & J8 where the Initial Rating has changed from NB to S. J2 has a note as to why it has remained as NB for the Final Rating.
The comparison Rule#1 and Comparison Rule#2 that I refer to in the next paragraph are now identified in the code below a bit more clearly for you to understand.
Because the table in reality is going to contain thousands of rows of data covering different Dates/Tracks & the IF function used is going to be different depending on the row contents (ie. this is just the IF function for when Race Band is "S" and Initial Rating is "NB") a macro is required.

I would be very very thankful if someone can understand what I am trying to do & answer the 3 questions I have below.

The structure is
a) Give a name to the instructions that follow under steps (b) to (e)
b) Find a row with particular cell contents and NAME it.
c) Find rows with particular cell contents that pass comparison rule#1 and NAME them.
d) Find rows with particular cell contents that pass comparison rule#2 and NAME them.
e) Use IF function based on result of Counting No. of rows from c) or d) to enter result
f) Loop instructions until type (b) rows no longer exist.

Three questions I have are:
1. What happens if there are no rows able to be named under step (b) which is the row type that everything else depends on? What instruction and where do I put it to get the macro move on to a completely new set of instructions?
2. Can a single row be able to be referred to by different names? ie. the name in step(c) and the name in step(d). I need this to be the case for my macro to work effectively.
3. If there are no rows found under (c), does the second part of the nested IF function from step(e) work? Is there a way of still allowing that second part of the nested IF function to work if the first part can't operate?

MACRO is as follows:
NAME the instructions that follow as FIRSTFINALRATING

FIND the 1st row from the top
WHERE columnH=S & columnI=NB & columnJ=” ”[ie. blank]
NAME this row FirstNBRow

FIND the row(s) NOT FirstNBRow
WHERE (columnA, the row)=(columnA,FirstNBRow)
AND (columnB, the row)=(columnB.FirstBNRow)
AND (columnE, the row)=(columnE.FirstBNRow)
AND (columnI, the row)=”S”
AND (columnC, the row) < (columnC, FirstNBRow) ----COMPARISON RULE#1
NAME this row(s) SlowFasterOT

FIND the row(s) NOT FirstNBRow
WHERE (columnA,the row)=(columnA,FirstNBRow)
AND (columnB,the row)=(columnB.FirstBNRow)
AND (columnH, the row)=”S”
AND (columnE, the row) => (columnF,FirstNBRow) ------COMPARISON RULE#2
AND (columnD, the row) < (columnD, FirstNBRow) ------COMPARISON RULE#2
NAME this row(s) SlowBandFasterST

FIND the row(s) NOT FirstNBRow
WHERE (columnA, the row)=(columnA,FirstNBRow)
AND (columnB, the row)=(columnB.FirstBNRow)
AND (columnH, the row)=”S”
AND (columnE, the row) => (columnF,FirstNBRow)
AND (columnC, the row) < (columnC, FirstNBRow)
NAME this row(s) SlowBandFasterOT

IF COUNTROWS(SlowFasterOT) >0,
AND
COUNTROWS(SlowBandFasterST) >1
THEN Select (columnJ,FirstNBRow) and enter “S”
OTHERWISE,
IF COUNTROWS(SlowBandFasterOT) >1
AND
COUNTROWS(SlowBandFasterOT) >1
THEN Select (columnJ,FirstNBRow) and enter “S”
OTHERWISE Select (columnJ,FirstNBRow) and enter “NB”

LOOP FIRSTFINALRATING UNTIL COUNTROWS(FirstNBRow) = 0

**bernborough** · 08-31-2010, 09:58 AM

Hi,

OK. To explain how that Final Rating is derived, I have made a note on cells J5 & J8 where the Initial Rating has changed from NB to S. J2 has a note as to why it has remained as NB for the Final Rating.
The comparison Rule#1 and Comparison Rule#2 that I refer to in the next paragraph are now identified in the code below a bit more clearly for you to understand.
Because the table in reality is going to contain thousands of rows of data covering different Dates/Tracks & the IF function used is going to be different depending on the row contents (ie. this is just the IF function for when Race Band is "S" and Initial Rating is "NB") a macro is required.

I would be very very thankful if someone can understand what I am trying to do & answer the 3 questions I have below.

The structure is
a) Give a name to the instructions that follow under steps (b) to (e)
b) Find a row with particular cell contents and NAME it.
c) Find rows with particular cell contents that pass comparison rule#1 and NAME them.
d) Find rows with particular cell contents that pass comparison rule#2 and NAME them.
e) Use IF function based on result of Counting No. of rows from c) or d) to enter result
f) Loop instructions until type (b) rows no longer exist.

Three questions I have are:
1. What happens if there are no rows able to be named under step (b) which is the row type that everything else depends on? What instruction and where do I put it to get the macro move on to a completely new set of instructions?
2. Can a single row be able to be referred to by different names? ie. the name in step(c) and the name in step(d). I need this to be the case for my macro to work effectively.
3. If there are no rows found under (c), does the second part of the nested IF function from step(e) work? Is there a way of still allowing that second part of the nested IF function to work if the first part can't operate?

MACRO is as follows:
NAME the instructions that follow as FIRSTFINALRATING

FIND the 1st row from the top
WHERE columnH=S & columnI=NB & columnJ=” ”[ie. blank]
NAME this row FirstNBRow

FIND the row(s) NOT FirstNBRow
WHERE (columnA, the row)=(columnA,FirstNBRow)
AND (columnB, the row)=(columnB.FirstBNRow)
AND (columnE, the row)=(columnE.FirstBNRow)
AND (columnI, the row)=”S”
AND (columnC, the row) < (columnC, FirstNBRow) ----COMPARISON RULE#1
NAME this row(s) SlowFasterOT

FIND the row(s) NOT FirstNBRow
WHERE (columnA,the row)=(columnA,FirstNBRow)
AND (columnB,the row)=(columnB.FirstBNRow)
AND (columnH, the row)=”S”
AND (columnE, the row) => (columnF,FirstNBRow) ------COMPARISON RULE#2
AND (columnD, the row) < (columnD, FirstNBRow) ------COMPARISON RULE#2
NAME this row(s) SlowBandFasterST

FIND the row(s) NOT FirstNBRow
WHERE (columnA, the row)=(columnA,FirstNBRow)
AND (columnB, the row)=(columnB.FirstBNRow)
AND (columnH, the row)=”S”
AND (columnE, the row) => (columnF,FirstNBRow)
AND (columnC, the row) < (columnC, FirstNBRow)
NAME this row(s) SlowBandFasterOT

IF COUNTROWS(SlowFasterOT) >0,
AND
COUNTROWS(SlowBandFasterST) >1
THEN Select (columnJ,FirstNBRow) and enter “S”
OTHERWISE,
IF COUNTROWS(SlowBandFasterOT) >1
AND
COUNTROWS(SlowBandFasterOT) >1
THEN Select (columnJ,FirstNBRow) and enter “S”
OTHERWISE Select (columnJ,FirstNBRow) and enter “NB”

LOOP FIRSTFINALRATING UNTIL COUNTROWS(FirstNBRow) = 0