Excel: moving column data into new rows??

Microsoft Office excel 2007 home & stude...
August 6, 2010 at 08:09:47
Specs: Windows XP
i have a large set of data that I pull. I need to take the large list and create a new line for every new number and duplicate the first set of data. This will be easier to show:
Example data:
GASTROINTESTINAL AGENTS DIGESTIVE AIDS CREON 2 51200024006740 51200024006760 51200024006720
GASTROINTESTINAL AGENTS DIGESTIVE AIDS LIPRAM-UL 2 51990003206774 51990003206785 51990003206787
GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRELIPASE 2 51990003200310
GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRELIPASE MST-16 2 51990003206780
GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRON 2 51990003206772 51990003206786
GASTROINTESTINAL AGENTS DIGESTIVE AIDS ULTRASE 2 51990003206748
GASTROINTESTINAL AGENTS DIGESTIVE AIDS ULTRASE MT 2 51990003206774 51990003206785 51990003206787

Each number is pull into a separeat column to the right of each product. There could be up to 20 columns of numbers related to only 1 product. I need to convert that 1 row into x number of rows (based on number of columns) and show as separate row entries.
Ex) Creon 2 90 caps 51119999119 51111111111

needs to be:
creon 2 90 caps 51119999119
creon 2 90 caps 51111111111
etc etc.


any help???????


See More: Excel: moving column data into new rows??

Report •


#1
August 6, 2010 at 10:20:03
Hi

Here is a macro that copies your data from a worksheet named "Source" to a worksheet named "Destination".
Each code number is on a separate line. along with the name in column A and the value in column B.
(From the data sample you posted it appears that there is a value (2 in each case) following the product name and before the code number - if this is not the case, please confirm which column always contains the first code number.

You will need to name the Source worksheet "Source" or else edit all instances of "Source" in the macro.
You will need to have an empty worksheet named "Destination" - the macro does not create it and won't work without it.

To run the macro, I suggest you add a button to your source worksheet
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Sub Button1_Click()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestOffst As Integer
Dim intColsToMove As Integer
Dim n As Integer

On Error GoTo ErrHnd

'turn off screen updating to reduce flicker
Application.ScreenUpdating = False

'set start of source data
Set rngStart = Worksheets("Source").Range("A2")
'find end of source data
Set rngEnd = Worksheets("Source") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'set start of data on destination worksheet
Set rngDestStart = Worksheets("Destination").Range("A2")
'set destination row offset counter
intDestOffst = 0

'loop through all the items in column A on the Source worksheet
For Each rngCell In Worksheets("Source").Range(rngStart, rngEnd)
    'get number of codes starting at column C
    intColsToMove = rngCell.Offset(0, CStr(Application.Columns.Count - 1)).End(xlToLeft).Column - 2
    'move each code
    For n = 1 To intColsToMove
        'copy columns A & B
        rngCell.Offset(0, 0).Copy Destination:=rngDestStart.Offset(intDestOffst, 0)
        rngCell.Offset(0, 1).Copy Destination:=rngDestStart.Offset(intDestOffst, 1)
        'copy nth code
        rngCell.Offset(0, n + 1).Copy Destination:=rngDestStart.Offset(intDestOffst, 2)
        'increment destination row offset
        intDestOffst = intDestOffst + 1
    Next n
Next

'turn screen updating on again
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'turn screen updating on again
Application.ScreenUpdating = True
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.

Regards


Report •

#2
August 9, 2010 at 06:39:43
Thank you Humar!
But, My data has columns A-H. Column H has deliniation with comma(up to 15 new entries). So, I can deliniate data into uy to 15 different columns or keep as comma. What do you suggest?
So, let me give you a more specific picture of whats happening.
Column: example
A: ADHD/ANTI-NARCOLEPSY /ANTI-OBESITY
B: ADHD
C: amphetamine salt combo
D: 5-325 mg, 7.5-325 mg, 10-325 mg tab
E: 1
F: 1
G: 400 tablets per 30 days
H: 65991002050310, 65991002050315, 65991002050320 (up to 15 or more of these. This is where i could deliniate to different columns if needed)

So, does the code need changed?


Report •

#3
August 9, 2010 at 09:30:30
Hi,

I will need to change the code.
It was written based on duplicate the first set of data but it wasn't clear that this consisted of data in 7 columns. It was also based on the numbers being in individual columns (you didn't show any commas in the data).

1.
Having the code numbers in individual columns would help - that's what I wrote the code for.
So columns H up to possibly V will contain the numbers.

2.
Which columns need to be copied to each new row:
Is it all of column A to G, or just some of them.

3.
Column D has items separated by commas (not shown in original data)
Is column D to be copied complete, or are there additional rules for it.

As the data you have shown in your response is different in several respects to the data you used in your original question, please ensure that you include all forms of data and all the rules for processing it in your response.

Macros can't guess what to do - specific rules have to be written which will be able to respond to all variations of the source data.

Regards


Report •

Related Solutions

#4
August 9, 2010 at 10:20:42
Hey,
Thanks again for your help.

1) Yes columns H through AZ could potentially have numbers(stored as text)
2) Columns A through G will need copied every time, the variable field is H thru AZ in each line.
3) Column D sometimes pulls in different strengths of drug and puts a comma between, but some cells are blank. To me, it wouldn't matter if they were separated, only that they are copied through each row.

These are all text data.
So for the above example, i would expect to see:

ROW 1, columns
A: ADHD/ANTI-NARCOLEPSY /ANTI-OBESITY
B: ADHD
C: amphetamine salt combo
D: 5-325 mg, 7.5-325 mg, 10-325 mg tab
E: 1
F: 1
G: 400 tablets per 30 days
H: 65991002050310

ROW 2, columns
A: ADHD/ANTI-NARCOLEPSY /ANTI-OBESITY
B: ADHD
C: amphetamine salt combo
D: 5-325 mg, 7.5-325 mg, 10-325 mg tab
E: 1
F: 1
G: 400 tablets per 30 days
H: 65991002050315

ROW 3, columns
A: ADHD/ANTI-NARCOLEPSY /ANTI-OBESITY
B: ADHD
C: amphetamine salt combo
D: 5-325 mg, 7.5-325 mg, 10-325 mg tab
E: 1
F: 1
G: 400 tablets per 30 days
H: 65991002050320


Thanks again!


Report •

#5
August 10, 2010 at 10:10:11
Any help? (see above)

Report •

#6
August 15, 2010 at 04:52:12
Hi,

The following code appears to meet your requirements.
Each code is copied to a new line.
(Any number of codes in columns H onwards are copied)
Each line has all the data from columns A to G

Option Explicit

Private Sub Button1_Click()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestOffst As Integer
Dim intColsToMove As Integer
Dim n As Integer

On Error GoTo ErrHnd

'turn off screen updating to reduce flicker
Application.ScreenUpdating = False

'set start of source data
Set rngStart = Worksheets("Source").Range("A2")
'find end of source data
Set rngEnd = Worksheets("Source") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'set start of data on destination worksheet
Set rngDestStart = Worksheets("Destination").Range("A2")
'set destination row offset counter
intDestOffst = 0

'loop through all the items in column A on the Source worksheet
For Each rngCell In Worksheets("Source").Range(rngStart, rngEnd)
    'get number of codes starting at column H
    intColsToMove = rngCell.Offset(0, CStr(Application.Columns.Count - 1)) _
            .End(xlToLeft).Column - 7
    'move each code
    For n = 1 To intColsToMove
        'copy columns A to G
        rngCell.Resize(1, 7).Copy _
                Destination:=rngDestStart.Offset(intDestOffst, 0)
        'copy nth code (starting at column H - offset =7)
        'destination is column H
        rngCell.Offset(0, n + 6).Copy _
                Destination:=rngDestStart.Offset(intDestOffst, 7)
        'increment destination row offset
        intDestOffst = intDestOffst + 1
    Next n
Next

'turn screen updating on again
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'turn screen updating on again
Application.ScreenUpdating = True
End Sub

Regards


Report •

#7
August 16, 2010 at 19:02:04
Humar,
you are awesome. thank you. works perfect

Report •

#8
August 17, 2010 at 05:12:14
You're welcome

Regards

Humar


Report •

#9
September 6, 2010 at 16:39:52
Dear Humar,

I have a similar situation, only I am trying to create new rows for only those cells which hold values. I have 365 columns (one for each day of the year) and some days contain values while others do not.

I tried using your code as is, only adjusting to column E instead of H, and for some reason, I only get 32769 resulting rows in "Destination", when the "Source" sheet has 33799!

In advance, thanks for all your help,

Juan


Report •

#10
September 6, 2010 at 17:31:27
Hi,

You say that you have a similar situation and that you have used the code to skeeter24's data, only adjusting to column E instead of H.

Macros are usually very specific to the data, so minor changes often do not work.

I suggest that you start a new thread - post your question with lots of details ...
You need to say where your data is stored - rows and columns and whether on one or more worksheets.

Then describe what you are trying to achieve.

Regards


Report •


Ask Question