first macro second formula to combinning five columns into one column with empty cell

[abdo meghari]

hi
I need macro to combine the five columns into one column . the column A,B,C,D ,,E,F should collect into column H with considering there are some empty cells in some columns . if there are empty cells then should move the next column and merge which contain item and merge. I put some result into column H and should be for whole items into column H .
note : should create one space for each item for each column when merging . if it's not availabel by macro then should be by formula .

Bridgestone SPLIT).xlsm
	A	B	C	D	E	F	G
1	BRAND	BRAND	PATTERN	TT	TYPE	ORIGIN
2	BS	1200R20	18PR	TCF	G580	JAP
3	BS	1200R20	18PR	TC	G580	THI
4	BS	1200R24	20PR		G582	JAP
5	BS	13R22.5	16PR		R187	JAP
6	BS	1400R20		T	VSJ	JAP
7	BS	155R12C			R623	IND
8	BS	165R13C			R624	IND
9	BS	175/70R13			EP150	IND
10	BS	175/70R14			EP150	THI
11	BS	1800R25			VKT**	JAP
12	BS	185/65R14			EP150	IND
13	BS	185/65R14			TEC	THI
14	BS	185/65R15			T005	IND
15	BS	185/65R15			T01	JAP
16	BS	185/70R13			EP150	IND
17	BS	195/60R15			EP150	THA
18	BS	195/65R15			EP15	JAP
19	BS	195/65R15			EP150	THI
20	BS	195/70R15C			R623	JAP
21	BS	195R14C			613	JAP
22	BS	195R15C			R623	THI
23	BS	205/60R16			T001	JAP
24	BS	205/65R15			EP150	IND
25	BS	205/70R15C			R623	THI
26	BS	205R16C			D840	THI
27	BS	215/50R17			EP300	THI
28	BS	215/55R16			EP300	IND
29	BS	215/55R17			AR20	IND
30	BS	215/55R17			GR90	IND
31	BS	215/55R17			MY-02	THI
32	BS	215/60R16			EP300	THI
33	BS	215/60R16			T005A	THI
34	BS	215/70R15C			R623	THI
35	BS	225/55R16			EP300	IND
36	BS	225/60R16			EP300	IND
37	BS	225/60R16			T001	JAP
38	BS	225/70R15C			R623	JAP
39	BS	225/70R16			D687	JAP
40	BS	225/70R16			H005	THI
41	BS	225/70R17			D697	THI
42	BS	235/55R17			T005	THA
43	BS	235/55R17			ER300	JAP
44	BS	235/55R18			E031	JAP
45	BS	235/60R16			T01	JAP
46	BS	245/40ZR20			S001	JAP
47	BS	245/45R17			T05A	JAP
48	BS	245/45R18			EP300	THI
49	BS	245/45R19			T005	JAP
50	BS	245/70R17			684A	JAP
51	BS	245/75R17			693A	JAP
52	BS	255/70R15C			D840	THI
53	BS	265/50R20			DHPA	JAP
54	BS	265/60R18			D840	JAP
55	BS	265/65R17			D840	JAP
56	BS	265/70R16			D840	THI
57	BS	265/70R18			D684	JAP
58	BS	275/40R18			S007	JAP
59	BS	275/40R20			Dsport	JAP
60	BS	275/45R19			AL01	JAP
61	BS	275/55R17			Dsport	JAP
62	BS	275/55R20			AL01	JAP
63	BS	275/65R18			AL01	JAP
64	BS	275/70R16			D694	JAP
65	BS	285/60R18			DHPS	JAP
66	BS	285/65R17			R683	JAP
67	BS	315/80R22.5			R152	JAP
68	BS	315/80R22.5			R184	JAP
69	BS	315/80R22.5			R184	THI
70	BS	315/80R22.5-18PR	18PR		G580	JAP
71	BS	315/80R22.5-18PR			G582	THI
72	BS	325/95R24			G582	JAP
73	BS	385/65R22.5			R164	JAP
74	BS	385/65R22.5			R164	THA
75	BS	425/65R22.5			R164	JAP
76	BS	445/65R22.5			R164	JAP
77	BS	650R16			R230	JAP
78	BS	700R16		TCF	R230	JAP
79	BS	750R16	12PR		R230	JAP
80	BS	750R16	8PR		VSJ	JAP
81	BS	LT285/75R16			AT001	JAP
82	DT	315/80R22.5		T	DS50	THI
83	DT	385/65R22.5			DT40	THI
84	FS	205/65R15			TZ700	JAP
85	FS	215/55R17			TZ700	JAP
86
87
88
89
90
Sheet1

result in column H

Bridgestone SPLIT).xlsm
	H
1	MRGED
2	BS 1200R20 18PR TCF G580 JAP
3	BS 1200R20 18PR TC G580 THI
4	BS 1200R24 20PR G582 JAP
5	BS 13R22.5 16PR R187 JAP
6	1400R20 T VSJ JAP
Sheet1

thanks

 

[DanteAmor]

How about:

varios 29ene2022.xlsm
	A	B	C	D	E	F	G	H
1	BRAND	BRAND	PATTERN	TT	TYPE	ORIGIN		MERGED
2	BS	1200R20	18PR	TCF	G580	JAP		BS 1200R20 18PR TCF G580 JAP
3	BS	1200R20	18PR	TC	G580	THI		BS 1200R20 18PR TC G580 THI
4	BS	1200R24	20PR		G582	JAP		BS 1200R24 20PR G582 JAP
5	BS	13R22.5	16PR		R187	JAP		BS 13R22.5 16PR R187 JAP
6	BS	1400R20		T	VSJ	JAP		BS 1400R20 T VSJ JAP
7	BS	155R12C			R623	IND		BS 155R12C R623 IND
8	BS	165R13C			R624	IND		BS 165R13C R624 IND
9	BS	175/70R13			EP150	IND		BS 175/70R13 EP150 IND
10	BS	175/70R14			EP150	THI		BS 175/70R14 EP150 THI
Hoja2

Cell Formulas
Range	Formula
H2:H10	H2	=TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2)

 

[abdo meghari]

yes this is exactly what I want , if it's possible and you have free time to achieve it by macro I would truly appreciate it . if it's not possible I will close this thread and thank you .

 

[DanteAmor]

How about:

Sub mergeddata()
  With Range("H2:H" & Range("A" & Rows.Count).End(3).Row)
    .Formula = "=TRIM(A2&"" ""&B2&"" ""&C2&"" ""&D2&"" ""&E2&"" ""&F2)"
    .Value = .Value
  End With
End Sub

 

[abdo meghari]

perfect ! thank you so much

 

[DanteAmor]

Im glad to help you. Thanks for the feedback