Dataset organisation

[antsrik]

Hi All,

I have a dataset in the following format

-------WholesaleKP RepairsKP
1999Q1 29,935,196 2,753,918
1999Q2 28,822,636 2,661,801
1999Q3 27,588,453 2,668,333
1999Q4 30,280,338 2,774,898
2000Q1 30,803,697 2,903,654
2000Q2 29,384,567 2,986,720
2000Q3 27,896,545 3,030,622
2000Q4 30,642,902 3,034,591



I would like to organise as

WholesaleKP
---------------Q1______ Q2
1999
2000

RepairsKP
---------------Q1______ Q2
1999
2000
I am using office 2007

Thanks

 

[hiker95]

antsrik354,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

We can not tell what worksheets, cells, rows, columns, your raw data, and/or results should be in.


So that we can get it right the first time:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[antsrik]

Hi All,

See attached document, I would like to get the first table in the format of the second and third table using excel functions.

I am using excel 2007.

https://app.box.com/s/782ohorbf9y2bfyzpljh

 

[hiker95]

antsrik354,

Thanks for the workbook.

Instead of showing the raw data and results like this:

Excel 2007
	A	B	C	D	E
1		WholesaleKP	RepairsKP
2	1999Q1	1,496,760	137,696
3	1999Q2	1,441,132	133,090
4	1999Q3	1,379,423	133,417
5	1999Q4	1,514,017	138,745
6	2000Q1	1,540,185	145,183
7	2000Q2	1,469,228	149,336
8	2000Q3	1,394,827	151,531
9	2000Q4	1,532,145	151,730
10	2001Q1	1,544,047	151,236
11	2001Q2	1,459,776	151,539
12	2001Q3	1,389,803	152,542
13	2001Q4	1,389,803	152,542
14
15
16	WholesaleKP	Q1	Q2	Q3	Q4
17	1999	1,496,760	1,441,132	1,379,423	1,514,017
18	2000	1,540,185	1,469,228	1,394,827	1,532,145
19	2001	1,544,047	1,459,776	1,389,803	1,389,803
20
21
22	RepairsKP	Q1	Q2	Q3	Q4
23	1999	137,696	133,090	133,417	138,745
24	2000	145,183	149,336	151,531	151,730
25	2001	151,236	151,539	152,542	152,542
26
Sheet1

How about this?

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		WholesaleKP	RepairsKP		WholesaleKP	Q1	Q2	Q3	Q4		RepairsKP	Q1	Q2	Q3	Q4
2	1999Q1	1,496,760	137,696		1999	1,496,760	1,441,132	1,379,423	1,514,017		1999	137,696	133,090	133,417	138,745
3	1999Q2	1,441,132	133,090		2000	1,540,185	1,469,228	1,394,827	1,532,145		2000	145,183	149,336	151,531	151,730
4	1999Q3	1,379,423	133,417		2001	1,544,047	1,459,776	1,389,803	1,389,803		2001	151,236	151,539	152,542	152,542
5	1999Q4	1,514,017	138,745
6	2000Q1	1,540,185	145,183
7	2000Q2	1,469,228	149,336
8	2000Q3	1,394,827	151,531
9	2000Q4	1,532,145	151,730
10	2001Q1	1,544,047	151,236
11	2001Q2	1,459,776	151,539
12	2001Q3	1,389,803	152,542
13	2001Q4	1,389,803	152,542
14
Sheet1

 

[antsrik]

Thanks hiker95 but can you assist me with a function to get the data from table 1 in a format like table 2 & 3, this is just an extract from a large data set

 

[hiker95]

antsrik354,

1. Table1 is in range A1:C13?

Probably because of the length of Table1:

2. You could have Table2 in range E1:I4?

3. And, Table3 in range K1:O4?

 

[antsrik]

Table 2 &3 does not exist I just created that as an example, I want to pull the data from table 1

 

[hiker95]

antsrik354,

Table 2 &3 does not exist I just created that as an example, I want to pull the data from table 1

1. I assume that Table2 and Table3 should look like your screenshot?

2. Do you want Table 2 & 3 to be in the same worksheet as Table 1?

3. Do you want Table2 to be in its own worksheet? And, what should the worksheet name be?

4. Do you want Table3 to be in its own worksheet? And, what should the worksheet name be?

5. Do you want both Table2 & 3 in the same worksheet? And, what should the worksheet name be?

 

[antsrik]

yeah, table 2 and 3 is what I want from 1 I built it manually as an example.

both table 2 and 3 on the same worksheet and the name of the worksheet does not matter

 

[hiker95]

antsrik354,

Just to clarify some points before I start.

1. Approximately how many rows are there in the raw data worksheet?

2. This is what your raw data looks like, just more rows? And, each year contains Q1, Q2, Q3, and, Q4?

Excel 2007
	A	B	C
1		WholesaleKP	RepairsKP
2	1999Q1	1,496,760	137,696
3	1999Q2	1,441,132	133,090
4	1999Q3	1,379,423	133,417
5	1999Q4	1,514,017	138,745
6	2000Q1	1,540,185	145,183
7	2000Q2	1,469,228	149,336
8	2000Q3	1,394,827	151,531
9	2000Q4	1,532,145	151,730
10	2001Q1	1,544,047	151,236
11	2001Q2	1,459,776	151,539
12	2001Q3	1,389,803	152,542
13	2001Q4	1,389,803	152,542
14
Sheet1

3. And, this is what your results should look like, in a new worksheet?

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	WholesaleKP	Q1	Q2	Q3	Q4		RepairsKP	Q1	Q2	Q3	Q4
2	1999	1,496,760	1,441,132	1,379,423	1,514,017		1999	137,696	133,090	133,417	138,745
3	2000	1,540,185	1,469,228	1,394,827	1,532,145		2000	145,183	149,336	151,531	151,730
4	2001	1,544,047	1,459,776	1,389,803	1,389,803		2001	151,236	151,539	152,542	152,542
5
Results