A different kind of transpose

[ascool_asice]

Hi,

I have a worksheet with over 10K records which I would like to transpose in a specific manner... My original data looks like

Excel 2010
	A	B	C	D	E	F	G
1	[Page Title]	[ID]	[Name]	[Row 2]	[Part No_]	[Row4]	[Row 5]
2	DocuPrint N17b	1596	XEROX DOCUPRINT N17 TONER BLACK	Genuine Original Xerox	Part No.: 113R00095	Black	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b
3	DocuPrint N17b	1597	XEROX DOCUPRINT 4517 FUSER 110V	Genuine Original Xerox	Part No.: 108R00092		Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b
4	DocuPrint N17b	1598	XEROX 4517 FUSER ASSEMBLY	Genuine Original Xerox	Part No.: 126K06051		Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b
5	DocuPrint N17b	1599	XEROX DOCUPRINT 4517 FUSER / TRANSFER BIAS ROLLS	Genuine Original Xerox	Part No.: 108R93		Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b
6	DocuPrint N17b	1600	XEROX 4517 TONER REMAN	Legacy Non-OEM	Part No.: 113R95-REMAN	Black	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b
7	DocuPrint N17b	1601	XEROX N17 4517 DUPLEX OPTION	Genuine Original Xerox	Part No.: 97K22660		Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b
8	DocuPrint N17b	1602	XEROX 4517 PAPER DECK AND CASSETTE 500 SHEETS	Genuine Original Xerox	Part No.: 097K22680		Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b
9	DocuPrint N2125	1603	XEROX DOCUPRINT N2125 PRINT CTG 15K	Genuine Original Xerox	Part No.: 113R00446, X613	Black	Fits Models: DocuPrint N2125, DocuPrint N2125b
10	DocuPrint N2125	1604	XEROX DOCUPRINT N2125 PRINT CTG 10K	Genuine Original Xerox	Part No.: 113R445, X615	Black	Fits Models: DocuPrint N2125, DocuPrint N2125b
11	DocuPrint N2125	1605	XEROX N2125 MAINTENANCE KIT 110V	Genuine Original Xerox	Part No.: 108R00328	Black	Fits Models: DocuPrint N2125, DocuPrint N2125b
12	DocuPrint N2125	1606	XEROX N2125 MAINTENANCE KIT 110V	Genuine Original Xerox	Part No.: 126K10132		Fits Models: DocuPrint N2125, DocuPrint N2125b
13	DocuPrint N2125	1607	XEROX N2125 MAINTENANCE KIT 220V	Genuine Original Xerox	Part No.: 108R329		Fits Models: DocuPrint N2125, DocuPrint N2125b
14	DocuPrint N2125	1608	XEROX DOCUPRINT N2125 PRINT CTG REMAN	Legacy Non-OEM	Part No.: 113R445-REMAN, 113R00446-REMAN		Fits Models: DocuPrint N2125, DocuPrint N2125b
15	DocuPrint N2125	1609	XEROX DOCUPRINT N2125 FEED ROLLER KIT	Genuine Original Xerox	Part No.: 108R330		Fits Models: DocuPrint N2125, DocuPrint N2125b
16	DocuPrint N2125	1610	XEROX DOCUPRINT 2125 PAPER TRAY	Genuine Original Xerox	Part No.: 109R00448		Fits Models: DocuPrint N2125, DocuPrint N2125b
17	DocuPrint N2125b	1611	XEROX DOCUPRINT N2125 PRINT CTG 15K	Genuine Original Xerox	Part No.: 113R00446, X613	Black	Fits Models: DocuPrint N2125, DocuPrint N2125b
18	DocuPrint N2125b	1612	XEROX DOCUPRINT N2125 PRINT CTG 10K	Genuine Original Xerox	Part No.: 113R445, X615	Black	Fits Models: DocuPrint N2125, DocuPrint N2125b
19	DocuPrint N2125b	1613	XEROX N2125 MAINTENANCE KIT 110V	Genuine Original Xerox	Part No.: 108R00328	Black	Fits Models: DocuPrint N2125, DocuPrint N2125b
20	DocuPrint N2125b	1614	XEROX N2125 MAINTENANCE KIT 110V	Genuine Original Xerox	Part No.: 126K10132		Fits Models: DocuPrint N2125, DocuPrint N2125b
21	DocuPrint N2125b	1615	XEROX N2125 MAINTENANCE KIT 220V	Genuine Original Xerox	Part No.: 108R329		Fits Models: DocuPrint N2125, DocuPrint N2125b
22	DocuPrint N2125b	1616	XEROX DOCUPRINT N2125 PRINT CTG REMAN	Legacy Non-OEM	Part No.: 113R445-REMAN, 113R00446-REMAN		Fits Models: DocuPrint N2125, DocuPrint N2125b
23	DocuPrint N2125b	1617	XEROX DOCUPRINT N2125 FEED ROLLER KIT	Genuine Original Xerox	Part No.: 108R330		Fits Models: DocuPrint N2125, DocuPrint N2125b
24	DocuPrint N2125b	1618	XEROX DOCUPRINT 2125 PAPER TRAY	Genuine Original Xerox	Part No.: 109R00448		Fits Models: DocuPrint N2125, DocuPrint N2125b
25	DocuPrint N24	1619	XEROX DOCUPRINT N24 N32 N40 PRINT CTG BLACK	Genuine Original Xerox	Part No.: 113R173	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
26	DocuPrint N24	1620	XEROX DOCUPRINT N24 N32 N40 TONER REMAN	Legacy Non-OEM	Part No.: 113R173-REMAN	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
27	DocuPrint N24	1621	XEROX 108R158 STAPLE CARTRIDGE	Genuine Original Xerox	Part No.: 108R00158 (3 per box)		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025, DocuPrint N4525, DocuPrint N4525CN, DocuPrint N4525DX, DocuPrint N4525N
28	DocuPrint N24	1622	XEROX DOCUPRINT N24 N32 N40 MAINTENANCE KIT 110V	Genuine Original Xerox	Part No.: 109R00486		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
29	DocuPrint N24	1623	TROY XEROX DOCUPRINT N24 MICR TONER CART BLACK	Genuine Original Troy	Part No.: 02-81022-001	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
30	DocuPrint N24	1624	XEROX DOCUPRINT N24 N40 TONER MICR	Legacy Non-OEM	Part No.: 113R173-MICR	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
31	DocuPrint N24	1625	XEROX DP N24 FUSER ASSEMBLY 220V	Genuine Original Xerox	Part No.: 126K10024		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
32	DocuPrint N24	1626	XEROX DOCUPRINT N24 N32 N40 MAINTENANCE KIT	Genuine Original Xerox	Part No.: 109R00348		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
33	DocuPrint N24	1627	XEROX DOCUPRINT N24 N32 N40 MAINTENANCE KIT	Genuine Original Xerox	Part No.: 109R46		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
34	DocuPrint N24	1628	XEROX N24 N32 N40 MAINTENANCE KIT 220V	Genuine Original Xerox	Part No.: 109R487		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
35	DocuPrint N24	1629	XEROX DOCUPRINT N32 MAINTENANCE KIT EU	Genuine Original Xerox	Part No.: 109R00349		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
36	DocuPrint N24	1630	XEROX DOCUPRINT N24 N32 MAINTENANCE KIT 220V	Genuine Original Xerox	Part No.: 109R00047		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
37	DocuPrint N24	1631	FUJI XEROX DOCUPRINT N24 DRUM/TONER CARTRIDGE BLACK	Genuine Original Fuji Xerox	Part No.: X589, 113R162	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
38	DocuPrint N24	1632	XEROX DOCUPRINT N24 N32 N40 TONER BLACK	Genuine Original Xerox	Part No.: 113R184	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
39	DocuPrint N24	1633	XEROX DOCUPRINT N24 N32 N40 TONER	Genuine Original Xerox	Part No.: 113R162		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
40	DocuPrint N24	1634	CARTUCHO DE TONER 4524 / 4532 / 4540	Genuine Original Xerox	Part No.: 113M00452	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
41	DocuPrint N24	1635	CARTUCHO DE TONER 4524 / 4532 / 4540	Genuine Original Xerox	Part No.: 113R00452	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
42	DocuPrint N24	1636	CARTUCHO DE IMPRESSO S315 / S316 / S320 / S321	Genuine Original Xerox	Part No.: 113M70489		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
43	DocuPrint N24	1637	XEROX DOCUPRINT N24 N32 N40 TONER CARTRIDGE REMAN	Legacy Non-OEM	Part No.: 113R184-REMAN		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
44	DocuPrint N24	1638	XEROX DOCUPRINT N24 N40 TONER REMAN	Genuine Original Xerox	Part No.: 113R162R		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
45	DocuPrint N24	1639	XEROX DOCUPRINT N24 N32 N40 REFILL BOTTLE 535GR	Genuine Original Xerox	Part No.: 113R173-REFILL	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
46	DocuPrint N24	1640	XEROX DOCUPRINT N24 N32 N40 REFILL BOTTLE 1070GR	Genuine Original Xerox	Part No.: 113R00184-REFILL	Black	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
47	DocuPrint N24	1641	XEROX N24 BTR ASSEMBLY	Genuine Original Xerox	Part No.: 022K36343		Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
Orignal

The below mentioned table shows the way it should actually look once the macro is run... I tried to record a macro however failed miserably in achieving this...

Excel 2010
	A	B	C	D
1	DocuPrint N17b	DocuPrint N2125	DocuPrint N2125b	DocuPrint N24
2	XEROX DOCUPRINT N17 TONER BLACK	XEROX DOCUPRINT N2125 PRINT CTG 15K	XEROX DOCUPRINT N2125 PRINT CTG 15K	XEROX DOCUPRINT N24 N32 N40 PRINT CTG BLACK
3	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox
4	Part No.: 113R00095	Part No.: 113R00446, X613	Part No.: 113R00446, X613	Part No.: 113R173
5	Black	Black	Black	Black
6	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
7	XEROX DOCUPRINT 4517 FUSER 110V	XEROX DOCUPRINT N2125 PRINT CTG 10K	XEROX DOCUPRINT N2125 PRINT CTG 10K	XEROX DOCUPRINT N24 N32 N40 TONER REMAN
8	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox	Legacy Non-OEM
9	Part No.: 108R00092	Part No.: 113R445, X615	Part No.: 113R445, X615	Part No.: 113R173-REMAN
10		Black	Black	Black
11	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
12	XEROX 4517 FUSER ASSEMBLY	XEROX N2125 MAINTENANCE KIT 110V	XEROX N2125 MAINTENANCE KIT 110V	XEROX 108R158 STAPLE CARTRIDGE
13	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox
14	Part No.: 126K06051	Part No.: 108R00328	Part No.: 108R00328	Part No.: 108R00158 (3 per box)
15		Black	Black
16	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025, DocuPrint N4525, DocuPrint N4525CN, DocuPrint N4525DX, DocuPrint N4525N
17	XEROX DOCUPRINT 4517 FUSER / TRANSFER BIAS ROLLS	XEROX N2125 MAINTENANCE KIT 110V	XEROX N2125 MAINTENANCE KIT 110V	XEROX DOCUPRINT N24 N32 N40 MAINTENANCE KIT 110V
18	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox
19	Part No.: 108R93	Part No.: 126K10132	Part No.: 126K10132	Part No.: 109R00486
20
21	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
22	XEROX 4517 TONER REMAN	XEROX N2125 MAINTENANCE KIT 220V	XEROX N2125 MAINTENANCE KIT 220V	TROY XEROX DOCUPRINT N24 MICR TONER CART BLACK
23	Legacy Non-OEM	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Troy
24	Part No.: 113R95-REMAN	Part No.: 108R329	Part No.: 108R329	Part No.: 02-81022-001
25	Black			Black
26	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
27	XEROX N17 4517 DUPLEX OPTION	XEROX DOCUPRINT N2125 PRINT CTG REMAN	XEROX DOCUPRINT N2125 PRINT CTG REMAN	XEROX DOCUPRINT N24 N40 TONER MICR
28	Genuine Original Xerox	Legacy Non-OEM	Legacy Non-OEM	Legacy Non-OEM
29	Part No.: 97K22660	Part No.: 113R445-REMAN, 113R00446-REMAN	Part No.: 113R445-REMAN, 113R00446-REMAN	Part No.: 113R173-MICR
30				Black
31	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
32	XEROX 4517 PAPER DECK AND CASSETTE 500 SHEETS	XEROX DOCUPRINT N2125 FEED ROLLER KIT	XEROX DOCUPRINT N2125 FEED ROLLER KIT	XEROX DP N24 FUSER ASSEMBLY 220V
33	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox
34	Part No.: 097K22680	Part No.: 108R330	Part No.: 108R330	Part No.: 126K10024
35
36	Fits Models: 4517 MP, DocuPrint 4517, DocuPrint N17, DocuPrint N17b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
37		XEROX DOCUPRINT 2125 PAPER TRAY	XEROX DOCUPRINT 2125 PAPER TRAY	XEROX DOCUPRINT N24 N32 N40 MAINTENANCE KIT
38		Genuine Original Xerox	Genuine Original Xerox	Genuine Original Xerox
39		Part No.: 109R00448	Part No.: 109R00448	Part No.: 109R00348
40
41		Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N2125, DocuPrint N2125b	Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
42				XEROX DOCUPRINT N24 N32 N40 MAINTENANCE KIT
43				Genuine Original Xerox
44				Part No.: 109R46
45
46				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
47				XEROX N24 N32 N40 MAINTENANCE KIT 220V
48				Genuine Original Xerox
49				Part No.: 109R487
50
51				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
52				XEROX DOCUPRINT N32 MAINTENANCE KIT EU
53				Genuine Original Xerox
54				Part No.: 109R00349
55
56				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
57				XEROX DOCUPRINT N24 N32 MAINTENANCE KIT 220V
58				Genuine Original Xerox
59				Part No.: 109R00047
60
61				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
62				FUJI XEROX DOCUPRINT N24 DRUM/TONER CARTRIDGE BLACK
63				Genuine Original Fuji Xerox
64				Part No.: X589, 113R162
65				Black
66				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
67				XEROX DOCUPRINT N24 N32 N40 TONER BLACK
68				Genuine Original Xerox
69				Part No.: 113R184
70				Black
71				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
72				XEROX DOCUPRINT N24 N32 N40 TONER
73				Genuine Original Xerox
74				Part No.: 113R162
75
76				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
77				CARTUCHO DE TONER 4524 / 4532 / 4540
78				Genuine Original Xerox
79				Part No.: 113M00452
80				Black
81				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
82				CARTUCHO DE TONER 4524 / 4532 / 4540
83				Genuine Original Xerox
84				Part No.: 113R00452
85				Black
86				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
87				CARTUCHO DE IMPRESSO S315 / S316 / S320 / S321
88				Genuine Original Xerox
89				Part No.: 113M70489
90
91				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
92				XEROX DOCUPRINT N24 N32 N40 TONER CARTRIDGE REMAN
93				Legacy Non-OEM
94				Part No.: 113R184-REMAN
95
96				Fits Models: DocuPrint N24, DocuPrint N24cn, DocuPrint N32, DocuPrint N3225, DocuPrint N32cn, DocuPrint N32fn, DocuPrint N33, DocuPrint N40, DocuPrint N4025
Formatted

It would be great if someone could help me..


Regards
HeemanshuC

 

[sergioMabres]

It looks like a typical report writer problem.
I think it best suited for a report writer solution
Use your data as a database
Use a tool for writing reports and create yours using excel as a database
There are many free tools from Microsoft
Sergio

 

[ascool_asice]

It looks like a typical report writer problem.
I think it best suited for a report writer solution
Use your data as a database
Use a tool for writing reports and create yours using excel as a database
There are many free tools from Microsoft
Sergio

Hi Sergio,

Thanks for the prompt response, I tried looking on google however came out with nothing concrete..

Since I am quiet new to this it would be great if you could mention the names of a few tools..

Regards
HeemanshuC

 

[Peter_SSs]

See if this gets you close.
Test in a copy of your workbook as the code deletes the original data!!
If this is not what you want, post back with further details (like where you want the results).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange_Data()<br>  <SPAN style="color:#00007F">Dim</SPAN> a, b, ColWidth<br>  <SPAN style="color:#00007F">Dim</SPAN> rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> maxitems <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  ColWidth = Split("25 25 25 75") <SPAN style="color:#007F00">'<- Final column widths. Change to suit</SPAN><br>  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>  col = 0<br>  a = Range("A1", Range("G" & Rows.Count).End(xlUp)).Value<br>  rws = <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>  maxitems = rws * 5 + 1<br>  <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> maxitems, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>  <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> rws<br>    <SPAN style="color:#00007F">If</SPAN> a(i, 1) <> a(i - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>      col = col + 1<br>      r = 1<br>      <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> maxitems, 1 <SPAN style="color:#00007F">To</SPAN> col)<br>      b(r, col) = a(i, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> j = 3 <SPAN style="color:#00007F">To</SPAN> 7<br>      r = r + 1<br>      b(r, col) = a(i, j)<br>    <SPAN style="color:#00007F">Next</SPAN> j<br>  <SPAN style="color:#00007F">Next</SPAN> i<br>  Range("J1").Resize(maxitems, col).Value = b<br>  Columns("A:I").Delete<br>  <SPAN style="color:#00007F">For</SPAN> j = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(ColWidth)<br>    <SPAN style="color:#00007F">With</SPAN> Columns(j + 1)<br>      .ColumnWidth = ColWidth(j)<br>      .WrapText = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> j<br>  Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ascool_asice]

See if this gets you close.
Test in a copy of your workbook as the code deletes the original data!!
If this is not what you want, post back with further details (like where you want the results).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange_Data()<br>**<SPAN style="color:#00007F">Dim</SPAN> a, b, ColWidth<br>**<SPAN style="color:#00007F">Dim</SPAN> rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>**<SPAN style="color:#00007F">Dim</SPAN> maxitems <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>**<br>**ColWidth = Split("25 25 25 75") <SPAN style="color:#007F00">'<- Final column widths. Change to suit</SPAN><br>**Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>**col = 0<br>**a = Range("A1", Range("G" & Rows.Count).End(xlUp)).Value<br>**rws = <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>**maxitems = rws * 5 + 1<br>**<SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> maxitems, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>**<SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> rws<br>****<SPAN style="color:#00007F">If</SPAN> a(i, 1) <> a(i - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>******col = col + 1<br>******r = 1<br>******<SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> maxitems, 1 <SPAN style="color:#00007F">To</SPAN> col)<br>******b(r, col) = a(i, 1)<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****<SPAN style="color:#00007F">For</SPAN> j = 3 <SPAN style="color:#00007F">To</SPAN> 7<br>******r = r + 1<br>******b(r, col) = a(i, j)<br>****<SPAN style="color:#00007F">Next</SPAN> j<br>**<SPAN style="color:#00007F">Next</SPAN> i<br>**Range("J1").Resize(maxitems, col).Value = b<br>**Columns("A:I").Delete<br>**<SPAN style="color:#00007F">For</SPAN> j = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(ColWidth)<br>****<SPAN style="color:#00007F">With</SPAN> Columns(j + 1)<br>******.ColumnWidth = ColWidth(j)<br>******.WrapText = <SPAN style="color:#00007F">True</SPAN><br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>**<SPAN style="color:#00007F">Next</SPAN> j<br>**Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hi Peter,

Thanks for the code, I would actually need to retain the original data and would appreciate if you could help modify the code to suit my needs... It would be ideal if the code could create a new sheet with results

 

[Peter_SSs]

It would be ideal if the code could create a new sheet with results

Just add the blue line of code where shown and edit it to match the sheet name the original data is on.

  Application.ScreenUpdating = False
  Sheets("Original").Copy After:=Sheets("Original")
  col = 0