?
Solved

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

Posted on 2014-08-14
9
Medium Priority
?
273 Views
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:

Capture.PNG
0
Comment
Question by:Hakum
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

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

Author Comment

by:Hakum
ID: 40260561
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
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40260589
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:Hakum
ID: 40262523
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
 
LVL 1

Author Comment

by:Hakum
ID: 40272370
any1?
0
 
LVL 11

Expert Comment

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

Author Comment

by:Hakum
ID: 40278674
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
 
LVL 11

Accepted Solution

by:
Dany Balian earned 2000 total points
ID: 40279751
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
 
LVL 1

Author Closing Comment

by:Hakum
ID: 40285453
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

840 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