I have table like this 1 2 6 5 2 5 2 4 3 6 7 9 2 2 4 1 now i want to get the position of each data in the table, how can i do so? I have tried with the rank formula but cannot be used for the duplicate entries.
I have table like this 1 2 6 5 2 5 2 4 3 6 7 9 2 2 4 1 now i want to get the position of each data in the table, how can i do so? I have tried with the rank formula but cannot be used for the duplicate entries.
Last edited by jignesh142; 01-29-2014 at 12:19 AM.
is it a one row table with repeating values? and if you are wanting to learn the position of, say, 2, which 2 is it? there are 5.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Hi, mancubus, i don't know how to format the table in this forum but it is like first row contains 1 2 6 5, second row 2 5 2 4, third row 3 6 7 9, fourth row 2 2 4 1 i want the position of every 2 that are in the table
hi. you can use worksheet function MATCH to return the relative position of a value. ----- assuming your table is in columns A-D, with no header row: in E1 : =MATCH(2,A1: D1,0) copy down. args: lookup_value, lookup_array, match_type. 0 in the formula is for exact match. to learn more visit: http://www.contextures.com/xlFunctions03.html
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Posting Permissions
Reply With Quote