Excel 2007 : Splitting data from the row into column with adding number

**tsamael** · 05-28-2010, 11:16 AM

Please can anybody help to create a formula?

Example of original text:
A B C
1 Acura TL 2004 2005 2006
2 Acura RL 2005 2006 2007 2008

After formula applied:
A B C
1 Acura TL 2004
2 Acura TL 2005
3 Acura TL 2006
4 Acura RL 2005
5 Acura RL 2006
6 Acura RL 2007
7 Acura RL 2008

I HOPE THIS IS THE CORRECT PLACE TO POST THIS QUESTION.

THANK YOU GUYS FOR YOUR HELP

**stanleydgromjr** · 05-30-2010, 04:18 PM

tsamael,

For a VBA macro solution, detach/open workbook Splitting data from the row into column with adding number - tsamael - SDG10.xlsm, and run the ReorgData macro.

**eastydie** · 06-11-2010, 04:41 AM

Hello,
I had this problem before and I didnt use any formula. Here is what I did:

(Assuming your data have an individual cell.. with Column A containing Acura TL and Acura RL.. then Column B has 2004 and 2005 then column C has 2005 and 2006... )
With your data as
A B C
1 Acura TL 2004 2005 2006
2 Acura RL 2005 2006 2007 2008

NEW SHEET:
I copied the column A:
Acura TL
Acura RL
to the new sheet's column A.. then copied
2004
2005
to its column B

then copied the column A
Acura TL
Acura RL
again below it then copied
2005
2006
to its column B

then copied the column A again
Acura TL
Acura RL
again below it then copied
2006
2007
to its column B

and so on and so forth

so I will have a very long rows of data.. but with only two columns..
then I sorted the Column A accordingly (i think its descending) then TADAAA!

BUT if your data is like this -
Column A
1 Acura
2 Acura

Column B
1 TL
2 RL

then Column C has
1 2004 2005 2006
2 2004 2005 2006 2007

What I would do is use TEXT TO COLUMN for the COLUMN C to separate the entries then use my above suggested procedure

My Excel doesnt go well with MACROS so I make the most of manual labor hahaha!

I hope you get my suggestion

Let me know if anything is a blur and I also hope this helps.

Thanks!