rearranging columns by reversing in location and create headers

[abdo meghari]

Hi guys,
I have untidy data in columns I:N .I would arranging again in columns A:E
column A=column N
column B=column L
column C=column K
column D=column J
column E=column I
and every number is merged in column N,K,J,I then should cancel merging as show in column A,C,D,E and at the same time should merge the cells are consecutive in column L as show in column B for the number is merged in column N
after that should delete all of columns from I:N
and should create headers in row1.


before

COL.xlsm
	I	J	K	L	M	N
1
2	2,700.00	JAPAN	BRIDGESTONE	BS 1200R20 18PR G580		1
3				JAP
4	2,680.00	JAPAN	BRIDGESTONE	BS 1200R20 18PR R187		2
5				JAP
6	2,550.00	JAPAN	BRIDGESTONE	BS 1200R24 G580		3
7	2,600.00	JAPAN	BRIDGESTONE	BS 1200R24 G582 JAP		4
8	4,160.00	JAPAN	BRIDGESTONE	BS 1400R20 TCF R180		5
9				JAP
10	4,280.00	JAPAN	BRIDGESTONE	BS 1400R20VSJ TCF JAP		6
11	420	THILAND	BRIDGESTONE	BS 175/65R14 EP150		7
12	180	TURKEY	BRIDGESTONE	BS 175/70R13 EP150 82S		8
13				TURK
14	280	THILAND	BRIDGESTONE	BS 175/70R13 EP150 THI		9
15	215	THILAND	BRIDGESTONE	BS 175/70R14 MY02 THI		10
16	260	Indonesia	BRIDGESTONE	BS 185/65R14 EP150		11
17				IND
18	315	JAPAN	BRIDGESTONE	BS 185/65R15 B250 JAP		12
19	290	Indonesia	BRIDGESTONE	BS 185/70R13 EP150		13
20				IND
21	360	THILAND	BRIDGESTONE	BS 185/70R14 B250 THI		14
22	435	TURKEY	BRIDGESTONE	BS 185R14C R660		15
23				102R100R 8 TURK
24	288	THILAND	BRIDGESTONE	BS 195/60R15 EP150 THI		16
25	267	JAPAN	BRIDGESTONE	BS 195/60R15 T001 JAP		17
26	335	JAPAN	BRIDGESTONE	BS 195/65R15 EP150 JAP		18
27	740	JAPAN	BRIDGESTONE	BS 195/70R15C R623		19
28				JAP
29	490	THILAND	BRIDGESTONE	BS 195R14C R623 THI		20
30	488	TURKEY	BRIDGESTONE	BS 195R14C R660 TURK		21
31	428	JAPAN	BRIDGESTONE	BS 195R15C 613V JAP		22
32	490	THILAND	BRIDGESTONE	BS 195R15C R623 THI		23
33	490	TURKEY	BRIDGESTONE	BS 195R15C R660 TURK		24
34	409	TURKEY	BRIDGESTONE	BS 205/65R15 EP150		25
35				TURK
36	300	THILAND	BRIDGESTONE	BS 205/65R15 T005 THI		26
SS

result should be

COL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	BRAND	MARKS	ORIGIN	PRICE
2	1	BS 1200R20 18PR G580 JAP	BRIDGESTONE	JAPAN	2,700.00
3	2	BS 1200R20 18PR R187 JAP	BRIDGESTONE	JAPAN	2,680.00
4	3	BS 1200R24 G580	BRIDGESTONE	JAPAN	2,550.00
5	4	BS 1200R24 G582 JAP	BRIDGESTONE	JAPAN	2,600.00
6	5	BS 1400R20 TCF R180 JAP	BRIDGESTONE	JAPAN	4,160.00
7	6	BS 1400R20VSJ TCF JAP	BRIDGESTONE	JAPAN	4,280.00
8	7	BS 175/65R14 EP150	BRIDGESTONE	THILAND	420.00
9	8	BS 175/70R13 EP150 82S TURK	BRIDGESTONE	TURKEY	180.00
10	9	BS 175/70R13 EP150 THI	BRIDGESTONE	THILAND	280.00
11	10	BS 175/70R14 MY02 THI	BRIDGESTONE	THILAND	215.00
12	11	BS 185/65R14 EP150 IND	BRIDGESTONE	Indonesia	260.00
13	12	BS 185/65R15 B250 JAP	BRIDGESTONE	JAPAN	315.00
14	13	BS 185/70R13 EP150 IND	BRIDGESTONE	Indonesia	290.00
15	14	BS 185/70R14 B250 THI	BRIDGESTONE	THILAND	360.00
16	15	BS 185R14C R660 102R100R 8 TURK	BRIDGESTONE	TURKEY	435.00
17	16	BS 195/60R15 EP150 THI	BRIDGESTONE	THILAND	288.00
18	17	BS 195/60R15 T001 JAP	BRIDGESTONE	JAPAN	267.00
19	18	BS 195/65R15 EP150 JAP	BRIDGESTONE	JAPAN	335.00
20	19	BS 195/70R15C R623 JAP	BRIDGESTONE	JAPAN	740.00
21	20	BS 195R14C R623 THI	BRIDGESTONE	THILAND	490.00
22	21	BS 195R14C R660 TURK	BRIDGESTONE	TURKEY	488.00
23	22	BS 195R15C 613V JAP	BRIDGESTONE	JAPAN	428.00
24	23	BS 195R15C R623 THI	BRIDGESTONE	THILAND	490.00
25	24	BS 195R15C R660 TURK	BRIDGESTONE	TURKEY	490.00
26	25	BS 205/65R15 EP150 TURK	BRIDGESTONE	TURKEY	409.00
27	26	BS 205/65R15 T005 THI	BRIDGESTONE	THILAND	300.00
SS

 

[Alex Blakenburg]

that's mystery to be fast

Don't keep it a mystery, just ask questions.
The slowest part for most code is how many times you access the spreadsheet which is mostly when you read and write to it.
Reading a lot of data at once and writing a lot of data once is much faster than doing multiple read and writes. So you do one bulk read and one bulk write.

The rest of the processing is done in memory using the array.
This generally only works well if you don't have to keep formulas that need to be written back in the output.

In my code:

        Set rngSrc = .Range("I2", .Cells(rowLastSrc, "N"))
        arrSrc = rngSrc.Value                                  ' This reads all the data in one go into an array

    ' the rest of the code is in between these 2
    With rngDest.Offset(1).Resize(iDest, UBound(arrDest, 2))
        .Value = arrDest                                           ' This writes all the data in one go back to the spreadsheet from the 2nd array