dupplicate rows based on data in a column.

T

taylor58285

New Member
Joined
Mar 1, 2014
Messages
14
I have a spreadsheet of 18 columns and 1800 rows.
I deed to duplicate all columns of a given row based on the data in a specific (Tickets)column. Any help is appreciated.
Similar to AK216 January 2013
My example

A B C D E F G H I J K etc.
Tickets Name Date Item Qty
1 Joe 12/22/2013 Soup 24
3 Sue 12/29/2013 Shrimp 12
2 Jack 02/11/2014 Beans 36

Desired Result
1 Joe 12/22/2013 Soup 24
3 Sue 12/29/2013 Shrimp 12
3 Sue 12/29/2013 Shrimp 12 all columns to be duplicated
3 Sue 12/29/2013 Shrimp 12
2 Jack 02/11/2014 Beans 36
2 Jack 02/11/2014 Beans 36
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
taylor58285,

Sample raw data:


Excel 2007
ABCDEFGHIJKLMNOPQR
1TicketsNameDateItemQty6789101112131415161718
21Joe12/22/2013Soup241111111111111
33Sue12/29/2013Shrimp123333333333333
42Jack02/11/2014Beans362222222222222
5
6
7
8
Sheet1


After the macro using two arrays in memory:


Excel 2007
ABCDEFGHIJKLMNOPQR
1TicketsNameDateItemQty6789101112131415161718
21Joe12/22/2013Soup241111111111111
33Sue12/29/2013Shrimp123333333333333
43Sue12/29/2013Shrimp123333333333333
53Sue12/29/2013Shrimp123333333333333
62Jack02/11/2014Beans362222222222222
72Jack02/11/2014Beans362222222222222
8
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.

Code: 
Option Explicit
Sub ReorgData()
' hiker95, 03/02/2014, ME761285
Dim a As Variant, o As Variant
Dim i As Long, ii As Long, c As Long
Dim lr As Long, lc As Long, n As Long, r As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
a = Range(Cells(2, 1), Cells(lr, lc))
n = Evaluate("=Sum(A2:A" & lr & ")")
ReDim o(1 To n, 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
  For r = 1 To a(i, 1)
    ii = ii + 1
    For c = 1 To UBound(a, 2)
      o(ii, c) = a(i, c)
    Next c
  Next r
Next i
Cells(2, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
Range("C2:C" & UBound(o, 1) + 1).NumberFormat = "mm/dd/yyyy"
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

Then run the ReorgData macro.
 
Upvote 0
hiker95 said:
Rich (BB code): 
Option Explicit
Sub ReorgData()
' hiker95, 03/02/2014, ME761285
Dim a As Variant, o As Variant
Dim i As Long, ii As Long, c As Long
Dim lr As Long, lc As Long, n As Long, r As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
a = Range(Cells(2, 1), Cells(lr, lc))
n = Evaluate("=Sum(A2:A" & lr & ")")
ReDim o(1 To n, 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
  For r = 1 To a(i, 1)
    ii = ii + 1
    For c = 1 To UBound(a, 2)
      o(ii, c) = a(i, c)
    Next c
  Next r
Next i
Cells(2, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
Range("C2:C" & UBound(o, 1) + 1).NumberFormat = "mm/dd/yyyy"
End Sub
Click to expand...
hiker95, just a few comments about your generally good, fast code.

1. Given that the OP has about 1,800 rows and 18 columns, you have about 34,000 UBound calculations that are not necessary.
Since you set the dimensions of array 'a' using lr and lc and the dimensions of array 'o' using n and UBound(a,2) - which was itself determined by lc - you don't really need to keep evaluating those bounds in your loops, or a couple of other places I have indicated in the code below.

2. To enable direct use of lr I have also read row 1 into array 'a' but started processing that array at row 2.

3. These changes don't seem to make much difference to the run time, but could in some circumstances. In any case it just seems sensible to use existing values where you can.

4. I'm also not sure why the red "+ 1" is in your code, but I left that in mine anyway.

So my slight modifications to your code are in the rows marked with asterisks.

Rich (BB code): 
Sub ReorgData2()
Dim a As Variant, o As Variant
Dim i As Long, ii As Long, c As Long
Dim lr As Long, lc As Long, n As Long, r As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
a = Range(Cells(1, 1), Cells(lr, lc))             '***
n = Evaluate("=Sum(A2:A" & lr & ")")
ReDim o(1 To n, 1 To lc)                          '***
For i = 2 To lr                                   '***
  For r = 1 To a(i, 1)
    ii = ii + 1
    For c = 1 To lc                               '***
      o(ii, c) = a(i, c)
    Next c
  Next r
Next i
Cells(2, 1).Resize(n, lc) = o                     '***
Range("C2:C" & n + 1).NumberFormat = "mm/dd/yyyy" '***
End Sub
 
Upvote 0
Thanks hiker95, however, it did not work.
When running the macro it retuned a message "Script out of range". Clicking debug, the 11th line beginning ReDim o (........ is highlighted.
Maybe his indicates what needs to be changed.
 
Upvote 0
taylor58285,

When running the macro it retuned a message "Script out of range". Clicking debug, the 11th line beginning ReDim o (........ is highlighted.
Click to expand...

Sounds like we have not seen what your actual raw data looks like.


Instead of text displays of what your raw data, and, results, look like can we have:


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Hiker95

I have tried several times to put a JPG screen shot and insert from windows clipboard and it doe not work for me . I am probably dong something wrong. The before and after example above I a fine example of a segment of my file. When creating a macro with your code and pressing Alt +Q, VBA does not close and Macro does not exist.
 
Upvote 0
taylor58285,

What version of Excel and Windows are you using?

Are you using a PC or a Mac?

I have tried several times to put a JPG screen shot
Click to expand...

JPG screenshots will not do.


You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
taylor58285,

I am using excel 2003 but could use excel 2007, windows 7 Home Premium on a PC
Click to expand...

Thanks for that.

I would still need to see your workbook.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Average in excel
Replies
3
Views
58
etaf
etaf
M
  • Question
calculate minutes worked in the hour, by the hour
Replies
4
Views
357
bebo021999
B
K
  • Solved
count without duplicate
2 3
Replies
27
Views
764
kelvin_9
K
S
  • Question
how to enter a value in a specific cell based on column and row headers
Replies
4
Views
108
Shazzj001
S
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
235
Lukma
L

Forum statistics

Threads
1,214,813
Messages
6,121,705
Members
449,048
Latest member
81jamesacct

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top