Check if Number Exist in Row Range

ibmy

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
129
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
Thanks all for the formula.

@Akuini , the code produce result all "Max X"
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
VBA - floating photo
Replies
3
Views
92
RoryA
RoryA
R
  • Question
Copy and unfilter in VBA
Replies
1
Views
128
Alex Blakenburg
A
R
  • Solved
Loop through Rows
Replies
4
Views
170
rollingzep
R
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
473
mumps
mumps
D
  • Solved
VBA - Select a range of cells with keystrokes but don't want to see the cells
Replies
6
Views
206
Dan2k
D

Forum statistics

Threads
1,220,965
Messages
6,157,120
Members
451,399
Latest member
alchavar

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