Excel find data in same row but diffrent columns, and insert it into other cell

Hi guys,

I'm struggling with some Excel formula I'm not quite sure how to do it, hopefully someone can help...

I have a range of 5-10 columns with data that's not inserted equally but mixed up, i need to find a function that will be an addition to vlookup.

Usernr. will be the common denominator, but how do it make it find the different values in different rows?

like this:

Capture.PNG
LVL 1
HakumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dany BalianCTOCommented:
the only way i can see this done is using vba code and regular expressions.

let's say you have 3 columns as in the screenshots


dim pattern1 as string, pattern2 as string, pattern3 as string
dim finalvalue1 as string, finalvalue2 as string, finalvalue3 as string
Function testRegex(ByVal s As String, ByVal Pattern As String) As Boolean
    Dim re, re_matches
    Set re = CreateObject("vbscript.regexp")
    re.Global = True
    re.IgnoreCase = True
    re.Pattern = Pattern
    Set re_matches = re.Execute(s)
    testRegex = re_matches.Count = 1
End Function

Function findFirstMatch(ByVal value1 As String, byval value2 As String, byval value3 As String, byval pattern As String) As String
    If testRegex(value1, pattern) Then
        findFirstMatch = value1
    ElseIf testRegex(value2, pattern) Then
        findFirstMatch = value2
    ElseIf testRegex(value3, pattern) Then
        findFirstMatch = value3
    Else
        findFirstMatch = "" 'none of the 3 columns match with the pattern
    End If
End Function

pattern1="B37[0-9]{4}"
pattern2="08\-[0-9]{3}"
pattern3="sms|mail|phone"

'then you have to loop every row:
'on each row, validate col1 with pattern1, pattern2, pattern3
'when there's a match save value into the corresponding value

'ex: for row1
col1="b371234"
col2="sms"
col3="08-632"
value1=findFirstMatch(col1, col2, col3, pattern1)
value2=findFirstMatch(col1, col2, col3, pattern2)
value3=findFirstMatch(col1, col2, col3, pattern3)

Open in new window


then save your value1, value2, value3 to the desired columns (try to save them on other sheet, or other columns
0
HakumAuthor Commented:
I'm sure i quiet get how to use this vba? I'm sorry i'm no thtat familar with VBA, bus i'm trying to learn..

Its not neccesary that cost coloumn is always 08-6xx, at times it can be 6325 or 6325-4.....

how do i define where to put what i can seem to figur out of that from you code... and it's looking the values in the other cells with a Vlookup.. and the data is not on the same sheet..
0
Dany BalianCTOCommented:
first, with my code, you need a matching pattern, to be able to identify the data.. there's no way for the system to guess which column is in which place without an exact pattern match.

second, my code is not complete yet, it's just a base to start with because i have no clue about your excel files structures

third, the code is vba code that you type in the vba interface of excel (by typing alt+f11) but still, the code is not complete.. can you just attach a sample excel file, to see what you're talking about?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HakumAuthor Commented:
I completely understand its important to find a common denominator, or a pattern what to search for it's easy enough for 2 of them.

The User ID always starts with B37 and the Phone always starts with +01, but the Cost center is a bit more trick.

Please see the sample file attach for getting a better understanding, thank you soooo much for all the time and help, its really appreciated!!!
Sample-file.xlsx
0
HakumAuthor Commented:
any1?
0
Dany BalianCTOCommented:
ok try this.. i've made a sample excel file.. but the problem is that i am working on excel for mac, so although the vba code, can be written, i cannot use regex script on mac.

the file now contains a button on the main window. click it for results.
note that:
- you have to enable macros for the code to work.
- to see the written code just press on alt + f11

i hope it's bug free, coz i wrote it all without test
expertsexchange.xlsm
0
HakumAuthor Commented:
Thank you sooo much!!!! you rock!!!

But sadly it's not working, i have tried to google Regex abit and try to debug where it might have gone wrong but no luck.

This is what the debug says:

debug
0
Dany BalianCTOCommented:
this run time error means that the regular expression pattern used is incorrect..  try some regular expression testers..
try to run the testregex function alone..
example: msgbox testregex("email","email|phone|sms")
this tests if the first part is either email or phone or sms
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HakumAuthor Commented:
I tried everything.. :( and i can't get it to work... i'm giving up.. but thank you soo much for all of you time and effort!! its eall appriciated!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.