Excel 2007 : Using Formula result as TEXT for other formulas.

Perhaps you are saying you want something like:

Please Login or Register  to view this content.

In terms of how you coerce a number to a string, use &"", eg: =(1*2)&""

Hi

Will this help? Please open the files i uploaded.

The first is the formula to get the name of the tecnician.
The second finds the ID based on the name.

The name is exactly the same but it cant find the ID, wish is in the first collumn i use in the second print screen.

This must be this way because... well maybe not.
IŽll try doing in reverse.

Images are of limited use in truth.

We can tell from the Image that the VLOOKUP is failing to find a match but we have no idea if the names are actually listed in INPUT!O:O

(note: your table array contains 2 coumns [O:P] yet presently you're returning column 1 - this doesn't affect the current Error but is something you should be aware of)

by all means upload your file - I am going off line for a while but rest assured others will be able to resolve for you

Thank you so much.

Ok so here it is:

I need to remove all the duplicated IDs From "ID Técnicos" to the column "Remove Duplicate".

And then i would like to use the ID to search for the name of the coworker.
I hope i can do the rest.

But removing automaticly duplicat records and search with vLookup using the TEXT result of another Formula is making me headheaks.

Thank You
Best Regards

Perhaps you could outline the first few expected results (calculated manually) ?

As far as I can tell every ID in your list is a duplicate... on that basis I'm not quite sure what you're looking for in terms of end result.

If you are saying you wish to create a unique listing from your parent list then perhaps:

Please Login or Register  to view this content.

ITs Working... YESSSS

hehe
Thank you very much.
I do understand the second code with the &""
But i do not understand the first with the LARGE. what does it do? How does it work.

Best regards
Thank you

Given your IDs are numeric we can use LARGE to retrieve the ID numbers.
In this particular example we use COUNTIF to determine "k" such that we only retrieve unique numbers.

Simplified example:

Please Login or Register  to view this content.

so we have 5 unique values (10,9,8,7,5)

B1 we leave blank or enter text then in B2 we use:

Please Login or Register  to view this content.

Using B2 as example - the COUNTIF will return 0 as no numbers in A1:A10 are greater than the blank/text so "k" is 1 (1+0).
B2 then returns the largest number in A1:A10

Using B3 as example - the COUNTIF will return 2 as 2 numbers >= 10 so "k" becomes 3 (1+2)
B3 then returns the third largest number in A1:A10 which is 9

and so on and so forth and there we have a set of unique numbers

(we add some handlers in the real example such that we don't get errors whilst simultaneously avoiding calculating [potentially expensive] formulae unnecessarily)

Nice, now that i understand the formula i am trying to do the same again for the "Equipas" (Teams) based on another sheet where i have the ID and the TEAM. (CQ1 or CQ2).

I am doing the same formula but it does not work again... and i am using the &"" to use the result of the cell.

Here are the prints of each cell:
The last print shows the steps and we can see that he is using the formula and not the result, is this why its not working?

Best regards

sorry, slipped through the net.

Are the values on DB sheet numbers stored as text or numbers ?

A quick way to check - what does:

Please Login or Register  to view this content.

return ?

and what does:

Please Login or Register  to view this content.

return ?

The fact that COUNTIF returns 0 implies simply that the B3 value does not exist in Column A of DB sheet - neither in text nor number form
(the COUNT = 0 test also means we can be 100% sure that the value is not going to be stored in number form)

If you're adamant that the value (B3) does exist in Column A on DB sheet (please check) then...

Given all entries in DB!A:A are text (COUNT=0) this means there could be trailing spaces involved.
Find the cell in DB!A:A that you think matches B3 criteria and run a LEN test on the matching cell - compare that to the LEN of B3 and report back.

The values are seemingly in Col E rather than Col A on the DB sheet.

I suspect you are looking to return Col A where Col E = criteria, correct ?

Please Login or Register  to view this content.

you can also use:

Please Login or Register  to view this content.

but we wouldn't really recommend it.

Note: you will still get #N/A errors where the items don't exist, if you would like to "catch" those and replace with a default string then you can use COUNTIF

Please Login or Register  to view this content.

Bear in mind I can't see the file you're working on at this moment but...

The VLOOKUP you had - which was A:E would always return #N/A irrespective of whether or not you coerced the numbers stored as strings in DB!E:E simply because the criteria does not exist in DB!A:A

What you're describing is certainly not a bug - it's simply that if a cell is format as General and you paste in values stored as text they will remain as text.
If you re-enter the values manually XL will see they are numbers are given the General format will treat as a number - what was stored in the cell previously is of no concern.

Coercing the values would only make your formulae work if you're using B3 as the criteria and NOT B3&"" (ie you're searching for the number now rather than the string)

If you want to coerce the values in DB!E:E you can do them all at once by highlighting the column -> Data -> Text to Columns -> click Finish
(you can also select a blank cell, highlight all the number entries and run Paste Special -> Operation: Add)