Modifying Data Format

22strider

Active Member
Joined
Jun 11, 2007
Messages
311
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
Sheet1
<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">


Excel 2010
ABCDE
11OutPut
12Item12345
13CrossReferenceVV987ABCDsample vendor desc.
14CrossReferenceVV567WXYZ
15Item45678
16CrossReferenceVV698EFGHsample vendor desc.
17Item78956
18CrossReferenceV987EHYC
Sheet1
<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">
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
Sheet1
<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">
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top