Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, and match
Hello all.
Ok, I have a question about my formula in excel. =if(b1<1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, and match
The position is relative to the range given to the match function. Are you
trying to find the row number the second value is in or the index number within the range containing your data (which appears to start at A1, so these two are probably the same - for now). If the formula you posted returns a 3, that means it is in cell A4 (third cell in the range A2:A300). You could perhaps try: =if(b1<1,match(a1,a2:a$300,0)+rows(a$1:a1),"ok") I assume your data is sorted and you are trying to find the row number. "SteveDB1" wrote: Hello all. Ok, I have a question about my formula in excel. =if(b1<1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, and match
I would suspect some of your cells in column A are formatted as text.
Regards, Paul "SteveDB1" wrote in message ... Hello all. Ok, I have a question about my formula in excel. =if(b1<1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, and match
Row locations are my goal.
I've noticed that "match" finds the row that the requested value is located in. Ok, so that I understand this correctly.... Because I'm dropping one row each equation, it "resets" the starting point to be that cell the equation is in? While that kind of makes sense, in this case, I have some answers whose answers should only be one or two row values off. Some of these are upwards of 200 rows short. I.e., my errors are stating that a value is located in row 46, and it's actually in 157, and I'm near the top. I just went back and looked more closely, it does appear that the "reset" occurs; thus making that row's location the starting point. I.e., if my equation is in row 11, then row eleven is my starting point for my search the way it is written. Ok, let me ask this. how can I have it ignore the one row that I'm in with that specific equation? I.e., =if(b12<1,match(a12, a1:a11;a13:a300,0),"ok") Again, thank you. "JMB" wrote: The position is relative to the range given to the match function. Are you trying to find the row number the second value is in or the index number within the range containing your data (which appears to start at A1, so these two are probably the same - for now). If the formula you posted returns a 3, that means it is in cell A4 (third cell in the range A2:A300). You could perhaps try: =if(b1<1,match(a1,a2:a$300,0)+rows(a$1:a1),"ok") I assume your data is sorted and you are trying to find the row number. "SteveDB1" wrote: Hello all. Ok, I have a question about my formula in excel. =if(b1<1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, and match
Ok, While I've solved my problem in three steps as opposed to one, I'd still
like to know if I can use match and skip the row that my equation is located in. I.e., a jump over as it were. Is that possible, and if so, how? Thank you. "JMB" wrote: The position is relative to the range given to the match function. Are you trying to find the row number the second value is in or the index number within the range containing your data (which appears to start at A1, so these two are probably the same - for now). If the formula you posted returns a 3, that means it is in cell A4 (third cell in the range A2:A300). You could perhaps try: =if(b1<1,match(a1,a2:a$300,0)+rows(a$1:a1),"ok") I assume your data is sorted and you are trying to find the row number. "SteveDB1" wrote: Hello all. Ok, I have a question about my formula in excel. =if(b1<1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, and match
You could try:
=IF((B11)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$30 0,0)+ROWS(A$1:A1),"") which would tell you where the second occurence is (I assume there can't be more because you said the countif function will return either 1 or 2). If you are trying to identify the duplicates, you could enter =Countif(a$1:a1) in cell B1, copy down to B300. Then autofilter on column B (may want to insert a header row) using a custom criteria of greater than 1. That would bunch all of them together for you and you could delete the filtered rows (Edit/Delete Row) - if your intention is to delete them. Just be sure to backup your data before trying new things. "SteveDB1" wrote: Ok, While I've solved my problem in three steps as opposed to one, I'd still like to know if I can use match and skip the row that my equation is located in. I.e., a jump over as it were. Is that possible, and if so, how? Thank you. "JMB" wrote: The position is relative to the range given to the match function. Are you trying to find the row number the second value is in or the index number within the range containing your data (which appears to start at A1, so these two are probably the same - for now). If the formula you posted returns a 3, that means it is in cell A4 (third cell in the range A2:A300). You could perhaps try: =if(b1<1,match(a1,a2:a$300,0)+rows(a$1:a1),"ok") I assume your data is sorted and you are trying to find the row number. "SteveDB1" wrote: Hello all. Ok, I have a question about my formula in excel. =if(b1<1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if, and match
Ok
I've got it now-- thank you. The only difference is that the rows function needs to be row() instead of rows(A$1:A1) Having the row() gives the actual row number, and the rows(A$1:A1) offsets it by a negative one-- making me think it'd read rows(A$1:A1) as 0, then 1, 2, ......, n So, the function then becomes =IF((B11)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$30 0,0)+ROW(),"") ballissimo..... Thank you very much for your help. Enjoy your weekend. "JMB" wrote: You could try: =IF((B11)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$30 0,0)+ROWS(A$1:A1),"") which would tell you where the second occurence is (I assume there can't be more because you said the countif function will return either 1 or 2). If you are trying to identify the duplicates, you could enter =Countif(a$1:a1) in cell B1, copy down to B300. Then autofilter on column B (may want to insert a header row) using a custom criteria of greater than 1. That would bunch all of them together for you and you could delete the filtered rows (Edit/Delete Row) - if your intention is to delete them. Just be sure to backup your data before trying new things. "SteveDB1" wrote: Ok, While I've solved my problem in three steps as opposed to one, I'd still like to know if I can use match and skip the row that my equation is located in. I.e., a jump over as it were. Is that possible, and if so, how? Thank you. "JMB" wrote: The position is relative to the range given to the match function. Are you trying to find the row number the second value is in or the index number within the range containing your data (which appears to start at A1, so these two are probably the same - for now). If the formula you posted returns a 3, that means it is in cell A4 (third cell in the range A2:A300). You could perhaps try: =if(b1<1,match(a1,a2:a$300,0)+rows(a$1:a1),"ok") I assume your data is sorted and you are trying to find the row number. "SteveDB1" wrote: Hello all. Ok, I have a question about my formula in excel. =if(b1<1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Any way for 2 column vlookups. i.e match last name then match firs | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
When MATCH and v/hLOOKUP functions *FAIL* to match (but they should)... | Excel Worksheet Functions |