Excel 2010: Macro Code to re-align data table

Question

Hi,

I need to realign a data table as shown below:

C denates that to clear the cell content.

1-7 are the position of the new column after moving

Insert is the bank column added.

To convert column 6 to positive by multiply it by -1.

Is there any Macro Code to perform this task?

Thank you in advance.

Answer 1

Hi,

I still need the VBA Codes as requested.

Is there anybody knowledgeable could help?

Thanks

Answer 2

Makes sense. But I strongly encourage you to look in to get and transform/PowerQuery, if not now, in the near future.  It WILL do everything you can with a macro, using a drag and drop and menu interface. Faster and Easier because you will immediately see the effects of your (recorded) actions.

Take a look at this article:

https://www.microsoft.com/en-us/microsoft-365/blog/2015/09/10/integrating-power-query-technology-in-excel-2016/

This page currently has 3 webinar replays. One is about macros.

https://www.myexcelonline.com/free-excel-webinars

But that site also periodically offers this webinar "Free Power Query & Data Cleansing Webinar" 

*  Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)... AND without using VBA or Macros!

  *  Data Cleansing Excel Tips & Special Bonus attendee material that you can keep and use to become better at Excel straight away, skyrocketing your personal & professional development!

  *  Unpivot – convert a report into raw data so that I is useful for analysis

  *  Consolidate multiple worksheets

You can skip most of it if you wish.

Actually, I've uploaded the webinar to my OneDrive. Here is the link to it:

https://1drv.ms/v/s!Am8lVyUzjKfph0yIrbWCIyvZByb2

If you are interested, download it, I've put a 3 day time limit on it.

Answer 3

Hi,

I need a macro because I perform this task repeatedly and frequently.

Running a well written Macro is the simplest and fastest way to do the job.

Thanks

Answer 4

Why do you want a macro. Is this something you want to repeat on more than worksheet or over time?

Macros are the "old school", manual approach. A more "automated" way is using Get & Transform (2016) / PowerQuery (2019 / 2013 / 2010).  You import data in to it, then do your manipulation, add columns, remove columns, reformat data etc and as you do that, EXCEL records your actions (creating "macros" in the background).  Later you can import more data and it will be manipulated in the same way.

Here are some tips using the feature.

@ Get & Transform in Excel 2016 Intro -  

https://support.office.com/en-us/article/Get-Transform-in-Excel-2016-881c63c6-37c5-4ca2-b616-59e18d75b4de

Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Get & Transform enables you to connect, combine, and refine data sources to meet your analysis needs. These features are also used in Power BI, and in the Power Query Add-In available for previous versions of Excel. Links to more info about using the feature.

@ Get & Transform- Pivotal Advance Boosts Excel’s Power – PowerPivot Enhances PivotTablehttp://www.excel-university.com/articles/journal-of-acct/pivotal-advance-boosts-excels-power/

September 2011             Jeff Lenning

In today’s accounting world, financial and operational data typically is stored in a variety of programs and formats. When accountants need to prepare a report based on data from various systems, the first step is to export the data into Excel. Typically, it is fairly easy to export the required data into Excel. But, depending on the structure or format of the data, or if multiple data tables need to be combined, it is not always easy to summarize the data in a single report.

That’s where a new Excel tool called PowerPivot comes into play. PowerPivot is a free plug-in from Microsoft that boosts the capabilities of the already popular PivotTable function, allowing you to create previously impossible PivotTables that enhance Excel’s efficiency and effectiveness. ET MR PivotTables.docx

@ Getting Started with Get & Transform in Excel 2016https://support.office.com/en-us/article/Getting-Started-with-Get-Transform-in-Excel-2016-a8310388-2a12-438c-9d29-c6d29cb8df6a

With Get & Transform in Excel 2016, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.

ET R 0 Ribbon.docx

@ Import and analyze dataImport data

https://support.office.com/en-us/article/Import-and-analyze-data-ccd3c4a6-272f-4c97-afbb-d3f27407fcde#ID0EAABAAA=Import_data

You can use Excel’s Get & Transform (Power Query) experience to connect to multiple data sources, then use the Query Editor to shape and transform data.

    Getting Started with Get and Transform

    Import data from external data sources

    View and Manage Queries in a Workbook

    Add a query to an Excel worksheet

    Introduction to the Query Editor

    Shape data

    Edit Query Step Settings