Convert text to column and transpose

[jitendra]

Hi friend,

I have some data like below and I want to change it like the second table by using macro if anyone know please help me and thanks in advance.

Existing data
Country Destination C C Cde Price($) statu
ALBANIA OLO 355 4249,4250,4251,4252 3.5 new
ALBANIA TIRANA 126 422,423,424 6.8 old

Required data
Country Destination Country Code City Code Price($) statu
ALBANIA OLO 355 4249 3.5 new
ALBANIA OLO 355 4250 3.5 new
ALBANIA OLO 355 4251 3.5 new
ALBANIA OLO 355 4252 3.5 new
ALBANIA TIRANA 126 422 6.8 old
ALBANIA TIRANA 126 423 6.8 old
ALBANIA TIRANA 126 424 6.8 old


My required format is
Column 1:-Country
Column 2:- Destination
Column 3:-Country Code
Column 4:-City Code
Column 5rice($)
Column 6:-Status

 

[Domenic]

Biz,

Yeah, my code assumes that Column D contains text values.

Hiker,

Thanks!

Cheers to you both!

 

[Venkatp]

Hello - how do we make it run on all cells. Right now it only converts the text and transposes one column. Thank you!

 

[hiker95]

Hello - how do we make it run on all cells. Right now it only converts the text and transposes one column. Thank you!

Venkatp,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Rick Rothstein]

See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

You should read all of hiker95's comments, but to follow up on the above part of his comments... if the number of delimited pieces of text can vary from cell to cell, be sure to show that variation and the results for it so we can see how, if at all, you want the "alignment" between delimited pieces of text to be arranged.

 

[Venkatp]

Thank you hiker95 and Rick for the reply. My data is as below:

Actual data:
Databefore transformation

[FONT=&quot]Key[/FONT]​	[FONT=&quot]Nick Name[/FONT]​	[FONT=&quot]Lead name[/FONT]​	[FONT=&quot]Member Name[/FONT]​	[FONT=&quot]Gender[/FONT]​	[FONT=&quot]Age[/FONT]​	[FONT=&quot]Status[/FONT]​	[FONT=&quot]Trans Mode[/FONT]​
y9gbb		Venu	Venu G, Mantina, Prahlad, Gopal	Male, Female, Male, Male	2, 7, 44, 37	Tentative, Tentative, Tentative, Tentative	Bus
38p98	vdas	Ven	Ven, Sumi, Naid P, Rama, Prem	Male, Female, Male, Female, Female	35, 35, 60, 50, 75	Confirmed, Confirmed, Confirmed, Confirmed, Tentative	Bus

<tbody>
</tbody>

Goal:

[FONT=&quot]Key[/FONT]​	[FONT=&quot]Nick Name[/FONT]​	[FONT=&quot]Lead name[/FONT]​	[FONT=&quot]Member Name[/FONT]​	[FONT=&quot]Gender[/FONT]​	[FONT=&quot]Age[/FONT]​	[FONT=&quot]Status[/FONT]​	[FONT=&quot]Trans Mode[/FONT]​
y9gbb		Venu	Venu G	Male	2​	Tentative	Bus
			Mantina	Female	7​	Tentative
			Prahlad	Male	44​	Tentative
			Gopal	Male	37​	Tentative
38p98	Vdas	Ven	Ven	Male	35​	Confirmed	Bus
			Sumi	Female	35​	Confirmed
			Naid P	Male	60​	Confirmed
			Rama	Female	50​	Confirmed
			Prem	Female	75​	Tentative

<tbody>
</tbody>

The delimiter is Fixed i.e, its "," but the column data is variant and the problem I get when I run the macro from previous posts (by hiker95 posted Aug 31st, 2011, 09:30 PM ) is that only Member Name is transformed and Gender, Age.. columns still stay the same.

I hope I am clear. Please let me know if more details are needed. (Using Excel version 2016)

 

[hiker95]

Venkatp,

Here is a macro solution for you to consider that is based on your latest screenshots. I assume that the cells in each group in a row, in columns D, E, F, and, G, contain the same number of separator characters, the comma and a space.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G	H
1	Key	Nick Name	Lead name	Member Name	Gender	Age	Status	Trans Mode
2	y9gbb		Venu	Venu G, Mantina, Prahlad, Gopal	Male, Female, Male, Male	2, 7, 44, 37	Tentative, Tentative, Tentative, Tentative	Bus
3	38p98	vdas	Ven	Ven, Sumi, Naid P, Rama, Prem	Male, Female, Male, Female, Female	35, 35, 60, 50, 75	Confirmed, Confirmed, Confirmed, Confirmed, Tentative	Bus
4
5
6
7
8
9
10
11
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	Key	Nick Name	Lead name	Member Name	Gender	Age	Status	Trans Mode
2	y9gbb		Venu	Venu G	Male	2	Tentative	Bus
3				Mantina	Female	7	Tentative
4				Prahlad	Male	44	Tentative
5				Gopal	Male	37	Tentative
6	38p98	vdas	Ven	Ven	Male	35	Confirmed	Bus
7				Sumi	Female	35	Confirmed
8				Naid P	Male	60	Confirmed
9				Rama	Female	50	Confirmed
10				Prem	Female	75	Tentative
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorganizeData()
' hiker95, 06/12/2017, ME575713
Dim r As Long, lr As Long, c As Long, s, i As Long, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 2 Step -1
    For c = 4 To 7 Step 1
      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If c = 4 Then
          .Rows(r + 1).Resize(UBound(s)).Insert
        End If
        .Cells(r, c).Resize(UBound(s) + 1) = Application.Transpose(s)
      End If
    Next c
  Next r
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorganizeData macro.

 

[Venkatp]

Thank you so much !!! It really saved a ton of time. Thank you!

This is my full form, Could you please modify such that it can run on below table:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

Key

Nick Name

Lead name

Member Name

Gender

Age

Status

Trans Mode

Pref 1

Pref 2

Select 1

Select 2

Volunteer

Language

Country Code

Whatsapp

Mobile

Comments

y9gbb

Venu

Venu G, Mantina, Prahlad, Gopal

Male, Female, Male, Male

2, 7, 44, 37

Tentative, Tentative, Tentative, Tentative

Bus

3 Bed

2 Bed

One Start

Two Start

1 hours/day, .5 hours/day

English, English, English, English

001

123-456-7890

123-456-7890

Allergies ….

38p98

vdas

Ven

Ven, Sumi, Naid P, Rama, Prem

Male, Female, Male, Female, Female

35, 35, 60, 50, 75

Confirmed, Confirmed, Confirmed, Confirmed, Tentative

Bus

2 Bed

3 Bed

Two Star

One Star

.5 hours/day, .5 hours/day, 1 hours/day, 1 hours/day

English, English, Telugu, Telugu, Tamil

<colgroup><col span="13"><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[hiker95]

Re: Convert text to column and transpose
Thank you so much !!! It really saved a ton of time. Thank you!

Venkatp,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[hiker95]

This is my full form, Could you please modify such that it can run on below table:

Venkatp,

I see that in a row, there could be a different number of comma/space characters, and, strings.

Be back in a little while.

 

[hiker95]

Venkatp,

Here is another macro for you to consider that is based on your latest screenshots.

Not all columns are shown to fit the MrExcel display area.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G	H	M	N	O	P	Q	R
1	Key	Nick Name	Lead name	Member Name	Gender	Age	Status	Trans Mode	Volunteer	Language	Country Code	Whatsapp	Mobile	Comments
2	y9gbb		Venu	Venu G, Mantina, Prahlad, Gopal	Male, Female, Male, Male	2, 7, 44, 37	Tentative, Tentative, Tentative, Tentative	Bus	1 hours/day, .5 hours/day	English, English, English, English	1	123-456-7890	123-456-7890	Allergies .
3	38p98	vdas	Ven	Ven, Sumi, Naid P, Rama, Prem	Male, Female, Male, Female, Female	35, 35, 60, 50, 75	Confirmed, Confirmed, Confirmed, Confirmed, Tentative
4
Sheet1

And, after the new macro:

Excel 2007
	A	B	C	D	E	F	G	H	M	N	O	P	Q	R
1	Key	Nick Name	Lead name	Member Name	Gender	Age	Status	Trans Mode	Volunteer	Language	Country Code	Whatsapp	Mobile	Comments
2	y9gbb		Venu	Venu G	Male	2	Tentative	Bus	1 hours/day	English	1	123-456-7890	123-456-7890	Allergies .
3				Mantina	Female	7	Tentative		.5 hours/day	English
4				Prahlad	Male	44	Tentative			English
5				Gopal	Male	37	Tentative			English
6	38p98	vdas	Ven	Ven	Male	35	Confirmed
7				Sumi	Female	35	Confirmed
8				Naid P	Male	60	Confirmed
9				Rama	Female	50	Confirmed
10				Prem	Female	75	Tentative
11
Sheet1

Sub ReorganizeData_V2()
' hiker95, 06/12/2017, ME575713
Dim r As Long, lr As Long, lc As Long, c As Long, s, i As Long, n As Long, mxr As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For r = lr To 2 Step -1
    mxr = 0
    For c = 4 To lc Step 1
      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If UBound(s) > mxr Then
          mxr = UBound(s)
        End If
      End If
    Next c
    .Rows(r + 1).Resize(mxr).Insert
    For c = 4 To lc Step 1
      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If UBound(s) < mxr Then
          .Cells(r, c).Resize(UBound(s) + 1) = Application.Transpose(s)
        Else
          .Cells(r, c).Resize(mxr + 1) = Application.Transpose(s)
        End If
      End If
    Next c
  Next r
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my Reply #16.

Then run the ReorganizeData_V2 macro.