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

Posted on 2014-08-14
Last Modified: 2014-08-26
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:

Question by:Hakum
    LVL 11

    Expert Comment

    by:Dany Balian
    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
            findFirstMatch = "" 'none of the 3 columns match with the pattern
        End If
    End Function
    '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
    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
    LVL 1

    Author Comment

    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..
    LVL 11

    Expert Comment

    by:Dany Balian
    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?
    LVL 1

    Author Comment

    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!!!
    LVL 1

    Author Comment

    LVL 11

    Expert Comment

    by:Dany Balian
    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
    LVL 1

    Author Comment

    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:

    LVL 11

    Accepted Solution

    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
    LVL 1

    Author Closing Comment

    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!!!!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Outlook Free & Paid Tools
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now