Extracting data on the basis of multiple conditions

[Rahul87]

Hi Experts, once again thank you very much for helping me in achieving my tasks. Once again I posting here to get another tasks to be done. Please have a look on below requirements and if possible then please help me out.

Any help would be highly appreciated.

In my sheet there are eight columns A, B, C, D, E, F, G, H with heading as Trading Symbol, LTP, ATP, Recorded ATP, Rate, TSL, Reconsideration, and Rank as below.

I want to fetch out the number of data (only Trading symbols) from column A as mentioned by the user in integer value in Cell V5 as (1,2,3.......) for positioning of data in vertical order and also to provide position or rank as 1, 2, 3.... and so on vertically from cell U18,V18.... for ranking or serial number or for positioning as below, and similarly U19, V19.... for trading symbol.

1	2	3	4
BHEL23APRFUT	CHAMBLFERT23APRFUT	BAJAJFINSV23APRFUT	HDFC23APRFUT

on the basis of below criteria, which will be defined by the user only in other columns/cell as like below snap:-

data should be fetched out and pasted horizontally from Cell U19, V19.........
if percentage value in column E (Rate) is greater than value of Cell V8
and if column B (LTP) value is greater than column G (Reconsideration) And column H (Rank) value is less than or equals to cell value of V5
and if any of the position value in vertical order is vacant then only it should fill the vacant position at last and it should replace or move the existing trading symbol above only when the rank in column H is greater than the out of rank value of Cell V9 and if Value of column B is less than value of column F.

Below is the code, which I am was able to search and build with help of internet, but unable to get it done.

Please help me out in achieving the task. if not understood then please post, I will make it more clear.

Private Sub Worksheet_Change(ByVal Target As Range)
StartTimer

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim dataRange As Range
Set dataRange = Range("A2:H" & Cells(Rows.Count, "H").End(xlUp).Row)

If Target.Address = "$V$5" Then
    'Check if the entered value is an integer and not empty and not zero
    If IsNumeric(Target.Value) And Target.Value <> "" And Target.Value <> 0 And Int(Target.Value) = Target.Value Then
        'Get the threshold, consideration, maxRank, and position values from the worksheet
        Dim threshold As Double
        threshold = Range("V8").Value
        Dim consideration As Double
        consideration = Range("G1").Value
        Dim maxRank As Long
        maxRank = Range("V5").Value
        Dim outOfRank As Long
        outOfRank = Range("V9").Value
        Dim position As Long
        position = Range("V5").Value
        
        'Initialize the row counter for writing data to the output range
        Dim outputRow As Long
        outputRow = 19
        
        'Initialize the serial number
        Dim serialNumber As Long
        serialNumber = 1
        
        'Loop through the rows in the data range
        Dim i As Long
        For i = 1 To dataRange.Rows.Count
            'Check if the percentage value in the E column is greater than the threshold
            If dataRange(i, 5).Value > threshold Then
                'Check if the LTP column value is greater than the consideration value
                If dataRange(i, 2).Value > consideration Then
                    'Check if the Rank column value is less than or equal to the maxRank value
                    If dataRange(i, 8).Value <= maxRank Then
                        'Check if the position value in the vertical order is vacant
                        If Range("V" & (position + 18)).Value = "" Then
                            'Write the serial number and Trading Symbol horizontally to the output range starting at V19
                            Range("U" & outputRow).Value = serialNumber
                            Range("V" & outputRow).Value = dataRange(i, 1).Value
                            'Increment the row and serial number
                            outputRow = outputRow + 1
                            serialNumber = serialNumber + 1
                        'If the position value in the vertical order is not vacant, check if the rank is greater than the outOfRank value and if the LTP is less than TSL
                        ElseIf dataRange(i, 8).Value > outOfRank And dataRange(i, 2).Value < dataRange(i, 6).Value Then
                            'Find the next vacant position in the vertical order
                            Do While Range("V" & (position + 18)).Value <> ""
                                position = position + 1
                            Loop
                            'Write the serial number and Trading Symbol horizontally to the vacant position in the vertical order
                            Range("U" & (position + 18)).Value = serialNumber
                            Range("V" & (position + 18)).Value = dataRange(i, 1).Value
                            'Increment the serial number
                            serialNumber = serialNumber + 1
                        End If
                    End If
                End If
            End If
        Next i
    End If
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Rahul87]

Restated Question 1. Does my code seem like the code that you provided in the workbook link?

Yes Jim thank you very much, it is highly appreciated.

 

[Rahul87]

I apologize but we have a language problem. I'm trying to understand.

The code that I provided only runs when user changes cell V5.

    If Target.Address = "$V$5" Then

BUT, your code does not work. Mine does.

Question 1: Does my code looks seem like code that you were trying to write before?

Question 2: Is it that code -- the worksheet change event code -- that is supposed to do what is needed (if user changes cell V5)?

Dear Jim, please accept my apology, actually the problem was there in my excel application only, so when I re-installed then your code started to work. Thanks a log it is highly appreciated.

 

[Rahul87]

For Long Rank I tried to find rows in your data that meet your selection criteria. Of the data that you provided are their any rows that meet all of your selection critreia?

Does what I have below seem like I understand which Long Rank rows to select?

'Include data for for long rank
'If Rate percentage (in column E) of a stock is greater than Threshold value of Cell (V8)
'And
'If LTP (in column B) value is greater than reconsideration (in column G)
'And
'IF Rank (in Column H) is less than or equal to cell value of V5.

            If dRatePercent > rThresholdCell.Value _
               And dLTP > dReconsideration _
               And iRank <= rLongRankCell.Value _
             Then
                iFoundCount = iFoundCount + 1

            End If

Dear Jim, Thanks once again, can you please suggest where should I include this above code.

And apart from that can you please assist me in writing the above code totally opposite for the below ranges, which you can see in my excel workbook which I shared for short Rank. Please refer the shared workbook on same link Link to download the new file.

Anyhow I tried to do so, but not able to achieve, cause I know I have done totally wrong, so please do let me know how can achieve this for both. Below is the code.

Private Sub Worksheet_Change1(ByVal Target As Range)
    'StartTimer

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim rDataRange1 As Range
   
    Dim dThreshold1 As Double
   
    Dim vConsideration1 As Variant
   
    Dim iMaxRank1 As Long
   
    Dim iOutOfRank1 As Long
   
    Dim iPosition1 As Long

    Dim iOutputRow1 As Long
   
    Dim iSerialNumber1 As Long
   
    Dim iOuterLoopIndex1 As Long
   
    Dim iInnerLoopIndex1 As Long
           
    Set rDataRange1 = Range("K2:R" & Cells(Rows.Count, "R").End(xlUp).Row)

    If Target.Address = "$V$11" Then
   
'       Check if the entered value is an integer and not empty and not zero
        If IsNumeric(Target.Value) And Target.Value <> "" And Target.Value <> 0 And Int(Target.Value) = Target.Value Then

'           Get the threshold, consideration, iMaxRank1, and iPosition1 values from the worksheet
            dThreshold1 = Range("V14").Value
           
            vConsideration1 = Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).Value
           
            iMaxRank1 = Range("V11").Value '>= same as iPosition1?
           
            iOutOfRank1 = Range("V15").Value
           
            iPosition1 = Range("V11").Value  '>= same as iMaxRank?
           
'           Initialize the row counter for writing data to the output range
            iOutputRow1 = 19
           
'           Initialize the serial number
            iSerialNumber1 = 1
           
'           Loop through the rows in the data range
            For iOuterLoopIndex1 = 1 To rDataRange1.Rows.Count
           
'               Check if the percentage value in the O column is smaller than the threshold
                If rDataRange1(iOuterLoopIndex1, 5).Value < dThreshold1 Then

'                   Check if the LTP column value is smaller than the consideration value
                    For iInnerLoopIndex1 = 1 To UBound(vConsideration1)
                   
                        If rDataRange1(iOuterLoopIndex1, 2).Value < vConsideration1(iInnerLoopIndex1, 1) Then
                       
'                           Check if the Rank column value is less than or equal to the iMaxRank1 value
                            If rDataRange1(iOuterLoopIndex1, 8).Value >= iMaxRank1 Then

'                               Check if the position value in the vertical order is vacant
                                If Range("Y" & (iPosition1 + 18)).Value = "" Then
                               
'                                   Write the serial number and Trading Symbol horizontally to the output range starting at V19
                                    Range("X" & iOutputRow1).Value = iSerialNumber1
                                    Range("Y" & iOutputRow1).Value = rDataRange1(iOuterLoopIndex1, 1).Value

'                                   Increment the row and serial number
                                    iOutputRow1 = iOutputRow1 + 1
                                    iSerialNumber1 = iSerialNumber1 + 1

'                               If the position value in the vertical order is not vacant, check if the rank is smaller than the iOutOfRank value and if the LTP is less than TSL
                                ElseIf rDataRange1(iOuterLoopIndex1, 8).Value < iOutOfRank1 And rDataRange1(iOuterLoopIndex1, 2).Value > rDataRange1(iOuterLoopIndex1, 6).Value Then

'                                   Find the next vacant position in the vertical order
                                    Do While Range("Y" & (iPosition + 18)).Value <> ""
                                        iPosition1 = iPosition1 + 1
                                    Loop

'                                   Write the serial number and Trading Symbol horizontally to the vacant position in the vertical order
                                    Range("X" & (iPosition1 + 18)).Value = iSerialNumber1
                                    Range("Y" & (iPosition1 + 18)).Value = rDataRange1(iOuterLoopIndex1, 1).Value

'                                   Increment the serial number
                                    iSerialNumber1 = iSerialNumber1 + 1
                               
                                End If 'rDataRange1(iOuterLoopIndex1, 8).Value >= iMaxRank
                           
                            End If 'rDataRange1(iOuterLoopIndex1, 2).Value < vConsideration(iInnerLoopIndex1, 1)
                       
                        End If 'rDataRange1(iOuterLoopIndex1, 2).Value > vConsideration1(iInnerLoopIndex1, 1)
                   
                    Next iInnerLoopIndex1
                          
                End If 'rDataRange1(iOuterLoopIndex1, 5).Value < dThreshold1
                      
            Next iOuterLoopIndex1
       
        End If 'IsNumeric1(Target.Value) And Target.Value <> "" And Target.Value <> 0 And Int(Target.Value) = Target.Value
   
    End If 'Target.Address = "$V$11"

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[Rahul87]

Dear Jim, Thanks once again, can you please suggest where should I include this above code.

And apart from that can you please assist me in writing the above code totally opposite for the below ranges, which you can see in my excel workbook which I shared for short Rank. Please refer the shared workbook on same link Link to download the new file.

Anyhow I tried to do so, but not able to achieve, cause I know I have done totally wrong, so please do let me know how can achieve this for both. Below is the code.

Private Sub Worksheet_Change1(ByVal Target As Range)
    'StartTimer

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim rDataRange1 As Range
  
    Dim dThreshold1 As Double
  
    Dim vConsideration1 As Variant
  
    Dim iMaxRank1 As Long
  
    Dim iOutOfRank1 As Long
  
    Dim iPosition1 As Long

    Dim iOutputRow1 As Long
  
    Dim iSerialNumber1 As Long
  
    Dim iOuterLoopIndex1 As Long
  
    Dim iInnerLoopIndex1 As Long
          
    Set rDataRange1 = Range("K2:R" & Cells(Rows.Count, "R").End(xlUp).Row)

    If Target.Address = "$V$11" Then
  
'       Check if the entered value is an integer and not empty and not zero
        If IsNumeric(Target.Value) And Target.Value <> "" And Target.Value <> 0 And Int(Target.Value) = Target.Value Then

'           Get the threshold, consideration, iMaxRank1, and iPosition1 values from the worksheet
            dThreshold1 = Range("V14").Value
          
            vConsideration1 = Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).Value
          
            iMaxRank1 = Range("V11").Value '>= same as iPosition1?
          
            iOutOfRank1 = Range("V15").Value
          
            iPosition1 = Range("V11").Value  '>= same as iMaxRank?
          
'           Initialize the row counter for writing data to the output range
            iOutputRow1 = 19
          
'           Initialize the serial number
            iSerialNumber1 = 1
          
'           Loop through the rows in the data range
            For iOuterLoopIndex1 = 1 To rDataRange1.Rows.Count
          
'               Check if the percentage value in the O column is smaller than the threshold
                If rDataRange1(iOuterLoopIndex1, 5).Value < dThreshold1 Then

'                   Check if the LTP column value is smaller than the consideration value
                    For iInnerLoopIndex1 = 1 To UBound(vConsideration1)
                  
                        If rDataRange1(iOuterLoopIndex1, 2).Value < vConsideration1(iInnerLoopIndex1, 1) Then
                      
'                           Check if the Rank column value is less than or equal to the iMaxRank1 value
                            If rDataRange1(iOuterLoopIndex1, 8).Value >= iMaxRank1 Then

'                               Check if the position value in the vertical order is vacant
                                If Range("Y" & (iPosition1 + 18)).Value = "" Then
                              
'                                   Write the serial number and Trading Symbol horizontally to the output range starting at V19
                                    Range("X" & iOutputRow1).Value = iSerialNumber1
                                    Range("Y" & iOutputRow1).Value = rDataRange1(iOuterLoopIndex1, 1).Value

'                                   Increment the row and serial number
                                    iOutputRow1 = iOutputRow1 + 1
                                    iSerialNumber1 = iSerialNumber1 + 1

'                               If the position value in the vertical order is not vacant, check if the rank is smaller than the iOutOfRank value and if the LTP is less than TSL
                                ElseIf rDataRange1(iOuterLoopIndex1, 8).Value < iOutOfRank1 And rDataRange1(iOuterLoopIndex1, 2).Value > rDataRange1(iOuterLoopIndex1, 6).Value Then

'                                   Find the next vacant position in the vertical order
                                    Do While Range("Y" & (iPosition + 18)).Value <> ""
                                        iPosition1 = iPosition1 + 1
                                    Loop

'                                   Write the serial number and Trading Symbol horizontally to the vacant position in the vertical order
                                    Range("X" & (iPosition1 + 18)).Value = iSerialNumber1
                                    Range("Y" & (iPosition1 + 18)).Value = rDataRange1(iOuterLoopIndex1, 1).Value

'                                   Increment the serial number
                                    iSerialNumber1 = iSerialNumber1 + 1
                              
                                End If 'rDataRange1(iOuterLoopIndex1, 8).Value >= iMaxRank
                          
                            End If 'rDataRange1(iOuterLoopIndex1, 2).Value < vConsideration(iInnerLoopIndex1, 1)
                      
                        End If 'rDataRange1(iOuterLoopIndex1, 2).Value > vConsideration1(iInnerLoopIndex1, 1)
                  
                    Next iInnerLoopIndex1
                         
                End If 'rDataRange1(iOuterLoopIndex1, 5).Value < dThreshold1
                     
            Next iOuterLoopIndex1
      
        End If 'IsNumeric1(Target.Value) And Target.Value <> "" And Target.Value <> 0 And Int(Target.Value) = Target.Value
  
    End If 'Target.Address = "$V$11"

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Apart from that Jim , I have checked the output it is writing the trading symbols duplicate one by one. It should be only One .

output is below in snapshot by your given code.

Above the Trading symbol should reflect only one serial no 1 and in serial number 2 it should be different one and so on.

 

[OaklandJim]

I wish that language is not a problem. I want to assist but I just cannot understand what you want. I wish that it is not so difficult for me to understand what the code is supposed to do.

The code -- for long rank -- I provided was my attempt at repairing your code. I do not know if it does what is needed. You cannot tell me if it is correct? I cannot tell how to make it do what is wanted.

When I run that code on the data that you provided for the long rank nothing happens. So is it the data that is wrong or the code? I will guess that it is the code.

Sorting demo6.1.xlsm
	A	B	C	D	E	F	G	H
1	LONG LIST
2	Trading Symbol	LTP	ATP	Recorded ATP	Rate	TSL	RECONSIDERATION	Rank
3	HDFC23APRFUT	2526.8	2429.62	2429.62	4.00%	2551.096	2429.62	1
4	BSOFT23APRFUT	262.5	254.85	254.85	3.00%	267.5971	254.85	2
5	CANBK23APRFUT	284.85	276.55	276.55	3.00%	290.3811	276.55	3
6	COLPAL23APRFUT	1500	1470.59	1470.59	2.00%	1544.118	1470.59	4
7	AXISBANK23APRFUT	840	823.53	823.53	2.00%	864.7059	823.53	5
8	GRASIM23APRFUT	1644.15	1611.91	1611.91	2.00%	1692.507	1611.91	6
9	EICHERMOT23APRFUT	2964.5	2935.15	2935.15	1.00%	3081.906	2935.15	7
10	BERGEPAINT23APRFUT	578.05	572.33	572.33	1.00%	600.9431	572.33	8
11	AUBANK23APRFUT	567	567.00	567.00		595.35	567.00	9
12	APOLLOHOSP23APRFUT	4339	4339.00	4339.00		4555.95	4339.00	10
13	BOSCHLTD23APRFUT	19140	19333.33	19333.33	-1.00%	20300	19333.33	11
14	DABUR23APRFUT	548	553.54	553.54	-1.00%	581.2121	553.54	12
15	GODREJPROP23APRFUT	1037.15	1058.32	1058.32	-2.00%	1111.232	1058.32	13
16	CHAMBLFERT23APRFUT	265.3	1233.00	1233.00	-78.48%	1294.65	1233.00	14
17	BIOCON23APRFUT	207.5	1300.00	1300.00	-84.04%	1365	1300.00	15
18	BHEL23APRFUT	22222	232232.00	232232.00	-90.43%	243843.6	232232.00	16
19	BAJAJFINSV23APRFUT	1260.6	333333.00	333333.00	-99.62%	349999.7	333333.00	17
Sheet2

Cell Formulas
Range	Formula
E3:E19	E3	=(B3-D3)/D3
F3:F19	F3	=IFERROR(C3*$V$6," ")
G3:G19	G3	=IFERROR(C3*$V$7," ")

Question: is there always the same number of rows of data? Are there any rows of data that should be in the results area?

I understand that the short rank code does the opposite of what the long rank code does but I cannot understand what the long rank code is supposed to do.

Maybe try again to describe what the long rank code should do and then we can address the short rank code after the long rank code is correct. Maybe have a friend who know English better help you?

Have a good day!

 

[Rahul87]

I wish that language is not a problem. I want to assist but I just cannot understand what you want. I wish that it is not so difficult for me to understand what the code is supposed to do.

The code -- for long rank -- I provided was my attempt at repairing your code. I do not know if it does what is needed. You cannot tell me if it is correct? I cannot tell how to make it do what is wanted.

When I run that code on the data that you provided for the long rank nothing happens. So is it the data that is wrong or the code? I will guess that it is the code.

Sorting demo6.1.xlsm
	A	B	C	D	E	F	G	H
1	LONG LIST
2	Trading Symbol	LTP	ATP	Recorded ATP	Rate	TSL	RECONSIDERATION	Rank
3	HDFC23APRFUT	2526.8	2429.62	2429.62	4.00%	2551.096	2429.62	1
4	BSOFT23APRFUT	262.5	254.85	254.85	3.00%	267.5971	254.85	2
5	CANBK23APRFUT	284.85	276.55	276.55	3.00%	290.3811	276.55	3
6	COLPAL23APRFUT	1500	1470.59	1470.59	2.00%	1544.118	1470.59	4
7	AXISBANK23APRFUT	840	823.53	823.53	2.00%	864.7059	823.53	5
8	GRASIM23APRFUT	1644.15	1611.91	1611.91	2.00%	1692.507	1611.91	6
9	EICHERMOT23APRFUT	2964.5	2935.15	2935.15	1.00%	3081.906	2935.15	7
10	BERGEPAINT23APRFUT	578.05	572.33	572.33	1.00%	600.9431	572.33	8
11	AUBANK23APRFUT	567	567.00	567.00		595.35	567.00	9
12	APOLLOHOSP23APRFUT	4339	4339.00	4339.00		4555.95	4339.00	10
13	BOSCHLTD23APRFUT	19140	19333.33	19333.33	-1.00%	20300	19333.33	11
14	DABUR23APRFUT	548	553.54	553.54	-1.00%	581.2121	553.54	12
15	GODREJPROP23APRFUT	1037.15	1058.32	1058.32	-2.00%	1111.232	1058.32	13
16	CHAMBLFERT23APRFUT	265.3	1233.00	1233.00	-78.48%	1294.65	1233.00	14
17	BIOCON23APRFUT	207.5	1300.00	1300.00	-84.04%	1365	1300.00	15
18	BHEL23APRFUT	22222	232232.00	232232.00	-90.43%	243843.6	232232.00	16
19	BAJAJFINSV23APRFUT	1260.6	333333.00	333333.00	-99.62%	349999.7	333333.00	17
Sheet2

Cell Formulas
Range	Formula
E3:E19	E3	=(B3-D3)/D3
F3:F19	F3	=IFERROR(C3*$V$6," ")
G3:G19	G3	=IFERROR(C3*$V$7," ")

Question: is there always the same number of rows of data? Are there any rows of data that should be in the results area?

I understand that the short rank code does the opposite of what the long rank code does but I cannot understand what the long rank code is supposed to do.

Maybe try again to describe what the long rank code should do and then we can address the short rank code after the long rank code is correct. Maybe have a friend who know English better help you?

Have a good day!

Dear Jim, I deeply apologies that I am not able to make you understand what actually I need, but as per your reply please find below and if not understood now also then please let me know

The code -- for long rank -- I provided was my attempt at repairing your code. I do not know if it does what is needed Ans:-Yes it did, but it is providing repeated trading symbols. You cannot tell me if it is correct? Ans:-It was correct. I cannot tell how to make it do what is wanted.

When I run that code on the data that you provided for the long rank nothing happens. So is it the data that is wrong or the code? I will guess that it is the code Ans: Yes but not full, actually the trading symbols are pasted repeatedly which I have attached the snapshot.

Sheet2
	A	B	C	D	E	F	G	H
Sorting demo6.1.xlsm
1	LONG LIST
2	Trading Symbol	LTP	ATP	Recorded ATP	Rate	TSL	RECONSIDERATION	Rank
3	HDFC23APRFUT	2526.8	2429.62	2429.62	4.00%	2551.096	2429.62	1
4	BSOFT23APRFUT	262.5	254.85	254.85	3.00%	267.5971	254.85	2
5	CANBK23APRFUT	284.85	276.55	276.55	3.00%	290.3811	276.55	3
6	COLPAL23APRFUT	1500	1470.59	1470.59	2.00%	1544.118	1470.59	4
7	AXISBANK23APRFUT	840	823.53	823.53	2.00%	864.7059	823.53	5
8	GRASIM23APRFUT	1644.15	1611.91	1611.91	2.00%	1692.507	1611.91	6
9	EICHERMOT23APRFUT	2964.5	2935.15	2935.15	1.00%	3081.906	2935.15	7
10	BERGEPAINT23APRFUT	578.05	572.33	572.33	1.00%	600.9431	572.33	8
11	AUBANK23APRFUT	567	567.00	567.00		595.35	567.00	9
12	APOLLOHOSP23APRFUT	4339	4339.00	4339.00		4555.95	4339.00	10
13	BOSCHLTD23APRFUT	19140	19333.33	19333.33	-1.00%	20300	19333.33	11
14	DABUR23APRFUT	548	553.54	553.54	-1.00%	581.2121	553.54	12
15	GODREJPROP23APRFUT	1037.15	1058.32	1058.32	-2.00%	1111.232	1058.32	13
16	CHAMBLFERT23APRFUT	265.3	1233.00	1233.00	-78.48%	1294.65	1233.00	14
17	BIOCON23APRFUT	207.5	1300.00	1300.00	-84.04%	1365	1300.00	15
18	BHEL23APRFUT	22222	232232.00	232232.00	-90.43%	243843.6	232232.00	16
19	BAJAJFINSV23APRFUT	1260.6	333333.00	333333.00	-99.62%	349999.7	333333.00	17

Range	Formula
Cell Formulas
E3:E19	E3	=(B3-D3)/D3
F3:F19	F3	=IFERROR(C3*$V$6," ")
G3:G19	G3	=IFERROR(C3*$V$7," ")

please ignore the cell formula, we need to work on their values.
Question: is there always the same number of rows of data? Ans:-No the number of rows may increase or decrease.
Are there any rows of data that should be in the results area?, Ans:-it depends on user that how much he will input the Value in Cell V5

I understand that the short rank code does the opposite of what the long rank code does, Yes.

Have a good day!
Like Quote

 

[OaklandJim]

As I said, I just do not understand what the long rank code should do. And I'm not sure how I would test code even if I try to write it.

Without another try at an explanation of the need by you will have to hope that someone else understands and can write the code that you need.

Do you have a friend who knows English and who can help you describe the logic of the code in a different way?

Maybe show different data and then some examples of the results that you expect. Try using XL2BB to do that. See HERE.

 

[Rahul87]

As I said, I just do not understand what the long rank code should do. And I'm not sure how I would test code even if I try to write it.

Without another try at an explanation of the need by you will have to hope that someone else understands and can write the code that you need.

Do you have a friend who knows English and who can help you describe the logic of the code in a different way?

Maybe show different data and then some examples of the results that you expect. Try using XL2BB to do that. See HERE.

Ok Jim, thank you very much and your help was highly appreciated. I will try my best to rewrite the requirements and then I will post a fresh post. Thank you very much once again.

 

[OaklandJim]

Post to the same thread or I will not see it. Others will see it too. Please try to post different data and examples of results so someone willing to assist understands what the results should look like. Do have a look at XL2BB! With that add-i you can post portions of a worksheet.

 

[OaklandJim]

If I can understand the need I'm hopeful that I can assist.