Returning Row and Column of a search value

  • Thread starter Thread starter junkie
  • Start date Start date
J

junkie

All,
I have the following
=SUMPRODUCT(('>45Days'!$J$4:$J$318=$A$67)*('>45Days'!$T$4:$T$318=D61))

that will return the number of occurences if the, if the conditions are
met. This gives me one, which is correct. Are there any functions
that will give me the row and column of the occurence so I do not have
to scroll through the entire worksheet to find out where it actually
is? Do I have to get write VBA?

Please help THX

-junkie
 
junkie > said:
=SUMPRODUCT(('>45Days'!$J$4:$J$318=$A$67)*('>45Days'!$T$4:$T$318=D61))

that will return the number of occurences if the, if the conditions are
met. This gives me one, which is correct. Are there any functions
that will give me the row and column of the occurence so I do not have
to scroll through the entire worksheet to find out where it actually
is? Do I have to get write VBA?
Click to expand...

Looks like the column of such an occurrence, if there are any, is either J
or T. Or is there some other column of interest?

As for row, if you've already calculated the SUMPRODUCT in some cell, say,
EG1, then the row index of the first match would be

=MATCH(1,('>45Days'!$J$4:$J$318=$A$67)*('>45Days'!$T$4:$T$318=D61),0)
 
Hello Harlan,
Thanks for replying sumproduct returns the number of occurences of m
array, therefore the 1 refers to the number of occurences of
('>45Days'!H4:H318<>"")*('>45Days'!T4:T318="")

and not the column in which ir resides. I am looking for somethin
that will return the row and column for where this occurence is found.
For example i found 1 occurence and in the cell where the formul
resides I would like it to return the exact row x col or just the row.

Thx,
junki
 
Thanks for replying sumproduct returns the number of occurences of my
array, therefore the 1 refers to the number of occurences of
('>45Days'!H4:H318<>"")*('>45Days'!T4:T318="")
Click to expand...

Well, 1 would be the result if there were only one match, but I understand what
you're tyring to say.
and not the column in which ir resides. I am looking for something
that will return the row and column for where this occurence is found.
Click to expand...

Here's where *YOU* are being unclear. You're searching through two separate
columns for different conditions in each columns, and declaring a 'match' when
both criteria are satisfied in both columns on the same row. In other words, the
'match' occurs when both columns H and T satisfy your criteria. Let's say
there's only one such match, and it occurs in row 100. So '>45Days'!H100<>"" and
'>45Days'!T100="". In which column would you consider such a match occurs? H, T
or some as yet unmentioned column?

You have provided nothing so far that would allow anyone to figure out what you
mean by returning the *column* where this occurrence is found. It's pretty
clearly found in *both* columns H and T, but that doesn't seem to be the answer
you want. Since it is unclear (to put it mildly) what else it could be, I think
you need to provide a simple example of possible data, 4 rows of cols H and T
only with one of the rows satisfying both criteria would be sufficent, and the
column result you'd expect.

For example i found 1 occurence and in the cell where the formula
resides I would like it to return the exact row x col or just the row.
Click to expand...

If you mean the row within the range processed (H4:T318), so that a match in
H100 and T100 would return 97, which is the 97th row in the range H4:T318, then
(to repeat) use the array formula

=MATCH(1,('>45Days'!H4:H318<>"")*('>45Days'!T4:T318=""),0)

and *NOTE* that I'm not summing anything, rather I'm locating the first
(topmost) instance in which the values in cols H and T on the same row are
nonempty (<>"") and empty (""), respectively. If you want the match to give the
row number within the worksheet, use

=MATCH(1,('>45Days'!H4:H318<>"")*('>45Days'!T4:T318=""),0)+CELL("Row",H4:T318)-1

One or the other of these gives you the row index or number you claim to be
seeking. If that's not what you want, then you've done an exceptionally poor job
of describing what you want.
 
Hello Harlan,
Hey thanks for replying one again, and yes Im very confusing, heck som
times I confuse myself.

Nevertherless, you have answered my question. I just really wanted t
return the ROW and not COL or ROWxCOL sorry about that.

I wonder if a SUMPRODUCT returns multiple occurences, is there a awa
to pull all the ROWS in which the condtionals occur for the arrays?

Thanks again for clarifying my own confusions. hehe

Regards,
-junki
 
...
...
I wonder if a SUMPRODUCT returns multiple occurences, is there a away
to pull all the ROWS in which the condtionals occur for the arrays?
Click to expand...
...

You're using SUMPRODUCT to return the number of rows in which separate criteria
are satsified in different columns. For the sake of my own simplicity, I'll
assume that SUMPRODUCT formula is in a cell named N. If you want the row indices
within the range containing those matches, then you could use the following
formulas in, say, column X beginning in X1.

X1:
=IF(N,MATCH(1,('>45Days'!H$4:H$318<>"")*('>45Days'!T$4:T$318=""),0),"")

X2:
=IF(N>ROW()-ROW(X$1),MATCH(1,('>45Days'!H$4:H$318<>"")*('>45Days'!T$4:T$318="")
*(ROW('>45Days'!H$4:T$318)-CELL("Row",'>45Days'!H$4:T$318)>=X1),0),"")

Then select X2 and fill down as far as needed. The X2 formula will evaluate as
"" after the matches in '>45Days'!H$4:T$318 have been exhausted.
 
Hey Harlan,
I hate it when this happens, but for some reason as in programming thi
always never ceases to happen. Im applying the same formula again t
try to return the row when the occurence happens and it gives me 9
=MATCH(1,('>45Days'!H4:H318<>"")*('>45Days'!T4:T318=""),0)+CELL("Row",H4:T318)-1

The occurence actually occurs in Row 133, any ideas?????


Thx,
-Shaw
 
. . . Im applying the same formula again to
try to return the row when the occurence happens and it gives me 9
=MATCH(1,('>45Days'!H4:H318<>"")*('>45Days'!T4:T318=""),0)
+CELL("Row",H4:T318)-1

The occurence actually occurs in Row 133, any ideas?????
Click to expand...

What do H9 and T9 in the '>45Days' worksheet evaluate to?
 
I hate it when this happens, but for some reason as in programming this
always never ceases to happen. Im applying the same formula again to
try to return the row when the occurence happens and it gives me 9
=MATCH(1,('>45Days'!H4:H318<>"")*('>45Days'!T4:T318=""),0)
+CELL("Row",H4:T318)-1

The occurence actually occurs in Row 133, any ideas?????
Click to expand...

Next question: did you enter the formula above as an array formula? I wasn't
clear about this a few responses ago - one of those things I just assumed.
Anyway, if your formula wasn't array-entered, enter it as an array formula. What
do you get when you do so?
 
Sorry about the late response! Anyhow ya I did enter it as an arra
formula gives me 9, when not entered it gives #N/A.

I believe it is only returning the offset with respect to where my cel
is currently active, I imagine that is what is is doing. When I go an
delete an entry for a cell in column H to make the condition evaluat
to true, the formula gives me the correct row. Dont want to swea
this anymore as Im sure you are very busy as I am, but thanks sooo
much for your help Harlan.


Best Regards,
-Shaw
 
Back
Top