Excel Copy Data Rearrange Paste

Microsoft Microsoft excel 2007 full vers...
March 31, 2010 at 04:51:02
Specs: Windows 7
I have a column of data that is sorted by the date. I want to create a formula to have this data pulled into another sheet but the data sorted by the date in multiple columns not just the single column.


1/1/01 2
1/1/01 5
1/1/01 6
1/2/01 7
1/2/01 8

need data to look like

1/1/01 2 1/2/01 7
1/1/01 5 1/2/01 8
1/1/01 6

See More: Excel Copy Data Rearrange Paste

March 31, 2010 at 04:55:44
re: "I want to create a formula... "

We all want something.

Posting tip:

If you are asking for help, try actually asking as opposed to just telling us what you want.

Report •

March 31, 2010 at 06:26:24
I apologize as I am new to this site... did not mean to demand anything. If anyone would be willing to help me with my formula question it would be appreciated.

Report •

March 31, 2010 at 08:33:59

I am not quite sure how you want the data to be organized on sheet 2.
Is it option A or option B or something else:

	A		B			
1	01/01/01	2			
2	01/01/01	5			
3	01/01/01	6			
4	01/02/01	7			
5	01/02/01	8			
Sheet 2	Option A				
	A		B		C		D		E
	01/01/01	01/01/01	01/01/01	01/02/01	01/02/01
	2		5		6		7		8
Sheet 2	Option B				
	A		B	C		D	
	01/01/01	2	01/01/01	5	etc.

When posting cells use the <pre> and </pre> tags that you can find above the reply box.
It helps line-up the columns.


Report •

Related Solutions

March 31, 2010 at 09:06:54
I would like for my sheet 2 to have columns A (1/1/01); B (1/1/01 data; C (1/2/01); D (1/2/01 data)

Report •

April 4, 2010 at 09:43:04

Start by creating column headings for each date on the new worksheet Sheet2, using alternate columns. The column headings must be on row 1 and must use the actual dates you have on Sheet1.

Here is one way to get a list of the dates to make the column headers:
Go to column A with the dates and select all the dates and one row above the first date with a heading, e.g. Dates
From the Menu select Data - Filter - Advanced filter ...
In the dialog box that opens, select 'Copy to another location' and check 'unique records only'
Use the 'Copy to:' box to select a single cell in an empty column (it has to be on the same worksheet)
Click OK

Now select the new list of dates with header and from the menu select Data - Sort ... and in the dialog box that opens select your header 'Date' in the first 'Sort by' box. Click on 'Header row' under 'My data range has'.
Click OK.

Now select the sorted dates and right-click and Copy.
Go to Sheet2.
Select a cell A1 and right-click and Paste Special ...
Select Transpose and click OK.

You now have the column headers - just insert an empty cell between each date.

On Sheet1 insert a column before the dates.
Label this 'Rank' in cell A1.
In cell A2 enter this formula:

Note the $ signs - they are required to maintain the correct counting when this formula is extended.

Now drag this formula in cell A2, down alongside all the dates now in column B.
(Format column A as a number so as not to confuse it with the dates).

Go to sheet2.
In Cell A2 (the cell below your first date heading), enter


For both this formula and the next one, adjust the row number of the ranges to cover all the rows used on Sheet1 (I used $B$30 and $C$30 for my example).

Drag this formula down at least as many rows as the maximum number of entries for any one date on Sheet1. (If you had 20 entries for 01/02/01 and this was more than for any other date, drag the formula down 20 rows - or more if you plan on adding data to Sheet1).

In cell B2 enter this formula:

Drag the formula down the same number of rows as you did for cell A2.

Now select cell A2 to the last used row in column B, e.g. cell B20.
Right-click and Copy.
Goto cell C2 and Paste
Repeat the paste for the cell in row 2 under each date heading.

All the dates and their data will now appear on sheet2 in the format you requested. Here is part of my test output:

01/01/01		01/02/01		01/03/01	
01/01/01	2	01/02/01	7	01/03/01	8
01/01/01	5	01/02/01	8	01/03/01	10
01/01/01	6				01/03/01	13
						01/03/01	18
						01/03/01	20
						01/03/01	21

The data on sheet 1 does not need to be sorted into date order for this to work.


Report •

Ask Question