Solved Moving columns data into new row

February 29, 2016 at 21:37:32
Specs: Excel
Hi,

i found this question and seems near to my problem, but I totally dont know how to revise the code.
"Excel: Moving Column Data Into New Rows??"

I have this data:
UniqueID Company 1FullName 1Designation 1CountryCode 1Phone 1Email 2FullName 2Designation 2CountryCode 2Phone 2Email
R123456 Company1 Chris Reeves +65 90221111 abcd@ggg.com
R234567 Company2 Sam Lim Sole Proprietor +65 9851234 lmno@ggg.com Samboy Lim Account +65 94890987 xyz@ggg.com
R345678 Company3 Jose Accounts Department +65 67672345 pqr@ggg.com


and I need the above data to be like this:

UniqueID Company 1FullName 1Designation 1CountryCode 1Phone 1Email
R123456 Company1 Chris Reeves 65 90221111 abcd@ggg.com
R234567 Company2 Sam Lim Sole Proprietor 65 9851234 lmno@ggg.com
R234567 Company2 Samboy Lim Account 65 94890987 xyz@ggg.com
R345678 Company3 Jose Accounts Department 65 67672345 pqr@ggg.com


Please help.

Thanks

edited by moderator: Mask email addresses


See More: Moving columns data into new row

Report •


#1
March 1, 2016 at 03:55:42
I have edited your post to mask anything that looks like an valid email address. We do not want this forum to become a place where email addresses can be harvested. Once the bad guys start hanging around it's really hard to get rid of them.

I can not determine the layout of your input or output data.

Please click on the following line and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 1, 2016 at 17:54:24
Hi DerbyDad03, thanks for the guidance.

Please, here is my data:

UniqueID	Company		1FullName	1Designation	1Code	1Phone		1Email		2FullName 	2Designation	2Code	2Phone		2Email
R123456		Company1	Chris Reeves			+65	90221111	abcd@ggg.com	
R234567		Company2	Sam Lim		Boss		+65	9851234		lmno@ggg.com	Samboy Lim 	Account		+65	94890987	xyz@ggg.com
R345678		Company3	Jose		Accounts 	+65	67672345	pqr@ggg.com

and I need the above data to be in this manner:

UniqueID	Company		1FullName	1Designation	1Code	1Phone		1Email		
R123456		Company1	Chris Reeves			+65	90221111	abcd@ggg.com	
R234567		Company2	Sam Lim		Boss		+65	9851234		lmno@ggg.com
R234567		Company2	Samboy Lim 	Account		+65	94890987	xyz@ggg.com
R345678		Company3	Jose		Accounts 	+65	67672345	pqr@ggg.com

Many thanks.

message edited by eynitro


Report •

#3
March 2, 2016 at 06:32:22
✔ Best Answer
You didn't include Row or Column designations, so I have to assume that your data is in Columns A1:L4. Based on that assumption, try this:

1 - Select Columns H:I, Right-Click...Insert
2 - In the "new" H2 enter this formula: =IF(J2<>"",A2,"")
3 - Drag this to I2
4 - Drag both formulas down to H4:I4
This should add the UniqueID and Company name to any row that has data in it, e.g. Row 3 in this case.
5 - Select H2:N4, Copy, Paste Special...Values (eliminates formulas, locks in values)
6 - Select H2:N2, Cut, Paste into A5
7 - Sort A1:G7 based on Company (Column B)
8 - Delete any blank rows at the top of your data

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question