Results 1 to 5 of 5

Thread: Modifying Data Format
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Modifying Data Format

    Hello Friends,

    I am preparing data to be imported from Excel (2010) in to our MRP. The system requires data to be presented in certain format. Could you please help me with a code that would present data in the required format? Following table show the input table (present format of the data) and output table (required format).
    Information given in column D of Input is optional; it may or may not be present in the input file.

    Thanks,
    Rajesh

    Excel 2010
    ABCD
    2Input
    3ItemVendorIDVendor Part NumberVendor description
    412345V987ABCDsample vendor desc.
    512345V567WXYZ
    645678V698EFGHsample vendor desc.
    778956V987EHYC



    Excel 2010

    ABCDE
    11OutPut
    12Item12345
    13CrossReferenceVV987ABCDsample vendor desc.
    14CrossReferenceVV567WXYZ
    15Item45678
    16CrossReferenceVV698EFGHsample vendor desc.
    17Item78956
    18CrossReferenceV987EHYC


  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,318
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Modifying Data Format

    I think the following macro will do what you want. However, I was not clear on what row your Input data header was on, so I left it at 3 like you showed... to change it, just change the red highlighted number to the row number where your header actually is.

    Code:
    Sub ReformatData()
      Dim X As Long, LastRow As Long, Items As Range, Ar As Range
      Const InputHeaderRow As Long = 3
      LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      For X = LastRow To InputHeaderRow Step -1
        If Cells(X, "A").Value <> Cells(X - 1, "A").Value Then Rows(X).Insert
      Next
      LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      Set Items = Range("A" & InputHeaderRow & ":A" & LastRow).SpecialCells(xlConstants)
      For Each Ar In Items.Areas
        Ar(1).Offset(-1).Resize(, 4) = Array("Item", Ar(1), "", "")
        Ar.Resize(, 4).Cut Ar.Offset(, 1).Resize(, 4)
        Ar.Offset(, -1).Resize(, 2) = Array("CrossReference", "V")
      Next
      Cells(LastRow, "B").ClearContents
      Columns("A:E").AutoFit
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modifying Data Format

    Hello Rick,

    Thanks for the code; it works. My header row is row number 1. I tried running the code by replacing 3 with 1 but it gave me error. The other feedback is that the out put has additional set (2) rows on the top of the table. As shown in the table below, first 2 rows are not required.

    Thanks again;
    Rajesh

    Excel 2010
    ABCDEF
    3ItemItem
    4CrossReferenceVVendorIDVendor Part NumberVendor description
    5Item12345
    6CrossReferenceVV987ABCDsample vendor desc.
    7CrossReferenceVV567WXYZ
    8Item45678
    9CrossReferenceVV698EFGHsample vendor desc.
    10Item78956
    11CrossReferenceV987EHYC


  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,318
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Modifying Data Format

    Quote Originally Posted by 22strider View Post
    Hello Rick,

    Thanks for the code; it works. My header row is row number 1. I tried running the code by replacing 3 with 1 but it gave me error. The other feedback is that the out put has additional set (2) rows on the top of the table. As shown in the table below, first 2 rows are not required.
    Okay, knowing for sure that your header row is 1 helps. Give this modified code a try...

    Code:
    Sub ReformatData()
      Dim X As Long, LastRow As Long, Items As Range, Ar As Range
      LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      For X = LastRow To 3 Step -1
        If Cells(X, "A").Value <> Cells(X - 1, "A").Value Then Rows(X).Insert
      Next
      LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      Set Items = Range("A2" & ":A" & LastRow).SpecialCells(xlConstants)
      For Each Ar In Items.Areas
        Ar(1).Offset(-1).Resize(, 4) = Array("Item", Ar(1), "", "")
        Ar.Resize(, 4).Cut Ar.Offset(, 1).Resize(, 4)
        Ar.Offset(, -1).Resize(, 2) = Array("CrossReference", "V")
      Next
      Cells(LastRow, "B").ClearContents
      Columns("A:E").AutoFit
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modifying Data Format

    Thank you very much; worked like a charm

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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