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