Creating new rows based on cell contents

imimin

imimin

Active Member
Joined
May 9, 2006
Messages
404
Hello!

I need help with creating a new macro. I think this might be a challenging macro to write (or should I say easy for some of you pros out there :-) if you are up to the challenge)! Any help on this will be GREATLY APPRECIATED and I will owe you!!!

I have a LARGE list of items that each have an “item Number” (column A), “Description”(column B) and a “size/price” field (column C). Most of the items have a single price. However, about 2,000+/- of the items have 2 or more sizes/prices. See table below:

Excel Workbook
ABC
111214314KY PRIN DIA CHANN BRIDAL SET D.75TW$887.24;
211214714KW MATCHING BAND TO 112147 D.37CTWsize:SW - $1,402.73;size:W - $532.29;
311186914KW MACHINE SET RBC ETER.BAND D.50TW SIZE 7size:6 - $424.44;size:6.5 - $424.44;size:7 - $424.44;
Sheet1


What I need to do is have a macro that will ‘examine’ column C (Size/Price) and do the following:

1) If there is only one price (such as ‘5.00;’ – notice the semicolon after the price and also notice that a size is not associated with the price if there is only one price), then do nothing and move on to the next line.
2) If the column C has more than 1 size/price (such as ‘size:SW - $1,402.73;size:W - $532.29;’) then it needs to add new rows under that row equivalent in number to the number of size/price combos. The item number for the new row(s) will be the same as the one above it followed by a hyphen and the numeric portion of the size (such as 123456-5.5). The ‘Description’ will be copied exactly as the line above it. The size/price (column C) will reflect the size/price for each new item number (such as ‘size:SW - $1,402.73;’ for the first new item number/row and ‘size:W - $532.29;’ for the next new item number/row).
3) This process continues until the end of the data (until the last row in column A (or B or C or whatever) that contains data)

Please see an example of what I am after for an output below:

Excel Workbook
ABC
811214314KY PRIN DIA CHANN BRIDAL SET D.75TW$887.24;
911214714KW MATCHING BAND TO 112147 D.37CTWsize:SW - $1,402.73;size:W - $532.29;
10112147-SW14KW MATCHING BAND TO 112147 D.37CTWsize:SW - $1,402.73;
11112147-W14KW MATCHING BAND TO 112147 D.37CTWsize:W - $532.29;
1211186914KW MACHINE SET RBC ETER.BAND D.50TWsize:6 - $424.44;size:6.5 - $424.44;size:7 - $424.44;
13111869-614KW MACHINE SET RBC ETER.BAND D.50TWsize:6 - $424.44;
14111869-6.514KW MACHINE SET RBC ETER.BAND D.50TW6.5 - $424.44;
15111869-714KW MACHINE SET RBC ETER.BAND D.50TWsize:7 - $424.44;
Sheet1


Thank you VERY MUCH and have a GREAT day!
 
Sort by date Sort by votes
Another question: do you have any restraints built into your code that would limit the number of size/price combos for a particular C-cell? I ran the script again that was causing the error and started to carefully look for the "changed" character BUT instead found that the script DID NOT work correctly on a C-cell that had 17 size/price combos and that NONE of the size/price combos AFTER that were properly "extracted" ! (every size/price combo BELOW that extracted correctly)(Looks like the script runs these size/price combo extractions from the bottom of the sheet up?). If there is a limit, it might be a good idea to lift it! I think there might be some size/price combos that go up to 20 or more!

Thanks again for your help!
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
imimin,

I will need to see more data.

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net
 
Upvote 0
OK, here is a screen shot showing the results of your FIRST ExpandData script AFTER it ran. Notice the large size/price combo on line 3472 which DID not expand but the one below it DID (line 3473). Also notice that the ones ABOVE it did NOT expand (lines 3468 and 3469)! I check this carefully and ALL the size/price combos below 3472 DID expand (as far as I can tell) BUT ALL the size/price combos ABOVE 3472 did NOT expand(for sure on this one)!!!

If you still need to see my actual data, please let me know.

Thank You!

Excel Workbook
ABCD
3468815899>>>Detailsize:7 - $81.81;size:8 - $81.81;*
3469815929>>>Detailsize:6 - $62.31;size:7 - $62.31;size:8 - $62.31;*
3470816069>>>Detailsize:6 - $53.88;size:7 - $53.88;*
3471816109>>>Detailsize:6 - $25.20;size:7 - $25.20;*
3472816139>>>Detailsize:10 - $42.60;size:10.5 - $42.60;size:11 - $42.60;size:11.5 - $42.60;size:12 - $42.60;size:12.5 - $42.60;size:13 - $42.60;size:4.5 - $42.60;size:5 - $42.60;size:5.5 - $42.60;size:6 - $42.60;size:6.5 - $42.60;size:7 - $42.60;size:7.5 - $42.60;size:8 - $42.60;size:8.5 - $42.60;size:9 - $42.60;size:9.5 - $42.60;*
3473816149>>>Detailsize:6 - $51.03;size:7 - $51.03;size:8 - $51.03;*
3474816149-6>>>Detailsize:6 - $51.03;*
3475816149-7>>>Detailsize:7 - $51.03;*
3476816149-8>>>Detailsize:8 - $51.03;*
Sheet1
 
Last edited:
Upvote 0
imimin,


Sample raw data:


Excel Workbook
ABC
111214314KY PRIN DIA CHANN BRIDAL SET D.75TW$887.24;
211214714KW MATCHING BAND TO 112147 D.37CTWsize:SW - $1,402.73;size:W - $532.29;
311186914KW MACHINE SET RBC ETER.BAND D.50TW SIZE 7size:6 - $424.44;size:6.5 - $424.44;size:7 - $424.44;
4816139>>>Detailsize:10 - $42.60;size:10.5 - $42.60;size:11 - $42.60;size:11.5 - $42.60;size:12 - $42.60;size:12.5 - $42.60;size:13 - $42.60;size:4.5 - $42.60;size:5 - $42.60;size:5.5 - $42.60;size:6 - $42.60;size:6.5 - $42.60;size:7 - $42.60;size:7.5 - $42.60;size:8 - $42.60;size:8.5 - $42.60;size:9 - $42.60;size:9.5 - $42.60;
5
Sheet1





After the updated macro:


Excel Workbook
ABC
111214314KY PRIN DIA CHANN BRIDAL SET D.75TW$887.24;
211214714KW MATCHING BAND TO 112147 D.37CTWsize:SW - $1,402.73;size:W - $532.29;
3112147-SW14KW MATCHING BAND TO 112147 D.37CTWsize:SW - $1,402.73;
4112147-W14KW MATCHING BAND TO 112147 D.37CTWsize:W - $532.29;
511186914KW MACHINE SET RBC ETER.BAND D.50TW SIZE 7size:6 - $424.44;size:6.5 - $424.44;size:7 - $424.44;
6111869-614KW MACHINE SET RBC ETER.BAND D.50TW SIZE 7size:6 - $424.44;
7111869-6.514KW MACHINE SET RBC ETER.BAND D.50TW SIZE 7size:6.5 - $424.44;
8111869-714KW MACHINE SET RBC ETER.BAND D.50TW SIZE 7size:7 - $424.44;
9816139>>>Detailsize:10 - $42.60;size:10.5 - $42.60;size:11 - $42.60;size:11.5 - $42.60;size:12 - $42.60;size:12.5 - $42.60;size:13 - $42.60;size:4.5 - $42.60;size:5 - $42.60;size:5.5 - $42.60;size:6 - $42.60;size:6.5 - $42.60;size:7 - $42.60;size:7.5 - $42.60;size:8 - $42.60;size:8.5 - $42.60;size:9 - $42.60;size:9.5 - $42.60;
10816139-10>>>Detailsize:10 - $42.60;
11816139-10.5>>>Detailsize:10.5 - $42.60;
12816139-11>>>Detailsize:11 - $42.60;
13816139-11.5>>>Detailsize:11.5 - $42.60;
14816139-12>>>Detailsize:12 - $42.60;
15816139-12.5>>>Detailsize:12.5 - $42.60;
16816139-13>>>Detailsize:13 - $42.60;
17816139-4.5>>>Detailsize:4.5 - $42.60;
18816139-5>>>Detailsize:5 - $42.60;
19816139-5.5>>>Detailsize:5.5 - $42.60;
20816139-6>>>Detailsize:6 - $42.60;
21816139-6.5>>>Detailsize:6.5 - $42.60;
22816139-7>>>Detailsize:7 - $42.60;
23816139-7.5>>>Detailsize:7.5 - $42.60;
24816139-8>>>Detailsize:8 - $42.60;
25816139-8.5>>>Detailsize:8.5 - $42.60;
26816139-9>>>Detailsize:9 - $42.60;
27816139-9.5>>>Detailsize:9.5 - $42.60;
28
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).


Code: 
Option Explicit
Sub ExpandDataV3()
' hiker95, 04/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=544963
Dim LR As Long, a As Long, aa As Long, Sp, s As Long, ss As Long, H As String
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 1 Step -1
  On Error GoTo ErrorSkip
  s = Len(Range("C" & a).Value) - Len(Application.Substitute(Range("C" & a).Value, ";", ""))
  If s > 1 Then
    Sp = Split(Cells(a, 3), ";")
    s = UBound(Sp)
    Rows(a + 1).Resize(s).Insert
    Rows(a).Copy Rows(a + 1).Resize(s)
    ss = 0
    For aa = a + 1 To a + s Step 1
      Cells(aa, 3) = Sp(ss) & ";"
      H = Mid(Cells(aa, 3), Application.Find(":", Cells(aa, 3), 1) + 1, Application.Find(" ", Cells(aa, 3), 1) - Application.Find(":", Cells(aa, 3), 1) - 1)
      Cells(aa, 1) = Cells(aa, 1) & "-" & H
      ss = ss + 1
    Next aa
  End If
ErrorSkip:
  On Error GoTo 0
Next a
Application.ScreenUpdating = True
End Sub


Then run the ExpandDataV3 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
948
Alex Blakenburg
A
S
  • Question Question
Excel VBA to create multi rows based on column header
Replies
1
Views
444
bebo021999
B
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
823
Agnarr
Agnarr
S
  • Solved
For each row, create new folder based on single cell value, then save exported XML doc to matching folder.
Replies
3
Views
686
shand
S
T
  • Solved
Create new workbooks based on cell content?
2
Replies
12
Views
782
kevin9999
K

Forum statistics

Threads
1,224,507
Messages
6,179,181
Members
452,893
Latest member
denay

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