VLooKUP, Copying and Pasting, Several issues

[GN0001]

Hello Team,

I have two spread sheets one has 404 rows and the other one has 14000 rows,

I have to copy and paste MemberID, DOB and contact members from larger sheet to smaller sheet.

This is how I have to take care of the task for the specific business purposes(business ruled task)


When I copy MemberID from larger sheet and add it to memberID column from member sheet, not everything is copied and pasted, if a member is like M234566, only M is pasted.


I do a VLookUp function and bring all the values of MemberID to the smaller sheet. Then I copy and paste the result as value.


My workbook becomes extremely slow, I am accused of putting formulas there. I explained that I have replaced the result of formulas with values. But nobody accept it.


Issues:

How can I copy and paste the member IDs that have alphabets and numbers and get the every member ID pasted, a memberID looks like M22223


I want to find the new members. My supervisor does this with conditional formatting.


I did VLookUp to find the matching values. My VLookUp doesn't return all the values. I have used the istext function to validate the values in memberID is text. Then I put the function =Text(cellvalue, "0") to convert everything to text, I have cleared the formatting, but still my vlookup doesn't pull all the values. What should I do? However my supervisor found the duplicates with conditional formatting, there is around 44 matching values. With VLookUp, I only pull 19 records.


Any help?

GGGGG

 

[lhkittle]

Hi =REPT("G",5),


Can post a small sample of your data?

Post the vlookup formula you are using.


Where/what are the dulicates you speak of?


Returning the M only from a cell that has M234566 is indeed quite strange.


Regards,

Howard

 

[GN0001]

I partially handled the task:

I used code(cell) to get the number of the invisible character

I used substitute function to remove unwanted characters,

=substitute(text, char( ),"")

Then my VLookUp returned value.

however, I did the same thing today and still I received a lot of #N/A

How can i fix this problem?

My values are:

Sheet 1: Sheet2:

MM100098 MM100098

000456 000456

RR34567 RR34567

Regards

GGGGG