Shift data to left

[Kishan]

Using Excel 2000

Hi,

I have got data in column D:G, and need shift data results in columns J:M,

Example data.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5
6				1	2	3	4			1	2	3	4
7					2|2|1					2|2|1
8
9					2|2|1					2|2|1
10
11					1|1|1					1|1|1
12					1|1|2		1|1|1			1|1|2	1|1|1
13						1|1|1				1|1|1
14					1|2|1	2|1|1				1|2|1	2|1|1
15						1|1|1				1|1|1
16				1|1|1	1|1|1		1|1|1			1|1|1	1|1|1	1|1|1
17				1|2|1			2|1|1			1|2|1	2|1|1
18					1|1|2					1|1|2
19					1|1|2	1|1|2				1|1|2	1|1|2
20				1|1|1			1|1|1			1|1|1	1|1|1
21							1|1|2			1|1|2
22				1|1|1			1|1|1			1|1|1	1|1|1
23				1|1|1			1|1|1			1|1|1	1|1|1
24				1|1|1	1|1|1	1|1|1				1|1|1	1|1|1	1|1|1
25				1|2|2	1|1|1	1|1|1				1|2|2	1|1|1	1|1|1
26					1|1|1	1|1|1	1|1|1			1|1|1	1|1|1	1|1|1
27					1|1|1					1|1|1
28					1|2|1	1|1|1				1|2|1	1|1|1
29					2|1|1					2|1|1
30
31					2|2|1	1|1|1	2|1|1			2|2|1	1|1|1	2|1|1
32					1|2|2	2|1|1				1|2|2	2|1|1
33							1|1|1			1|1|1
34					1|1|1	2|1|2				1|1|1	2|1|2
35				1|2|1						1|2|1
36						1|2|1				1|2|1
37
38						1|1|1	1|1|2			1|1|1	1|1|2
39							1|2|2			1|2|2
40
41							1|1|1			1|1|1
42				1|2|1	1|1|1					1|2|1	1|1|1
43					1|1|1	1|1|2	1|2|2			1|1|1	1|1|2	1|2|2
44
45						1|2|1	1|1|1			1|2|1	1|1|1
46					1|1|1	1|1|1				1|1|1	1|1|1
47							1|1|1			1|1|1
48				1|1|1		1|1|1				1|1|1	1|1|1
49						1|1|1	1|1|1			1|1|1	1|1|1
50				1|1|1		1|1|2				1|1|1	1|1|2
51				1|1|1						1|1|1
52				1|1|1	1|2|1		1|1|1			1|1|1	1|2|1	1|1|1
53							1|1|1			1|1|1
54					1|1|1	1|1|1	2|1|2			1|1|1	1|1|1	2|1|2
55				1|1|1						1|1|1
56						2|1|1				2|1|1
57
58				1|2|2	1|1|1					1|2|2	1|1|1
59					1|1|2	1|1|1				1|1|2	1|1|1
60				1|2|2		1|1|1				1|2|2	1|1|1
61				1|1|1	1|2|1					1|1|1	1|2|1
62				2|1|1						2|1|1
63				1|1|1			1|2|1			1|1|1	1|2|1
64				1|1|1	1|1|1	2|1|2	1|1|1			1|1|1	1|1|1	2|1|2	1|1|1
65				1|2|1	1|2|2	2|2|1	1|1|1			1|2|1	1|2|2	2|2|1	1|1|1
66				1|2|2	1|1|1	1|1|1				1|2|2	1|1|1	1|1|1
67				1|1|1			2|2|1			1|1|1	2|2|1
68						1|1|1	1|1|2			1|1|1	1|1|2
69						2|1|1				2|1|1
70
71
Sheet1

Thank you in advance

Regards,
Kishan

 

[jproffer]

You're not giving us much to go on...and the data isn't even the same, so "shift" might not be the right word, seems to me.

 

[motilulla]

Sorry wrong thread

 

[Kishan]

You're not giving us much to go on...and the data isn't even the same, so "shift" might not be the right word, seems to me.

Hi jproffer, you can see the data column D:G are split in 4 columns, what I want alien them to left as shown in the column J:M, and remove the empty space in betweens of the data

Regards,
Kishan

 

[Kishan]

Hi, I did found code which delete empty cells and shift data to left but in the column D:G it self.

But I want to keep the original data in to columns D:G
And get shifted data in to new columns J:M

Is it possible?

It can be VBA or formula solution.

Regards,
Kishan

 

[jproffer]

I understand what you meant now by "shift"...so maybe you could copy your original data to J:M and then run the code you found on those columns to remove the spaces and fill data to the left...

?? Would that work?

If you need help with that, and so we're not reinventing the wheel you've already found, maybe post what you have that moves the data to the empty cells and we can add to it to accomplish what you want.

 

[Kishan]

I understand what you meant now by "shift"...so maybe you could copy your original data to J:M and then run the code you found on those columns to remove the spaces and fill data to the left...

?? Would that work?

If you need help with that, and so we're not reinventing the wheel you've already found, maybe post what you have that moves the data to the empty cells and we can add to it to accomplish what you want.

HI jproffer,
Here is the code, which align the data to left and delete the blank cells in the columns D:G

Sub ShiftLeft()
Dim rng As Range
With ActiveSheet
    For Each cll In Intersect(.UsedRange, .Range("D:G")).Cells
        If Len(cll.Value) = 0 Then Set rng = Union(cll, IIf(rng Is Nothing, cll, rng))
    Next cll
End With
rng.Delete xlShiftToLeft
End Sub

But I want to keep column D:G data remain original and show align data in columns in J:M

Does it is possible?

Regards,
Kishan

 

[MickG]

Try this for results starting "J7"

[COLOR="Navy"]Sub[/COLOR] MG31May00
[COLOR="Navy"]Dim[/COLOR] Ray, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("D7:G70")
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
 col = 0
 [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
    [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
        col = col + 1
        nray(n, col) = Ray(n, Ac)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
Range("J7").Resize(UBound(Ray, 1), UBound(Ray, 2)) = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

Try this for results starting "J7"

[COLOR=navy]Sub[/COLOR] MG31May00
[COLOR=navy]Dim[/COLOR] Ray, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] col [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Ray = Range("D7:G70")
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
[COLOR=navy]For[/COLOR] n = 1 To UBound(Ray, 1)
 col = 0
 [COLOR=navy]For[/COLOR] Ac = 1 To UBound(Ray, 2)
    [COLOR=navy]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR=navy]Then[/COLOR]
        col = col + 1
        nray(n, col) = Ray(n, Ac)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] n
Range("J7").Resize(UBound(Ray, 1), UBound(Ray, 2)) = nray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Excellent!! Mike, Thank you for your help and time.

Appreciate it. Performing as treat.

Regards,
Kishan

 

[MickG]

You're very welcome