Find if value already exists on separate worksheet, regardless of format (number, text, etc.)

[jodeyes]

Example:
Worksheet2, ColA, is a list of User IDs that are created via a formula that combines data from other cells in the same worksheet.
Worksheet2, ColA, is a list of IDs for existing account holders.

What I did:
I created a vlookup formula to confirm whether or not the new ID that is submitted on Worksheet1 already exists on Worksheet2, but have to do so many steps first that I think it needs a macro.

When I paste in my content from a data report to Worksheet2, I run a text-to-columns on ColA to get it in the right format. Then I copy and paste as values the User IDs on Worksheet1 and run text-to-columns on that data. Then my vlookup formula works.

Sigh...

I know there's a better way, but not sure how to get there.

 

[mse330]

Hi jodeyes,

Could you post some sample data & expected results after the macro ?

 

[hiker95]

jodeyes,

It is always easier to help and test possible solutions if we could work with your actual file.

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.

 

[jodeyes]

Thanks, hiker95. I did not know that I could share a file that way. Here's the link to the file and a more detailed (and clear, I hope!) description of what I'm trying to do:

https://www.dropbox.com/s/mt96xrrpjg6102m/User ID Template.xlsm?dl=0

On the NEW HIRE FORM: Col N verifies whether or not the User ID in Col A has been used before. It is a VLOOKUP against Col A in the Verify worksheet. The problem is that Col A is a formula and not a value, so the Verify lookup will not work as intended.

For example, in cell N10, the lookup is working (see item in red, with pink highlighting). The other two IDs in M8 and M9 are also both valid values that are located on the Verify tab, but the lookup in N8 and N9 is not working because of formatting.

I can either manually copy and paste as values Col A on the NEW HIRE FORM tab, or copy Col A and paste as values in Col M and then do the lookup. I'd like to find a more elegant solution where everything can be done in a formula or macro.

The other problem I was running into is inconsistent format that makes the lookup not work. For example, I bring the data on the Verify tab in from another source. In order to get the lookup to work, I have to run Text-to-Columns to get the format to be General instead of text. Then, I have to do the same thing on Col A on the NEW HIRE FORM tab (or on Col M, if I use that for the lookup).

I know that I am doing too many steps and there is a better way, but I just don't know how to get there.

Thank you in advance for the help!

 

[hiker95]

jodeyes,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.


And, then in your next reply also post your macro code using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[jodeyes]

Thanks again, hiker95. Here is the file without macros.

https://www.dropbox.com/s/6gbhfjtkjgl3803/User ID Template.xlsx?dl=0

I am actually not using macros in this file. It was originally intended to be sent to users who do not have the ability to work with macro-enabled worksheets, so I was trying to get everything to work without VBA. We have since switched directions, but the file only contained one macro to unhide all worksheets at one time, which was a requirement of the old methodology, but not how we're going to use the file going forward.

Please let me know if the link works for you now, and thanks again!

 

[Fluff]

If you keep col A as a formula & use this in col N
=VLOOKUP(A8,Verify!A:A,1,FALSE)

It works with colA on Verify as text.

 

[jodeyes]

Oh man, was it really that simple? I guess every other time I've done this type of lookup, I have highlighted the whole column ("A:A") in the formula, but I guess that doesn't work when you have text in the first few rows. Thank you, Fluff, for the answer, and to hiker95 for teaching me how to post items correctly on this forum! Much appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback

For reference If col A on verify was numbers rather than text, you could have used
=VLOOKUP(VALUE(A8),Verify!A:A,1,FALSE)
instead

 

[jodeyes]

Cool - thanks!