Check if Number Exist in Row Range

ibmy

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Sample of 100+k rows data:
3.1.xlsb
DEFGHIJKLM
1MaxBIBexpected result :
2
31.207021.207024Max Exist
41.207021.20675
51.207021.20889
61.207021.20302
71.207021.20702
81.207031.207031Max X
91.207031.20701
101.207061.207063Max X
111.207061.20702
121.207061.20705
131.207061.20702
141.207021.20699
151.207241.207246Max Exist
161.207241.20724
171.207241.20703
181.207061.20706
191.207061.20703
201.207071.20707
211.207071.20707
221.207071.20703
231.207071.20707
241.207071.207031Max Exist
251.207101.20707
261.207121.20712
Sheet4

Data start at row 3.

Range = Start Range : Row x+1 -> End Range : Row x + Value in Column H
Example in Row 3:
H3 value is 4 so check number in D3 (1.20702) in F4:F7 (Start Range : Row 3+1=4 -> End Range : Row 3+4=7)
Result in Column M, Row 3, M3 "Max Exist" (1.20702 exist in F7). If number do not exist, result is "Max X"
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
What do you mean by Max Exist and Max X? Do you want the Max of the calculated range?
 
Upvote 0
awoohaw said:
What do you mean by Max Exist and Max X? Do you want the Max of the calculated range?
Click to expand...
Number in Column D is Max Number.
It just like the return of TRUE and FALSE in formula , in this case, if a number exist, it return "Max Exist" / TRUE, if not exist "Max X" (Max Do Not Exist) / FALSE
 
Upvote 0
ibmy said:
Number in Column D is Max Number.
It just like the return of TRUE and FALSE in formula , in this case, if a number exist, it return "Max Exist" / TRUE, if not exist "Max X" (Max Do Not Exist) / FALSE
Click to expand...
I think i figured what you want out:

Book1
DEFGHM
1MaxBIBexpected result :
2
31.207021.207024Max Exists
41.207021.20675 
51.207021.20889 
61.207021.20302 
71.207021.20702 
81.207031.207031Max X
91.207031.20701 
101.207061.207063Max X
111.207061.20702 
121.207061.29000 
131.207061.20702 
141.207021.20699 
151.207241.207246Max Exists
161.207241.20724 
171.207241.20703 
181.207061.20706 
191.207061.20703 
201.207071.20707 
211.207071.20707 
221.207071.20703 
231.207071.20707 
241.207071.207031Max Exists
251.20711.20707 
261.207121.20712 
Sheet2
Cell Formulas
RangeFormula
M3:M26M3=IF(ISNUMBER($H3),IF(ISNUMBER(MATCH($D3,(INDEX($F$1:$F$100,ROW()+1,1):INDEX($F$1:$F$100,ROW()+$H3,1)),0)),"Max Exists","Max X"),"")
 
Upvote 1
Book1
DEFGHIJKLM
1MaxBIB
2
31.207021.207024Max Exists
41.207021.20675 
51.207021.20889 
61.207021.20302 
71.207021.20702 
81.207031.207031Max X
91.207031.20701 
101.207061.207063Max X
111.207061.20702 
121.207061.29 
131.207061.20702 
141.207021.20699 
151.207241.207246Max Exists
161.207241.20724 
171.207241.20703 
181.207061.20706 
191.207061.20703 
201.207071.20707 
211.207071.20707 
221.207071.20703 
231.207071.20707 
241.207071.207031Max Exists
251.20711.20707 
261.207121.20712 
27
Sheet1
Cell Formulas
RangeFormula
M3:M26M3=IF(H3,IF(ISNUMBER(MATCH(D3,OFFSET(F3,1,,H3),0)),"Max Exists","Max X"),"")
 
Upvote 1
Solution
With vba:
VBA Code: 
Sub ibmy_1()
Dim va, vb
Dim i As Long, j As Long, h As Long
Dim flag As Boolean
va = Range("D3", Cells(Rows.Count, "D").End(xlUp)).Resize(, 5)
ReDim vb(1 To UBound(va, 1), 1 To 1)
For i = 1 To UBound(va, 1)
    If va(i, 5) <> "" Then
        flag = False
        h = va(i, 1)
        For j = i + 1 To i + va(i, 5)
            If va(j, 3) = h Then
                flag = True
                Exit For
            End If
        Next
        
        If flag Then
            vb(i, 1) = "Max Exist"
        Else
            vb(i, 1) = "Max X"
        End If
        i = i + va(i, 5)
    End If
Next

Range("M3").Resize(UBound(vb, 1), 1) = vb
End Sub
 
Upvote 1
Scott Huish said:
Book1
DEFGHIJKLM
1MaxBIB
2
31.207021.207024Max Exists
41.207021.20675 
51.207021.20889 
61.207021.20302 
71.207021.20702 
81.207031.207031Max X
91.207031.20701 
101.207061.207063Max X
111.207061.20702 
121.207061.29 
131.207061.20702 
141.207021.20699 
151.207241.207246Max Exists
161.207241.20724 
171.207241.20703 
181.207061.20706 
191.207061.20703 
201.207071.20707 
211.207071.20707 
221.207071.20703 
231.207071.20707 
241.207071.207031Max Exists
251.20711.20707 
261.207121.20712 
27
Sheet1
Cell Formulas
RangeFormula
M3:M26M3=IF(H3,IF(ISNUMBER(MATCH(D3,OFFSET(F3,1,,H3),0)),"Max Exists","Max X"),"")
Click to expand...
With 100K rows I think the OFFSET function may slow your worksheet down.
www.mrexcel.com

Replace Volatile OFFSET with INDEX

OFFSET is a volatile function. It breaks the smart recalc feature of Excel. A single OFFSET function near the top of the worksheet will cause every formula in the calculation chain to recalc constantly.
www.mrexcel.com www.mrexcel.com
 
Last edited:
Upvote 0
Book1
DEFGHIJKLM
1MaxBIBexpected result :
2
31.207021.207024Max Exists
41.207021.20675 
51.207021.20889 
61.207021.20302 
71.207021.20702 
81.207031.207031Max X
91.207031.20701 
101.207061.207063Max X
111.207061.20702 
121.207061.29 
131.207061.20702 
141.207021.20699 
151.207241.207246Max Exists
161.207241.20724 
171.207241.20703 
181.207061.20706 
191.207061.20703 
201.207071.20707 
211.207071.20707 
221.207071.20703 
231.207071.20707 
241.207071.207031Max Exists
251.20711.20707 
261.207121.20712 
Sheet5
Cell Formulas
RangeFormula
M3:M26M3=IF(H3<>"",IF(COUNTIF(F4:INDEX(F4:F100,H3),D3),"Max Exists","Max X"),"")


INDEX can also be volatile in some cases:

www.fm-magazine.com

Talking 'dirty' about volatile functions in Excel

To speed up Excel workbooks, modellers should plan to keep the number of volatile functions and range names to a manageable minimum.
www.fm-magazine.com www.fm-magazine.com
 
Upvote 1
Eric W said:
Book1
DEFGHIJKLM
1MaxBIBexpected result :
2
31.207021.207024Max Exists
41.207021.20675 
51.207021.20889 
61.207021.20302 
71.207021.20702 
81.207031.207031Max X
91.207031.20701 
101.207061.207063Max X
111.207061.20702 
121.207061.29 
131.207061.20702 
141.207021.20699 
151.207241.207246Max Exists
161.207241.20724 
171.207241.20703 
181.207061.20706 
191.207061.20703 
201.207071.20707 
211.207071.20707 
221.207071.20703 
231.207071.20707 
241.207071.207031Max Exists
251.20711.20707 
261.207121.20712 
Sheet5
Cell Formulas
RangeFormula
M3:M26M3=IF(H3<>"",IF(COUNTIF(F4:INDEX(F4:F100,H3),D3),"Max Exists","Max X"),"")


INDEX can also be volatile in some cases:

www.fm-magazine.com

Talking 'dirty' about volatile functions in Excel

To speed up Excel workbooks, modellers should plan to keep the number of volatile functions and range names to a manageable minimum.
www.fm-magazine.com www.fm-magazine.com
Click to expand...
Thanks for sharing that article @Eric W .
I'm surprised microsoft has minimized it. Maybe it is because so few users use INDEX as the second part of a range formula.

 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
VBA - floating photo
Replies
3
Views
341
RoryA
RoryA
J
  • Solved
VBA to search for duplicates, concatenate cells from another column and delete row
Replies
5
Views
188
Alex Blakenburg
A
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
314
bdt
B
I
  • Solved
Simplify my working code please
Replies
7
Views
166
ipbr21054
I
J
  • Question Question
VBA Search box and button code
Replies
0
Views
246
jlcodes
J

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top