+ Reply to Thread
Results 1 to 7 of 7

Transform Column to several columns depending on condition.

Hybrid View

dimitrov_dimityr Transform Column to several... 10-14-2013, 04:47 AM
sktneer Re: Transform Column to... 10-14-2013, 07:01 AM
dimitrov_dimityr Re: Transform Column to... 10-14-2013, 07:31 AM
OllieB Re: Transform Column to... 10-14-2013, 07:36 AM
nilem Re: Transform Column to... 10-14-2013, 07:41 AM
OllieB Re: Transform Column to... 10-14-2013, 07:45 AM
dimitrov_dimityr Re: Transform Column to... 10-14-2013, 08:11 AM
  1. 10-14-2013, 04:47 AM #1
    dimitrov_dimityr is offline
    Registered User
    Join Date
    10-12-2013
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Transform Column to several columns depending on condition.

    Greetings,

    I would like to ask if anyone had met such export and managed to achieve proper transformation.
    Basicly i have in Col A several criteria which i need to separate in columns without losing the infos and numbers which are related to them.

    (like in column M to P)
    The number will be different depending on the extraction i get for different requests.

    Thanks for your time
    BR
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-14-2013, 07:01 AM #2
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Transform Column to several columns depending on condition.

    Very confusing. What you are trying to do? Please explain in the sheet itself.
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  3. 10-14-2013, 07:31 AM #3
    dimitrov_dimityr is offline
    Registered User
    Join Date
    10-12-2013
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Transform Column to several columns depending on condition.

    Here. Hope this is clearer
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-14-2013, 07:36 AM #4
    OllieB is offline
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Transform Column to several columns depending on condition.

    dimitrov,

    so column A is provided and the number of spaces in front of the text determines the nesting level. For example 0 spaces is level 1 (should be written in column M), 3 spaces is level 2 (should be written to column N, and so forth?
    If you like my contribution click the star icon!
    Reply With QuoteRegister To Reply
  5. 10-14-2013, 07:41 AM #5
    nilem is offline
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Transform Column to several columns depending on condition.

    Hi Dimityr,
    try it
    Sub ertert()
Dim x, y(), i&, j&, k&
x = Range("A7", Cells(Rows.Count, 1).End(xlUp)).Value
ReDim y(1 To UBound(x), 1 To 4)
With CreateObject("vbscript.regexp")
    .Pattern = "^\s*"
    For i = 1 To UBound(x)
        k = Len(.Execute(x(i, 1))(0)) / 3 + 1
        y(i, k) = Trim(x(i, 1))
        For j = k - 1 To 1 Step -1
            y(i, j) = y(i - 1, j)
        Next j
    Next i
End With
Range("M7:P7").Resize(i - 1).Value = y()
End Sub
    option
    .Pattern = "\w+"
k = .Execute(x(i, 1))(0).firstindex / 3 + 1
    Last edited by nilem; 10-14-2013 at 07:56 AM. Reason: add option
    Reply With QuoteRegister To Reply
  6. 10-14-2013, 07:45 AM #6
    OllieB is offline
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Transform Column to several columns depending on condition.

    or (only outputting rows when a value for column P is found)

    Public Sub transformData()

'#
'# declare private variables
'#
   Dim pvt_lng_RowNumber As Long
   Dim pvt_str_Level_M As String
   Dim pvt_str_Level_N As String
   Dim pvt_str_Level_O As String
   Dim pvt_str_Level_P As String
   
'#
'# process all rows on Sheet1 - only output acolumns M to P when a value for column
'# P has been found, else just catch the parent levels
'#
   With ThisWorkbook.Worksheets(1)
      For pvt_lng_RowNumber = 7 To .Cells(.Rows.Count, "A").End(xlUp).Row
         If Left$(.Cells(pvt_lng_RowNumber, "A").Value, 9) = Space(9) Then
            .Cells(pvt_lng_RowNumber, "M").Value = pvt_str_Level_M
            .Cells(pvt_lng_RowNumber, "N").Value = pvt_str_Level_N
            .Cells(pvt_lng_RowNumber, "O").Value = pvt_str_Level_O
            .Cells(pvt_lng_RowNumber, "P").Value = Trim$(.Cells(pvt_lng_RowNumber, "A").Value)
         ElseIf Left$(.Cells(pvt_lng_RowNumber, "A").Value, 6) = Space(6) Then
            pvt_str_Level_O = Trim$(.Cells(pvt_lng_RowNumber, "A").Value)
         ElseIf Left$(.Cells(pvt_lng_RowNumber, "A").Value, 3) = Space(3) Then
            pvt_str_Level_N = Trim$(.Cells(pvt_lng_RowNumber, "A").Value)
         Else
            pvt_str_Level_M = Trim$(.Cells(pvt_lng_RowNumber, "A").Value)
         End If
      Next pvt_lng_RowNumber
   End With

End Sub
    Reply With QuoteRegister To Reply
  7. 10-14-2013, 08:11 AM #7
    dimitrov_dimityr is offline
    Registered User
    Join Date
    10-12-2013
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Transform Column to several columns depending on condition.

    Came up with a form with text boxes to input the number of spaces before each level of the export and rearange the column with these values. 10 was the biggest number of different spaces. 

    Private Sub doit_butt_Click()
Dim Last_cell_num, a, b As Long
Dim temp1, temp2, temp3 As String
Dim set_space_filter As String
Dim get_space As Integer
Dim lev1, lev2, lev3, lev4, lev5, lev6, lev7, lev8, lev9, lev10 As String
lev1 = "0"
temp1 = ""
temp2 = ""
temp3 = ""
Dim len1, len2 As Integer
Dim lev1_tmp, lev2_tmp, lev3_tmp, lev4_tmp, lev5_tmp, lev6_tmp, lev7_tmp, lev8_tmp, lev9_tmp, lev10_tmp As String
    
lev2 = levf_2.Text
lev3 = levf_3.Text
lev4 = levf_4.Text
lev5 = levf_5.Text
lev6 = levf_6.Text
lev7 = levf_7.Text
lev8 = levf_8.Text
lev9 = levf_9.Text
lev10 = levf_10.Text
    With ActiveSheet
        Last_cell_num = .Cells(.Rows.count, 1).End(xlUp).Row
    End With
    For a = 1 To Last_cell_num
        temp1 = ActiveSheet.Range("A" & CStr(a)).Value
        len1 = Len(temp1)
        len2 = Len(temp1) - Len(Trim(temp1))
        
        If lev10 <> "not available" Then
            If len2 = CInt(lev1) Then
                lev1_tmp = Trim(temp1)
                lev2_tmp = ""
                lev3_tmp = ""
                lev4_tmp = ""
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev2) Then
                lev2_tmp = Trim(temp1)
                lev3_tmp = ""
                lev4_tmp = ""
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev3) Then
                lev3_tmp = Trim(temp1)
                lev4_tmp = ""
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev4) Then
                lev4_tmp = Trim(temp1)
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev5) Then
                lev5_tmp = Trim(temp1)
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev6) Then
                lev6_tmp = Trim(temp1)
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev7) Then
                lev7_tmp = Trim(temp1)
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev8) Then
                lev8_tmp = Trim(temp1)
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev9) Then
                lev9_tmp = Trim(temp1)
                lev10_tmp = ""
            ElseIf len2 = CInt(lev10) Then
                lev10_tmp = Trim(temp1)
            End If
''''' this continues a lot....
        End If
        
        If lev10 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
                ActiveSheet.Range("BH" & CStr(a)).Value = Trim(lev8_tmp)
                ActiveSheet.Range("BI" & CStr(a)).Value = Trim(lev9_tmp)
                ActiveSheet.Range("BJ" & CStr(a)).Value = Trim(lev10_tmp)
        ElseIf lev10 = "not available" And lev9 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
                ActiveSheet.Range("BH" & CStr(a)).Value = Trim(lev8_tmp)
                ActiveSheet.Range("BI" & CStr(a)).Value = Trim(lev9_tmp)
         ElseIf lev9 = "not available" And lev8 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
                ActiveSheet.Range("BH" & CStr(a)).Value = Trim(lev8_tmp)
         ElseIf lev8 = "not available" And lev7 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
         ElseIf lev7 = "not available" And lev6 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
         ElseIf lev6 = "not available" And lev5 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
         ElseIf lev5 = "not available" And lev4 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
         ElseIf lev4 = "not available" And lev3 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
         ElseIf lev3 = "not available" And lev2 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
         End If
    Next



End Sub
    I will try with the sub you gave me.
    Any suggestions how to reduce the code? Couse i am just beggining to do such stuff and it probably looks terrible

    Damn it took me a lot of time to get this post....

    THANKS for the reply. It does work great.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. select only certain columns depending upon last row in column A
    By mickjjuk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-22-2013, 11:42 AM
  2. Copy formulas in 4 columns down depending of last use cell in another column.
    By Fotis1991 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-18-2012, 01:07 PM
  3. MACRO to transform single column to multiple columns, based in dynamic values
    By gaqueiroz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-08-2012, 01:54 PM
  4. Sum values in column, depending on two different columns value
    By avelinov in forum Excel General
    Replies: 1
    Last Post: 02-16-2012, 09:18 AM
  5. summe column depending of other columns
    By jokenjo in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-15-2007, 06:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1