duplicate ranks while ranking with countif for different numbers
hi all,

i am trying to rank a set of numbers with countif but it gives me the same rank for different numbers while rank function works fine as shown below:



here are the formulae used for the first row. the same formulae have been dragged down to rest of the rows.
=countif($e$2:$e$4,">"&e2)+1
=rank.eq(e2,$e$2:$e$4)

has anyone faced this issue before or has any ideas how to solve this?
i need to use countifs because i am ranking based on multiple fields in my original dataset and cannot use the rank function.

any help is appreciated.

regards,
syed
welcome to mrexcel!

it actually works for me (see below). this appears to be a kind of rounding error. the way excel stores numbers causes some issues when you have decimals. it is a bit rare when comparing a cell to itself, but it can happen. you can try to use sumproduct instead, see column h.

efgh1valuescountifranksumproduct248737.76222336113.11333454287.76111f2:f4f2=countif($e$2:$e$4,">"&e2)+1g2:g4g2=rank.eq(e2,$e$2:$e$4)h2:h4h2=sumproduct(--(round($e$2:$e$4,2)>round(e2,2)))+1
`for i in count:` means `for i in 7:`, which won't work. The bit after the `in` should be of an iterable type, not a number. Try this:



```
for i in range(count):

``` sadly this isn't the first tine that the equal function fails with me and don't know the reason.
the isequal() function didn't fail. the two results are different ... just not by very much:
>> percent\_difference=abs(c\_num - sing\_ratio)./c\_num*100,

percent\_difference =

 2.4864e-14
if you want to know how cond() is computed, you can just read it:
 >> type cond.m The behaviour must be because you are appending the same array to both of the arrays.  

Try appending a different array for cell*code and cell*type separately. sorry for misunderstanding the core issue with this one,@tom\_mgdi. i just tested a find row step using a simple formula (just a sum function) and the zap was able to search based on the value of the result of the formula (ie the result of the sum). this tells usthat the zap is failing specifically for query or import range formula. it’s likelynot finding the valuesbecause lookup formulas and import formulas will only show the imported value while they are being observed in google sheets, otherwisethey either appear as blank or as the raw function. if you have access to the other sheet that you are using the function to get results from, it may be worth adding an extra “lookup spreadsheet row” action to the zap, to get the information from that row in the other sheet, then combine the two rows later on. you’re on the right track.

is the name column of the choices table of the same column type as the 1st place and 2nd place columns?

please post a screenshot of the error message and the complete expression to which it refers. yes i already tried the uff input order like i mentioned in my post.

in the end i just added an additional layer in my neural network to swap from channel-first, to channel-last after the input has been read so this issue can be closed. Turns out If I tweked each method 



```
foreach (var dataColumn in readFields.Select(column => new DataColumn(columntypeof(string)) { AllowDBNull = true, DefaultValue = string.Empty }))
{
    csvData.Columns.Add(dataColumn);
}

```

and in the second Method I changed it to use an index rather than column name



```
for (var count = 0; count < table.Columns.Count; count++)
{
    sqlBulkCopy.ColumnMappings.Add(count, count);
}

``` The existing solutions based on `findall` are fine for non-overlapping matches (and no doubt optimal except maybe for HUGE number of matches), although alternatives such as `sum(1 for m in re.finditer(thepattern, thestring))` (to avoid ever materializing the list when all you care about is the count) are also quite possible. Somewhat idiosyncratic would be using `subn` and ignoring the resulting string...:



```
def countnonoverlappingrematches(pattern, thestring):
  return re.subn(pattern, '', thestring)[1]

```

the only real advantage of this latter idea would come if you only cared to count (say) up to 100 matches; then, `re.subn(pattern, '', thestring, 100)[1]` might be practical (returning 100 whether there are 100 matches, or 1000, or even larger numbers).


Counting **overlapping** matches requires you to write more code, because the built-in functions in question are all focused on NON-overlapping matches. There's also a problem of definition, e.g, with pattern being `'a+'` and thestring being `'aa'`, would you consider this to be just one match, or three (the first `a`, the second one, both of them), or...?


Assuming for example that you want possibly-overlapping matches *starting at distinct spots in the string* (which then would give TWO matches for the example in the previous paragraph):



```
def countoverlappingdistinct(pattern, thestring):
  total = 0
  start = 0
  there = re.compile(pattern)
  while True:
    mo = there.search(thestring, start)
    if mo is None: return total
    total += 1
    start = 1 + mo.start()

```

Note that you do have to compile the pattern into a RE object in this case: function `re.search` does not accept a `start` argument (starting position for the search) the way *method* `search` does, so you'd have to be slicing thestring as you go -- definitely more effort than just having the next search start at the next possible distinct starting point, which is what I'm doing in this function. You need to iterate over the resource...

 
```
while ($row = mysql_fetch_object($tariff_query)) {
    echo $row->somefield;
    echo $row->someotherfield;
}

```
 So you now get all the records not just the first one.

 BTW, if you want to use an object, why not use PDO or mysqli? mysql is dead - or as good as. you might be able to use synchronize. if you have more than 2 tables, you will need to use it multiple times.