Column transform in excel

Question

0.00/5 (No votes)

See more:

Excel-2016

I have values in one sheet mentioned below. Col A has some Group names. Column B has Demo Values.

Col A	Col B
member{"cn=Group_A,OU=AA,DC=BB}", "cn=Group_B, OU=AB, DC=BB}", "cn=Group_C,OU=AA,DC=BB}"	Demo1
member {"cn=Group_B, OU=AA, DC=BB"}	Demo2
member {"cn=Group_A, OU=AA, DC=BB"}	Demo3
member {"cn=Group_C, OU=AA, DC=BB"}	Demo4
member {"cn=Group_D, OU=AA, DC=BB"}	Demo5

I want to separate the values and expecting the output mentioned below:

Col A	Col B
Group_A	Demo1, Demo3
Group_B	Demo1, Demo2
Group_C	Demo1, Demo4
Group_D	Demo5

What I have tried:

I tried to perform substring to match with the text and the tried to perform text join but it's not successful. Can anyone please help? This is really urgent.

Posted 2-Apr-24 2:46am

webmail123

Updated 2-Apr-24 11:41am

v2

Add a Solution

Comments

0x01AA 2-Apr-24 14:00pm

I would say: No chance without VBA

Maciej Los 2-Apr-24 16:43pm

You're right, Bruno :)

webmail123 2-Apr-24 17:43pm

Thanks for the solution. I really appreciate it. I just tweaked the value of column A. So we can ignore all other values in column A except value of "CN". How can I make that change in VBA Code? I tried to replace it with empty string but if you observe, values of OU and DC can be different for each row. But we just need to consider the value of CN

Maciej Los 3-Apr-24 13:50pm

You changed sample data. I'd suggest to use Replace function. Change my code to your needs :)

webmail123 3-Apr-24 13:51pm

I will do that. Thank you so much!!

Maciej Los 3-Apr-24 13:53pm

In case you'll get stuck, let me know.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2024-04-02T10:43:00

I'd suggest to use a dictionary object. See:

vba

Option Explicit

'needs reference to Mictosoft Scripting Runtime library
Sub RevertData()
    Dim srcWsh, dstWsh As Worksheet
    Dim i, k  As Long
    Dim sTmp As String
    Dim sValues As Variant
    Dim myDictionary As Dictionary
    
    'create dictionary object
    Set myDictionary = New Dictionary
    
    'define source sheet
    Set srcWsh = ThisWorkbook.Worksheets("Sheet1")
    'define destinate sheet
    Set dstWsh = ThisWorkbook.Worksheets("Sheet2")
    'start with row no. 2
    'counter - sheet 1
    i = 2
    Do While srcWsh.Range("A" & i) <> ""
        sValues = Split(srcWsh.Range("A" & i), ", ")
        For k = LBound(sValues) To UBound(sValues)
            'Debug.Print CStr(sValues(k))
            sTmp = sValues(k)
            sTmp = Replace(sTmp, "cn=", "")
            sTmp = Replace(sTmp, """", "")
            If Not myDictionary.Exists(sTmp) Then
                myDictionary.Add sTmp, CStr(srcWsh.Range("B" & i)) & ", "
            Else
                myDictionary(sTmp) = myDictionary(sTmp) & CStr(srcWsh.Range("B" & i)) & ", "
            End If
        Next k
        'increase counter
        i = i + 1
    Loop
    
    'counter - sheet 2
    i = 2
    For Each sValues In myDictionary.Keys
        dstWsh.Range("A" & i) = CStr(sValues)
        dstWsh.Range("B" & i) = myDictionary(CStr(sValues))
        i = i + 1
    Next
    
    Set dstWsh = Nothing
    Set srcWsh = Nothing
    
End Sub