Results 1 to 10 of 10

Thread: reformat

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    reformat

    hi all,
    i have the attached data sample (input) that i want to reformat (output) as depicted in the attachment.
    Attached Files Attached Files
    TIA
    dubdub

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I don't believe this is a click and the desired output is created.

    I copied and pasted the names four times; then copied and transposed the categories, then sorted only the categories.
    While this listed DI, FRE, ORI in that order, all of them are there.

    I put this new arrangement starting in T9 (I didn't want to overwrite your result). Then, in V9 (V8 has 2013 in it), I wrote:

    =VLOOKUP($T9,$A$2:$E$6,MATCH($U9,$A$2:$E$2,0),FALS E)

    and filled down. I did a similar thing in the other columns for years.

    That was my quick & dirty approach to get it done ASAP. There might be a more clever way...

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try this. I got slightly different than your output. I have MACD-125 (FRE), Fre be the the first 1,1,1,4, I have blanks for the KORB-105 (FRE) in the output. I beleive that my macro matches the source better than your output. If I am wrong, you may have to elaborate on the logic you use.

    Code:
    Option Explicit
    Sub ConvertText()
      Dim wSource As Worksheet
      Dim lRowSource As Long
      Dim iCat As Integer
      Dim lLastRow As Long
      Dim wDest As Worksheet
      Dim lRowDest As Long
      Dim iYear As Integer
      Dim iCount As Integer
      Dim dTotal As Double
      Dim dTemp As Double
      
      Set wSource = Activesheet
      lLastRow = wSource.Cells(wSource.Rows.Count, 1).End(xlUp).Row
      Set wDest = Worksheets.Add
      'Header row
      lRowDest = 1
      With wSource
        wDest.Cells(lRowDest, 1) = "NAME"
        wDest.Cells(lRowDest, 2) = "CATEGORY"
        For iYear = 1 To 4
          wDest.Cells(lRowDest, 2 + iYear) = .Cells(1, (iYear - 1) * 4 + 2)
        Next iYear
        wDest.Cells(lRowDest, 7) = "Total"
        'other rows
        For iCat = 1 To 4
          For lRowSource = 3 To lLastRow
            lRowDest = lRowDest + 1
            'Get name
            wDest.Cells(lRowDest, 1) = .Cells(lRowSource, 1)
            'get Category
            wDest.Cells(lRowDest, 2) = .Cells(2, (iCat - 1) * 4 + iCat + 1)
            'get years
            iCount = 0
            dTotal = 0
            For iYear = 1 To 4
              If Not IsEmpty(.Cells(lRowSource, (iYear - 1) * 4 + iCat + 1)) Then
                dTemp = .Cells(lRowSource, (iYear - 1) * 4 + iCat + 1)
                wDest.Cells(lRowDest, 2 + iYear) = dTemp
                dTotal = dTotal + dTemp
                iCount = iCount + 1
              End If
            Next iYear
            If iCount <> 0 Then
              wDest.Cells(lRowDest, 7) = dTotal
            End If
          Next lRowSource
        Next iCat
      End With
    End Sub
    Hope this is what you are after.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you Steve, and my sincere apology for the late reply. i will give it a try.
    TIA
    dubdub

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    dubdub,

    Very challenging. Here is a bit of a different approach where the data is stored in an array. This negates the need to write to a new sheet. It just converts the existing one. It is also set up to easily accommodate alterations in the layout such as different number of names, categories, and/or years by changing the values of the 5 variables in the setup section. I, also, have different results from your example (see image).

    HTH,
    Maud

    Code:
    Public Sub Reformat()
    Dim s() As Variant
    '----------------------------
    'SETUP
    StartRow = 2        'ROW THAT REFORMATTING WILL BEGIN
    YrStart = 2013      'FIRST YEAR OF CATEGORIES
    YrNum = 4           'NUMBER OF YEARS OF CATEGORIES
    NameNum = 4         'NUMBER OF NAMES
    CatNum = 3          'NUMBER OF CATEGORIES EXCLUDING TOTAL
    '----------------------------
    'GET DATA
    Total = 0: yrs = YrNum: Nnum = NameNum + 1 'INCLUDE HEADER ROW
    Cnum = ((CatNum + 1) * YrNum) + 1  'INCLUDE TOTAL FOR EACH YEAR TIMES # OF YEARS
    ReDim s(Nnum, Cnum)
    For I = 2 To Nnum + 1
        For J = 1 To Cnum: s(I - 1, J) = Cells(I, J): Next J
    Next I
    Cells.ClearContents
    '----------------------------
    'BUILD HEADER
    Cells(StartRow, 1) = "NAME"
    Cells(StartRow, 2) = "CATEGORY"
    For I = 3 To YrNum + 2
        Cells(StartRow, I) = YrStart: YrStart = YrStart + 1
    Next I
    Cells(StartRow, YrNum + 3) = "TOTAL"
    '----------------------------
    'REFORMAT
    For Col = 2 To CatNum + 2
        For Nnum = 2 To NameNum + 1
            yrs = 0: Total = 0: n = 0: StartRow = StartRow + 1 'INCREMENT LINE BEING WRITTEN
            Cells(StartRow, 1) = s(Nnum, 1): Cells(StartRow, 2) = s(1, Col) 'NAME AND CATEGORY
            For I = n To YrNum - 1
                Cells(StartRow, 3 + I) = s(Nnum, Col + yrs) 'ASSIGN CATEGORY
                Total = Total + s(Nnum, Col + yrs): yrs = yrs + YrNum 'TOTAL CATEGORIES
            Next I
            Cells(StartRow, YrNum + 3) = Total 'ASSIGN TOTAL
        Next Nnum
    Next Col
    End Sub
    Categories1.png

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This negates the need to write to a new sheet.
    I recommend, in these cases of rearranging data, that a new sheet is created and the original source data is maintained. The approach I used leaves the original unaltered. That allows the opportunity to review the results and after satisfactory results are obtained, that the source is deleted or possibly even archived if desired.

    Steve

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    i want to reformat (output) as depicted
    With code that writes to a new sheet and code that clears and rewrites to the existing sheet, perhaps an assumption was made that this was the only data that the sheet contains. This other data may linked data or referred to other cells while the may be sheet's module itself may contain coding as well. While the "writing to a new sheet" solution would result in data split between two sheets, rewriting to the same sheet could be limited to clearing only the range of data being restructured by changing the line Cells.ClearContents to a Range(x:y).Clearcontents. Even easier would be to simply changing the setup parameter StartRow=30 to write below it. Hence, the advantage of flexible coding over hard coding.

    Since the request was a reformat and the posted restructuring is on the same sheet, one can conclude that the same sheet be used. With all the countless code written in this forum that modifies/rearranges data, it is arcane that such a stance to maintain and archive originals is taken at this point in time.

    If the code is well tested to be sound there should be no need to verify the results nor archive duplicated data. Surely, however, this would be to the preference of the user

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the code is well tested to be sound there should be no need to verify the results nor archive duplicated data.
    And this is exactly my point. I know that the code I posted has NOT been "well tested", since it was only tested on a sample dataset. How representative this dataset is to the actual and what differences the actual dataset will have on the performance, is very much UN-tested. And thus I recommend, creating a sample output which allows the source data to remain untouched.

    Surely, however, this would be to the preference of the user
    Very true. It is only a recommendation I make based on my experiences.

    Steve

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    And thus I recommend, creating a sample output which allows the source data to remain untouched.
    Not to beat a dead horse but it seems more logical to experiment on copies of the original (such as the OP attachment) until well tested instead of writing the output to another sheet from the original as you suggest. Since you agree that the code is not fully tested, you run the risk of inadvertently making changes to the original. Additionally, once the code is perfected, you would then have to rewrite the code to copy it to the same sheet and test again. Yes, you could just keep the out put and delete the original providing that there were no additional data, formulas, formatting. Instead, your code could create a copy of the original and modify that. But isn't that what we are doing in test?

    If you were indicating to test the code on copies and not the original then apologies; I totally missed that.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I always recommend to work on copies. Just like I recommend making backups. But sometimes people don't take precautions.

    But the backup copies are most likely another workbook. Creating a new sheet allows testing the code on the same exact worksheet without having to pull up the copy each time the code is run and to compare the versions to each other and to the source.

    As I said it is just my recommendation, especially in examples of rearranging the data into a new structure. [in some cases, especially with manual operations, I will keep several intermediate versions in case something goes wrong and I can go back to a particular break point in the rearranging.]

    Steve

Posting Permissions

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