How to find Row number and column number

Farooqui Noor

Board Regular
Joined
Dec 31, 2019
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
I have some numbers in sheet No.1 and in sheet No. 2 i want to write Row number and column number of each number present in sheet No.1 by formula. is it possible.
nn.xlsx
H
11
Sheet1

nn.xlsx
ABCD
1
2NumberRow NumberColumn Number
3163
42??
53??
64??
75??
86??
9795
108??
11How to find row number and column number by formula
12
Sheet2


nn.xlsx
ABCDEFGHIJ
112345678910
2
3
4
5
614
7
862
975
10
1138
12
13
14
15
16
Sheet1
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try adding these 2 functions and then insert the formulas in the table as shown in picture provided. (type =Column_num(J4) in cell L4 in my example.)
Hope this helps.

VBA Code:
Public Function Row_num(N As Integer) As Integer
  Dim rng As Range
  Dim cl As Range
 
    Set rng = Sheet1.Range("A2:G11")
 
    For Each cl In rng
        If cl.Value = N Then
            Row_num = cl.Row
            Exit For
        End If
    Next cl
 
 
End Function


Public Function Column_num(N As Integer) As Integer
  Dim rng As Range
  Dim cl As Range
 
    Set rng = Sheet1.Range("A2:G11")
 
    For Each cl In rng
        If cl.Value = N Then
            Column_num = cl.Column
            Exit For
        End If
    Next cl
 
 
End Function
 

Attachments

  • mr_excel.JPG
    mr_excel.JPG
    66.2 KB · Views: 16
Last edited:
Upvote 0
How about
Fluff.xlsm
ABC
1
2NumberRow NumberColumn Number
3163
4286
53113
6467
7597
8683
9795
108117
Sheet2
Cell Formulas
RangeFormula
B3:B10B3=SUMPRODUCT((Sheet1!$A$2:$J$11=A3)*(ROW(Sheet1!$A$2:$J$11)))
C3:C10C3=SUMPRODUCT((Sheet1!$A$2:$J$11=A3)*(COLUMN(Sheet1!$A$2:$J$11)))
 
Upvote 0
But there is a problem I noticed. when I entered a same number twice in different cell the answer comes wrong. can we solve this query.
New Microsoft Excel Worksheet (2).xlsx
ABCDEFGHIJ
112345678910
2
35
4
52
614
7
8362
975
103
118
Sheet1


New Microsoft Excel Worksheet (2).xlsx
ABCDEFGH
1
2NumberRow NumberColumn Number
316363
42131256
53181082
646767
75121134
868383
979595
103181082
112131256
125121134
132550000
1456749221
1556749221
16567492
Sheet2
Cell Formulas
RangeFormula
B3:B16B3=SUMPRODUCT((Sheet1!$A$2:$J$11=A3)*(ROW(Sheet1!$A$2:$J$11)))
C3:C16C3=SUMPRODUCT((Sheet1!$A$2:$J$11=A3)*(COLUMN(Sheet1!$A$2:$J$11)))
E3:E15E3=ROW_num(A3)
F3F3= Column_num(A3)
F4:F15F4=Column_num(A4)
 
Upvote 0
Which row/column should be returned if you have the same number twice?
 
Upvote 0
if I entered a number 5 in cell D3 and second same number 5 entered in cell G9, then as a out put the answer shows row number 12 (sum of D3 & G9) and column number 11 (sum of D3 & G9).
 
Upvote 0
Yes, but what do you want the formula to return?
 
Upvote 0
actually my excel sheet having different names and different numbers but the same number is used for husband and wife (family tree). you can see in sample sheet (numbers 315 and 320. both numbers repeated in a particular sheet. Now when i applied the formula it shows wrong result in front of number 315 and 320 because both numbers have been written twice in the sheet. So how we can resolve the problem.
New Shijra Start 05-12-2022 (1).xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
12313زیارت علی ( زوجہ محمدی بیگم بنت واحد علی ابن اشرف علی)
13وفاتکامنمبرنامنکاحپیدائش
14امیر علی ( زوجہ رابعہ بیگم بنت یعقوب علی ابن اکبر علی ) (لاولد)وفاتکامنمبرنامنکاحپیدائش
15295لال بیگم زوجہ مکرم علی ابن ولایت علی ابن شمشاد علیحفاظت بیگم (غیر شادی شدہ )
16320عابد علی ( زوجہ گوری بیگم بنت صفدر علی ابن ببر علی )جننتی بیگم (غیر شادی شدہ )
17289کرامت علی ( زوجہ بادشاہ بیگم بنت کرم علی ابن امداد علی )315شافیہ بیگم زوجہ نواز علی ابن مقصود علی ابن دلاور علی
18نازنین بیگم عرف نزاکت بیگم زوجہ محبت علی ابن شفقت علی
19وفاتکامنمبرنامنکاحپیدائش
20317شبیر علی ( زوجہ اللہ والی بیگم بنت محفوظ علی ابن دلاور علی )وفاتکامنمبرنامنکاحپیدائش
21280سلامت علی (زوجہ صابرہ بیگم بنت سیدن علی ابن نجابت علی )317اللہ والی بیگم زوجہ شبیر علی ابن ہدایت علی ابن ببر علی
22حمایت علی ( زوجہ لطفن بیگم بنت رضا علی ابن بشارت علی )رضیہ بیگم زوجہ شرافت علی ابن سعادت علی ابن عنایت علی
23318وزیر بیگم زوجہ حفاظت علی ابن عنایت علیرفاقت علی ( زوجہ نورانی بیگم بنت نواز علی ابن مقصود علی )
24عبادت علی ( زوجہ خاتون بیگم بنت نواز علی ابن مقصود علی )
25وفاتکامنمبرنامنکاحپیدائش
26247اصغر علی ( زوجہ حسینی بیگم بنت شمش الدین افضل میاں خانہ پور )وفاتکامنمبرنامنکاحپیدائش
27333محبوب بیگم زوجہ جہانگیر علی ابن عنایت علی314صالحہ بیگم زوجہ سعادت علی ابن عنایت علی
28320گوری بیگم زوجہ عابد علی ابن حیدر علی315نواز علی ( زوجہ شافیہ بیگم بنت زیارت علی ابن دلاور علی )
29منیر بیگم زوجہ فیاض الدین ابن منیر الدین ابن امام الدین فاروقی253جہانگیر بیگم زوجہ شفی میاں ابن افضل میاں شیخ
S2 M
 
Upvote 0
Rather than repeating what the problem is, can you please answer my question.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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