Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Worksheet re-arrangement
I have a Quickbooks report exported to Excel in the form of a multi-column
table. It takes the form of columns A to E being grouped nominal codes and columns F to X bening cost-centres. What I need to do is bring the figures into one vertical column. So there would be the nominal codes repeated vertically for each cost-centre with all the figures in column F. Is there an easy way to do this please without all the cutting and pasting which could lead to terrible mistakes? Thanks in advance. Malcolm Brown |
#2
|
|||
|
|||
Malcolm,
Run the macro below, and when asked, answer 5... But, the figures will actually go into column G: column F will be the header values from the top row. HTH, Bernie MS Excel MVP Sub MakeDataTableFromCrosstab2() 'By Bernie Deitrick Dim myCell As Range Dim newSheet As Worksheet Dim mySheet As Worksheet Dim i As Long Dim j As Integer Dim k As Long Dim l As Integer Dim mySelection As Range Dim RowFields As Integer Set mySheet = ActiveSheet Set mySelection = ActiveCell.CurrentRegion RowFields = Application.InputBox( _ "How many left-most columns to treat as row fields?", _ "CrossTab to DataBase Helper", 1, , , , , 1) On Error Resume Next Application.DisplayAlerts = False Worksheets("New Database").Delete Application.DisplayAlerts = True Set newSheet = Worksheets.Add newSheet.Name = "New Database" mySheet.Activate i = 1 For j = mySelection(1).Row + 1 To _ mySelection(mySelection.Cells.Count).Row For k = mySelection(1).Column + RowFields To _ mySelection(mySelection.Cells.Count).Column If mySheet.Cells(j, k).Value < "" Then For l = 1 To RowFields newSheet.Cells(i, l).Value = _ Cells(j, mySelection(l).Column).Value Next l newSheet.Cells(i, RowFields + 1).Value = _ Cells(mySelection(1).Row, k).Value newSheet.Cells(i, RowFields + 2).Value = _ Cells(j, k).Value i = i + 1 End If Next k Next j End Sub "Malcolm Brown" wrote in message ... I have a Quickbooks report exported to Excel in the form of a multi-column table. It takes the form of columns A to E being grouped nominal codes and columns F to X bening cost-centres. What I need to do is bring the figures into one vertical column. So there would be the nominal codes repeated vertically for each cost-centre with all the figures in column F. Is there an easy way to do this please without all the cutting and pasting which could lead to terrible mistakes? Thanks in advance. Malcolm Brown |
#3
|
|||
|
|||
Bernie Deitrick wrote:
Run the macro below, and when asked, answer 5... Wow, thanks very much. It's about 20 years since I used Basic and I hadn't realised that macros could be written like this. I must pick it up again. Thanks again Malcolm Brown |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copyright and worksheet protection | Excel Discussion (Misc queries) | |||
Executing macro for all worksheet from a different worksheet | New Users to Excel | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel | |||
Linking items GREATER THAN O on another worksheet in the same Work | Excel Discussion (Misc queries) | |||
Worksheet name and Backward compatibility | Excel Discussion (Misc queries) |