Insert blank columns every other column and copy formula down to last cell - then move onto next blank column

9davros8

New Member
Joined
Jul 11, 2017
Messages
21
This is hard one – well for me anyway.

I won’t go into detail why I’m doing this but it’s because one database I use treats the same data in two different ways – apportioned and unapportioned. From this database I can extract two spread sheets. I need to populate both sheets with a common data field (cells) I can do a vlookup on. I have done this for one sheet and created a column consisting of ‘reference number’ + ‘date’ so it looks like this:
33ZF22DNNov 19kwh
(I know ‘date’ is a reserved name but this is just an example)

I now need to insert this same data into the second sheet using only the data on the second sheet to do this. The second sheet has Column B heading as ‘reference number’ and column C heading onwards in this format ‘Apr 20 kwh’ and the cells below each heading consist of numbers

So for example assume I have multiple rows of data and 10 columns. The columns have headings on Row 1.

Column A is name of property
Column B is a reference number
Column C is a date in this format ‘Apr 18 kwh’
Column D is ‘May 18 kwh’ and so on for all columns. (Columns vary in number and can contain up to 75 columns)

What I think I need to do is
1. Insert blank columns after Column C every other column. I have done this. Below is what I am having a problem with

2. In VBA find the first blank column (D) (there are no headings in the blank columns I have just inserted and Column D above is now Column E), in cell D2, put this formula in =$B2&$C$1 which is ‘reference number’ and ‘Apr 16 kwh’ which equates to 10108614Apr 16kWh

In Cell D3 this formula =$B3&$C$1 which is ‘reference number’ and ‘Apr 16 kwh’ and equates to 33ZF22HZApr 16kWh and so on to the end of my column of data.


3. I then need to loop through and find the next blank column (F) and in cell F2 paste this formula =$B2&$E$1, in cell F3 =$B3&$E$1 and so on until all blank columns and cells have been filled up using this formula.

I suspect I need to use R1C1 notation to do this as every column with a heading is different?

Basically I need to fill all the newly created columns - their cells, with a ‘reference number’ and ‘column heading’ to create unique records I can then use vlookup on.

Sorry if I have not explained it very well – it is a bit complicated for something that would be simple if the database I use treated the data in the same way – it does not and as it is an ‘off the shelf’ energy database there’s not a lot I can do about it

Any help appreciated
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,130,418
Messages
5,642,009
Members
417,250
Latest member
spr1nger

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
Top