Transpose Data into multiple Columns

[syar]

2.xlsx
	A
6	43 abcdf
7	16-6789
8	0316-xxxxxx
9	Criminal
10	44 thisishshs
11	18-1141
12	0315-xxxxx
13	Criminal
14	45
15	anonymos
16	17-8262
17	0314-xxxx
18	Criminal
19	46 mmmmmm
20	16-6717
21	0317-xxxxx
22	Criminal
23	17 asdf
24	16-6929
25	0334 -xxxxxx
26	Civil
Sheet2

I have inserted data from picture to excel sheet and it copied in one column A6: A276. Now i need to convert/transpose this data into 04 columns "
S.No - Name - BC ID - Contact No -- Specialty"
the converted data is in sequence but there is a problem that some data had S.no combined with name like "43 abcdf" . how to move or transpose this data into relevant columns. i tried columns to text but not succeed.

your help in this appreciated.

 

[SunnyAlv]

Like this ??
but it has a drawback, which is that there can be no space in every text except in the number & name text.

=LET(a,IFERROR(TEXTSPLIT(TEXTJOIN("|",,A1:A21)," ","|",TRUE),""),b,TOCOL(a),c,FILTER(b,b<>""),WRAPROWS(c,5))

but it has a drawback, which is that there cannot be spaces in each text except in the number & name texts

 

[syar]

Thanks for your response.
there is two problem i am encountering.
1. if "Name" had a second name like " John Doe" it split it into separate columns
2. I am applying the formula on "A1:A320", it split contact number with "-" in separate columns

regards

 

[kvsrinivasamurthy]

Specialty given in data is Criminal and Civil. Are you having more than these 2. Pl give the full list of all specialties used in file.

 

[Snakehips]

@sayar given what you have stated then you could try the below.
However if there is inconsistency in the structure of your individual data elements, especially regarding spaces, you will always have potential for error.
eg Your original post has a space before the - in the last contact number. Hopefully that was a typo on your part and not typical of the live data.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G
1	43 Jon Alfred Doe		S No.	Name	BC ID	Contact	Speciality
2	16-6789		43	Jon Alfred Doe	16-6789	0316-xxxxxx	Criminal
3	0316-xxxxxx		44	this ish shs	18-1141	0315-xxxxx	Criminal
4	Criminal		45	anonymos	17-8262	0314-xxxx	Criminal
5	44 this ish shs		46	mmmmmm	16-6717	0317-xxxxx	Criminal
6	18-1141		17	asdf	16-6929	0334-xxxxxx	Civil
7	0315-xxxxx
8	Criminal
9	45
10	anonymos
11	17-8262
12	0314-xxxx
13	Criminal
14	46 mmmmmm
15	16-6717
16	0317-xxxxx
17	Criminal
18	17 asdf
19	16-6929
20	0334-xxxxxx
21	Civil
22
Sheet2

Cell Formulas
Range	Formula
C2:G6	C2	=LET(a,IFERROR(TEXTSPLIT(TEXTJOIN("|",,SUBSTITUTE(A1:A350," ","|",1)),"|"),""),b,TOCOL(a),c,FILTER(b,b<>""),WRAPROWS(c,5))
Dynamic array formulas.

HTH

 

[kvsrinivasamurthy]

Try this VBA code. Result is at C6.

Sub ArrangeData()
Dim A, hdr, M
Dim T&, Ta&, X&
Dim Rng As Range
A = Range("A6:A36")
Set Rng = Range("C6")      'result range, can be changed
hdr = Array("S.No", "Name", "BC ID", "Contact No", "Specialty")

With CreateObject("Scripting.dictionary")
For T = 1 To UBound(A, 1)
If IsNumeric(A(T, 1)) Then
X = 4
.Item(T) = Array(A(T, 1), A(T + 1, 1), A(T + 2, 1), A(T + 3, 1), A(T + 4, 1))
Else
X = 3
M = Split(A(T, 1), " ")
.Item(T) = Array(M(0) + 0, M(1), A(T + 1, 1), A(T + 2, 1), A(T + 3, 1))
End If
T = T + X
Next T
Rng.CurrentRegion.Clear
Rng.Resize(1, 5) = hdr
Rng.Offset(1, 0).Resize(.Count, 5) = Application.Index(.items, 0, 0)

End With
End Sub

 

[syar]

@Snakehips. Yes, you are write the typo was on my part, and there is some inconsistency in the data so i adjust it manually the formula work fine. Thanks for your support

 

[Snakehips]

You are welcome. Glad we could all help.

 

[kvsrinivasamurthy]

Ref post#6. There is no need of manual correction. Just run the macro.