VBA to Data Cleanse Records

[tshumba]

Hi

I have 2 data sets. One is a clean Data set with 2 columns, ID and Surname (e.g. 10101, Smith). The other data set is dirty. It'll have a string in one cell (e.g. "Smith James 10101" or "10101 Smith" or "Smith10101" or "10101Smith"). Quite a few possibilities.

Because of this, I obviously cannot do a straight vlookup. I need to check the clean data set (ID & Surname) to see if the free texted string received has both the Surname and ID in any order whereby the string might contain more than just the ID and Username (e.g. "Smith James 10101")

Thanks

 

[Marcelo Branco]

Welcome to Mr Excel

If the ID is always present in the second list, maybe doing a search by ID using wildcard characters. Something like


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

List1​

[/td][td]

Result​

[/td][td][/td][td]

List2​

[/td][td]

Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

10101, Smith​

[/td][td="bgcolor:#D9D9D9"]

10​

[/td][td][/td][td]

Smith10101​

[/td][td]

10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

10102,ABCDE​

[/td][td="bgcolor:#D9D9D9"]

14​

[/td][td][/td][td]

XYFGR xxxx 10103​

[/td][td]

12​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

10103,XYFGR​

[/td][td="bgcolor:#D9D9D9"]

12​

[/td][td][/td][td]

xxxx 10102 ABCDE​

[/td][td]

14​

[/td][/tr]
[/table]

Formula in B2 copied down
=VLOOKUP("*"&LEFT(A2,SEARCH(",",A2)-1)&"*",D:E,2,0)

Hope this helps

M.