Modifying Data Format

22strider

Active Member
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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Input</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Item</td><td style=";">VendorID</td><td style=";">Vendor Part Number</td><td style=";">Vendor description</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12345</td><td style=";">V987</td><td style=";">ABCD</td><td style=";">sample vendor desc.</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12345</td><td style=";">V567</td><td style=";">WXYZ</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">45678</td><td style=";">V698</td><td style=";">EFGH</td><td style=";">sample vendor desc.</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">78956</td><td style=";">V987</td><td style=";">EHYC</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">OutPut</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Item</td><td style="text-align: right;;">12345</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">CrossReference</td><td style=";">V</td><td style=";">V987</td><td style=";">ABCD</td><td style=";">sample vendor desc.</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">CrossReference</td><td style=";">V</td><td style=";">V567</td><td style=";">WXYZ</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Item</td><td style="text-align: right;;">45678</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">CrossReference</td><td style=";">V</td><td style=";">V698</td><td style=";">EFGH</td><td style=";">sample vendor desc.</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Item</td><td style="text-align: right;;">78956</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">CrossReference</td><td style="text-align: right;;"></td><td style=";">V987</td><td style=";">EHYC</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">
 

Rick Rothstein

MrExcel MVP
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.

Rich (BB 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
 

22strider

Active Member
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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Item</td><td style=";">Item</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">CrossReference</td><td style=";">V</td><td style=";">VendorID</td><td style=";">Vendor Part Number</td><td style=";">Vendor description</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Item</td><td style="text-align: right;;">12345</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">CrossReference</td><td style=";">V</td><td style=";">V987</td><td style=";">ABCD</td><td style=";">sample vendor desc.</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">CrossReference</td><td style=";">V</td><td style=";">V567</td><td style=";">WXYZ</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Item</td><td style="text-align: right;;">45678</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">CrossReference</td><td style=";">V</td><td style=";">V698</td><td style=";">EFGH</td><td style=";">sample vendor desc.</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Item</td><td style="text-align: right;;">78956</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">CrossReference</td><td style="text-align: right;;"></td><td style=";">V987</td><td style=";">EHYC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">
 

Rick Rothstein

MrExcel MVP
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top