Match data with multiple sheets and rearranging it

[Asfandyar Ali]

Dear Excel Ninjas.
I've attached 4 files - 2 x source files in typical format and 2 x destination files.
The source files are provided by our Engineering department and are intended for our literature team to put into PDF catalogs. My team requires this same data for importing into a SQL database in our computer software. Every time Engineering updates their data it's a big effort for us to rearrange it for our needs because the layout is not designed for a database. Can you please solve my problem that whenever revised data is received it can be automatically rearranged? In some instances cells need to be unmerged and values copied into newly empty cells. In other instances a string needs to be stripped, eg: from: "10 x 20 x 30 (5 x 7 x 9)" to: "10 x 20 x 30" and in other instances multiple cells need to be added together to insert just one value into the database.

Thanks for your help

 

[NARAYANK991]

Hi Ali ,

There is a lot to go through and understand ; please do not expect quick results.

If I have any questions to ask , I'll get back to you on this same thread.

Narayan

 

[Asfandyar Ali]

No problem sir. take your time.

Regards.
A.Ali

 

[Splash]

Hi, for repetitive tasks like this the macro recorder can be useful (best to write out each step first though). Plenty of help on this site.
I've attached a short section of code that will get you started on writing the code manually if you feel like giving that a go.
Good luck.

Sub TransferData()

'Use previous destination sheet and just overwrite previous data

Dim wsSce, wsDest As Worksheet
Dim i As Integer

Set wsSce = Worksheets("Source")  'Saved source data on sheet called "Source"
Set wsDest = Worksheets("Dest")  'Saved destination data on sheet called "Dest", both in same workbook

wsDest.Cells(2, 1).Resize(5) = wsSce.Cells(4, 1)  'Model Size

For i = 1 To 5
  wsDest.Cells(i + 1, 2) = 60  'Frequency
  wsDest.Cells(i + 1, 3) = Replace(wsSce.Cells(i + 3, 7), "V", "")  'Voltage - "V" removed
  wsDest.Cells(i + 1, 4) = Format(wsSce.Cells(i + 3, 20), "###")  'MCA - decimals removed
Next

End Sub