Anyone can suggest a faster way to do this?

[edlim85]

I have a long list of raw data that i have to prepare into a specific format for a very old machine to read.

I will try my best to explain clearly here. below is one of the sample table, i have about 600 of such tables. i will need to arrange the raw data(image1) into another format below (image2). The tedious part here is some tags(yellow column) have different corresponding coefficient be it coefficient 1, 2, or 3.


image1:

if the coefficient is the same i will not need to repeat.

image2:

can anyone help?

 

[edlim85]

Any suggestion?

 

[hiker95]

edlim85,

You are posting a picture(s). This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

 

[edlim85]

Hi hiker95,

thanks for letting me know. Sorry about it. I've just signed up for box net.

here is the link to my shared folder for the file:example.xls
http://www.box.net/shared/ri6bhq0hkn5zzf3yxpu9

I'm using excel 2007.

 

[hiker95]

edlim85,

Thanks for the workbook.

Are we making a new worksheet for each table?

Can I have another workbook with more than just one table (say 10)?

And, can the data be displayed as it actually exists.

 

[edlim85]

hi Hiker95,

I have uploaded another file on to boxnet.
http://www.box.net/shared/ri6bhq0hkn5zzf3yxpu9

Both ways are possible (new worksheet or to dump all in one worksheet). The tags differs from table to table(tu572, tu845 etc) and each tag needs to be coupled by the coefficients.
And, can the data be displayed as it actually exists.
Do you mean the formatting on the original worksheet?

 

[hiker95]

edlim85,

Can you supply another workbook, with the results from the Original worksheet for the entire range A4:J14 in worksheet TU572.

 

[hiker95]

edlim85,


Sample raw data in worksheet Sheet1 (only showing 39 out of the 181 rows):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Tables	From	To	Tag	Coefficient 1	coefficient 2	coefficient 3	Coefficient 4	Coefficient 5	Coefficient 6
2	TU564	20080201	20080301	HRSE1	1.05	NA	NA	NA	NA	NA
3		20080201	20080301	HRSE2	1.00	NA	NA	NA	NA	NA
4	TU572	20080201	20080301	A	0.0000	0.0000	0.0000	1.0500	1.0500	1.0500
5		20080201	20080301	B	0.0000	0.0000	0.0000	1.1340	1.1340	1.1340
6		20080201	20080301	C	0.0000	0.0000	0.0000	1.3098	1.3098	1.3098
7		20080201	20080301	D	0.0000	0.0000	0.0000	1.2155	1.1025	1.2155
8		20080201	20080301	E	0.0000	0.0000	0.0000	1.3365	1.0673	1.3365
9		20080201	20080301	F	0.0000	0.0000	0.0000	1.2052	1.0300	1.1585
10		20080201	20080301	G	0.0000	0.0000	0.0000	1.0000	1.0000	1.0000
11		20080201	20080301	H	0.0000	0.0000	0.0000	1.6157	1.6157	1.6157
12		20080201	20080301	I	0.0000	0.0000	0.0000	1.1340	1.1340	1.1340
13		20080201	20080301	J	0.0000	0.0000	0.0000	1.2359	1.1556	1.2359
14		20080201	20080301	K	0.0000	0.0000	0.0000	1.3256	1.3256	1.3256
15	TU575	20080201	20080301	0 - 0.8	0.0000	0.0000	0.0000	1.1783	1.0165	1.0165
16		20080201	20080301	0.81 - 1	0.0000	0.0000	0.0000	1.3647	1.1322	1.1655
17		20080201	20080301	1.01 - 1.2	0.0000	0.0000	0.0000	1.6042	1.3440	1.3709
18		20080201	20080301	1.21 - 1.5	0.0000	0.0000	0.0000	1.5568	1.3700	1.3974
19		20080201	20080301	1.51 - 2	0.0000	0.0000	0.0000	1.5754	1.4420	1.4420
20		20080201	20080301	2.01 - 2.5	0.0000	0.0000	0.0000	1.8343	1.5986	1.5986
21		20080201	20080301	2.51 - 3	0.0000	0.0000	0.0000	2.0918	1.8231	1.8231
22		20080201	20080301	3.01 - 5	0.0000	0.0000	0.0000	2.1088	2.0700	2.0700
23		20080201	20080301	5.01 - 7.5	0.0000	0.0000	0.0000	2.3760	2.3760	2.3760
24		20080201	20080301	7.51 - 10	0.0000	0.0000	0.0000	2.6220	2.6220	2.6220
25		20080201	20080301	10.01 - 15	0.0000	0.0000	0.0000	3.2079	3.2079	3.2079
26		20080201	20080301	15.01 - 20	0.0000	0.0000	0.0000	4.2000	4.2000	4.2000
27		20080201	20080301	20.01 - 30	0.0000	0.0000	0.0000	4.9000	4.9000	4.9000
28		20080201	20080301	30.01 - 40	0.0000	0.0000	0.0000	5.7000	5.7000	5.7000
29		20080201	20080301	40.01 - 50	0.0000	0.0000	0.0000	6.5000	6.5000	6.5000
30		20080201	20080301	50.01 - 60	0.0000	0.0000	0.0000	7.0600	7.0600	7.0600
31		20080201	20080301	60.01 - 70	0.0000	0.0000	0.0000	7.2400	7.2400	7.2400
32		20080201	20080301	70.01 - 80	0.0000	0.0000	0.0000	7.4200	7.4200	7.4200
33		20080201	20080301	80.01 - 90	0.0000	0.0000	0.0000	7.6000	7.6000	7.6000
34		20080201	20080301	90.01 - 100	0.0000	0.0000	0.0000	7.7900	7.7900	7.7900
35		20080201	20080301	100.01 - 110	0.0000	0.0000	0.0000	7.9900	7.9900	7.9900
36		20080201	20080301	110.01 - 120	0.0000	0.0000	0.0000	8.1900	8.1900	8.1900
37		20080201	20080301	120.01 - 130	0.0000	0.0000	0.0000	8.3900	8.3900	8.3900
38		20080201	20080301	130.01 - 140	0.0000	0.0000	0.0000	8.6000	8.6000	8.6000
39		20080201	20080301	140.01 - 150	0.0000	0.0000	0.0000	8.8200	8.8200	8.8200
Sheet1

After the macro in a new worksheet Results (only showing thru column N. The last column is column HR):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	From	To	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient
2	20080201	20080301	HRSE1	1.05	HRSE2	1
3	20080201	20080301	A	1.05	B	1.134	C	1.3098	D	1.2155	D	1.1025	E	1.3365
4	20080201	20080301	0 - 0.8	1.1783	0 - 0.8	1.0165	0.81 - 1	1.3647	0.81 - 1	1.1322	0.81 - 1	1.1655	1.01 - 1.2	1.6042
5	20080201	20080301	V1	622.93	V1	901.7	V1	125.48	V1	110.86	V1	476.33	V1	95.66
6	20080201	20080301	35	1.0551	35	1.06	35	1.1	36	1.1079	36	1.06	36	1.1
7	20080302	20080401	HRSE1	1.05	HRSE2	1
8	20080302	20080401	A	1.05	B	1.134	C	1.3098	D	1.2155	D	1.1025	E	1.3365
9	20080302	20080401	0 - 0.8	1.1783	0 - 0.8	1.0165	0.81 - 1	1.3647	0.81 - 1	1.1322	0.81 - 1	1.1655	1.01 - 1.2	1.6042
10	20080302	20080401	V1	622.93	V1	901.7	V1	125.48	V1	110.86	V1	476.33	V1	95.66
11	20080302	20080401	35	1.0551	35	1.06	35	1.1	36	1.1079	36	1.06	36	1.1
12
Results

Is this what you are looing for in one worksheet Results?


Or, do you alwo want to display the NA's and zero's (only 1 if there are more in columns E thru J)?

 

[hiker95]

edlim85,


Reference the BOLD row 7:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Tables	From	To	Tag	Coefficient 1	coefficient 2	coefficient 3	Coefficient 4	Coefficient 5	Coefficient 6
2	TU564	20080201	20080301	HRSE1	1.05	NA	NA	NA	NA	NA
3		20080201	20080301	HRSE2	1.00	NA	NA	NA	NA	NA
4	TU572	20080201	20080301	A	0.0000	0.0000	0.0000	1.0500	1.0500	1.0500
5		20080201	20080301	B	0.0000	0.0000	0.0000	1.1340	1.1340	1.1340
6		20080201	20080301	C	0.0000	0.0000	0.0000	1.3098	1.3098	1.3098
7		20080201	20080301	D	0.0000	0.0000	0.0000	1.2155	1.1025	1.2155
Sheet1

There are two unique numbers in row 7, columns E thru J, which will produce in Results, this :

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	From	To	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient	Tag	Coefficient
2	20080201	20080301	HRSE1	1.05	HRSE2	1
3	20080201	20080301	A	1.05	B	1.134	C	1.3098	D	1.2155	D	1.1025	E	1.3365	E	1.0673
Results

Is the above screenshot correct?

 

[edlim85]

Hi Hiker95,

Whoa! thanks. this is great.!!! this is very close to what i need. the final table will compile the data by dates in row.

example
TU564
Row 2-2008020120080301
Row 3-2008030220080401

but this will work great. i would not have survive by copy and paste individual tags with each coefficient.

could you explain how did you do it?