• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

file processing with formulas that do not give desired results

Hi guys.

I have to do a series of elaborations but I am noticing that the results of the formulas entered do not appear and some I would need help in creating.

I show you the first.

As you can see the file consists of two sheets. In "Foglio1" I have to insert the data of the "popolazione" sheet of column "j" comparing the two columns with identical data.

Why doesn't the formula work?

Thank you
 

Attachments

  • Cartel1.xlsb
    9.4 KB · Views: 7
stefanoste78
Formulas works well!
... but Your datas won't match with Your way to use those formulas.
> Cell-formula ... is language sensitive - here it uses "type" - check more details from its syntax ))
> Match -formula ...
You try to 'match' exact value ... it works well, if popolazione-sheet would have exact-value ... but not!
... There are same looking value in cell K2, but ... there are 'something' after visible text.
>> after those notes ... formulas would give other kind of results
 
"VÍA MARTÍN LUTHER KING 1" en I2 Foglio1 es distinta a "VIA MARTIN LUTHER KING 1" en K2 de popolazione;
el primero mide 24 de largo y el segundo mide 170
 
stefanoste78
Formulas works well!
... but Your datas won't match with Your way to use those formulas.
> Cell-formula ... is language sensitive - here it uses "type" - check more details from its syntax ))
> Match -formula ...
You try to 'match' exact value ... it works well, if popolazione-sheet would have exact-value ... but not!
... There are same looking value in cell K2, but ... there are 'something' after visible text.
>> after those notes ... formulas would give other kind of results
I used the index compare formula. I cannot understand what you are referring to.
 
"VÍA MARTÍN LUTHER KING 1" en I2 Foglio1 es distinta a "VIA MARTIN LUTHER KING 1" en K2 de popolazione;
el primero mide 24 de largo y el segundo mide 170


they look identical to me .... Anse if I do, find by inserting an address copied from the other sheet brings me back to the cell with the same address.
I tried to put a value I invented and the formula works.
I wonder why the values that visually seem identical are different and what could be done to make them the same.
Thank you
 
stefanoste78
Check Your formula ...
Aren't You using Match-function too?
=MATCH(lookup_value, lookup_array, [match_type])
Your the last parameter with Match is 0
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
  • If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
As I and wrote
You try to 'match' exact value ... it works well, if popolazione-sheet would have exact-value ... but not!
... There are same looking value in cell K2, but ... there are 'something' after visible text.

It's not enough that those cells look same - those should be same while using 0.
Seems that You have pasted there more than You would like to paste!
eg "A cat" is not same as "A cat "
= after 2nd cat, there are 15 spaces, which are sometimes invisible.
 
I extracted that data from population software.
There was probably some being in the manipulation of the data but now I need to combine the data and I should find a solution.
Is there a possibility to strip off extra spaces or some other character that is invisible so that the data is the same and the formula works?
 
Those 'spaces' or invisible parts are there for some reason.
Have You tried to search eg from Chandoo.org it?
There are many threads which has solution for You. ... trim ... clean ...
This is a common challenge for many, who are eg copy&paste data from somewhere...
 
stefanoste78
There is no ... problem.
... or actually few ...
> If use match with exact match_type then there should be exact value to find.
> If copy & paste 'invisible' characters then those should notice/take care to use basic functions

Did You read my reply?
Did You notice word trim & clean?
Do search from up-top-corner and You'll find many solutions.
 
... ...
a) did You search with those two words?
b) 'google' Excel function which name is trim and
after that Excel function which name is clean
 
se ven idénticos a mí .... Anse si lo hago, encontrar la inserción de una dirección copiada de la otra hoja me trae de vuelta a la celda con la misma dirección.
Traté de poner un valor que inventé y la fórmula funciona.
Me pregunto por qué valores que visualmente parecen idénticos hijo diferente y que se puede hacer para hacer que los mismos.
Gracias
[/CITA]
"El K2 de popolazione tiene blancos a la derecha del texto, lo que hace diferente"
"Una forma de evitar estos problemas es tener los textos en una tabla aparte, y al usar tanto en I2 como en K2, hacerlo con referencia"
'"SI está en Hoja1 en A2, entonces poner en I2 y en K2: =Hoja1! A2"
 
stefanoste78
You could find those formulas from those previous threads - if You just would do that search as I've tried to help few times.
Has been localized for English, Danish, German, Spanish, French, Italian, Japanese, Korean, Dutch, Portuguese Brazilian, Russian, Swedish, Turkish, Chinese Traditional and Chinese Complex Script.
 
Like already explained within this forum - like in others - as every local Excel version is still in english internally​
so any smart people - good reader enough just warming a couple of neurons - can enter an english formula on VBA side​
via the Immediate window (Ctrl+G) then this formula appears automatically in local version within the worksheet​
so at kid level without any formula translation …​
 
Thanks for your answers. I did not know the combination of keys indicated by marc. However it is not possible to apply the formula because the data are not always uniform
 
As this combination is not hidden, written in the VBA menu …​
Good enough readers must read :​
 
As this combination is not hidden, written in the VBA menu …​
Good enough readers must read :​
thanks
 
Back
Top