Text to column and paste special transpose

Hi All,

I have 1 table like this


item_number	qty	ref
00010-001	3	U34,U36,U43
00013-001	2	U16,U21
12505-001	3	U32,U33,U42
12512-001	3	U5,U6,U27

and 1 want it to become like this

item_number	qty	ref
00010-001	3	U34
00010-001	3	U36
00010-001	3	U43
00013-001	2	U16
00013-001	2	U21
12505-001	3	U32
12505-001	3	U33
12505-001	3	U42
12512-001	3	U5
12512-001	3	U6
12512-001	3	U27

Anybody can help me to program it in macro.. usually i do it manually and have to redo again do to careless.


0
3/31/2012 2:06:45 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
1893 Views

Similar Articles

[PageSpeed] 27

After using Text to Columns on Column C.......comma
delimited.........Run the Reorganize macro.

Sub ReOrganize()
Dim LR As Long, I As Long, r As Long, c As Long, v As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
I = 2
Do Until Range("A" & I) = ""
    If Range("C" & I) <> "" Then
        c = Cells(I, Columns.Count).End(xlToLeft).Column
        v = I
        For r = 3 To c
            I = I + 1
            Rows(I).Insert xlShiftDown
            Range("A" & I) = Range("A" & I - 1)
            Range("C" & I) = Cells(v, r)
        Next r
    End If
    I = I + 1
Loop
Range("D1", Cells(Rows.Count, Columns.Count)).ClearContents
Call Fill_Blanks
Application.ScreenUpdating = True
End Sub

Sub Fill_Blanks()
Dim wks As Worksheet
Dim rng As Range
Dim lastrow As Long
Dim Col As Long
    Set wks = ActiveSheet
    With wks
        Col = .Range("B1").Column
        Set rng = .UsedRange
        lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        Set rng = Nothing
        On Error Resume Next
        Set rng = .Range(.Cells(2, Col), .Cells(lastrow, Col)) _
                .Cells.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If rng Is Nothing Then
            MsgBox "No blanks found"
            Exit Sub
        Else
             rng.NumberFormat = "General"
            rng.FormulaR1C1 = "=R[-1]C"
        End If
            With .Cells(1, Col).EntireColumn
            .Value = .Value
        End With
    End With
End Sub


Gord

On Sat, 31 Mar 2012 02:06:45 GMT, isfaruddin sapardi
<isfaruddin.sapardi@toyoplas.com.my> wrote:

>Hi All,
>
>I have 1 table like this
>
>
>item_number	qty	ref
>00010-001	3	U34,U36,U43
>00013-001	2	U16,U21
>12505-001	3	U32,U33,U42
>12512-001	3	U5,U6,U27
>
>and 1 want it to become like this
>
>item_number	qty	ref
>00010-001	3	U34
>00010-001	3	U36
>00010-001	3	U43
>00013-001	2	U16
>00013-001	2	U21
>12505-001	3	U32
>12505-001	3	U33
>12505-001	3	U42
>12512-001	3	U5
>12512-001	3	U6
>12512-001	3	U27
>
>Anybody can help me to program it in macro.. usually i do it manually and have to redo again do to careless.
>
0
phnorton (279)
3/31/2012 3:49:25 PM
Reply:

Similar Artilces:

show columns on other worksheet without using formulas
Hi, I have 1 worksheet containing lot's records. Just like in access, each line is 1 record, and each column contains data. ex: WORKSHEET 1: name title address city 1 dfjw kjhk kjhkj kjhk 2 lmkj lkjlk lkjkuf guyg 3 drdtg xcx yjutuy hgyy Now I want to have a selection of columns on worksheet 2: ex, only name and title: WORKSHEET 2: name title 1 dfjw kjhk 2 lmkj lkjlk 3 drdtg xcx I will only add/modify rows in worksheet 1. And I want that this is filled automaticaly in worksheet...

how do I to paste logo onto mailing label
Trying to copy/paste a logo onto a mailing label from word/Vista See http://www.gmayor.com/graphics_on_labels.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> sandy jock wrote: > Trying to copy/paste a logo onto a mailing label from word/Vista ...

Text Box Linked To Cell
My goal is to have a text box that displays the contents of a particular cell. How do I link the text box to the cell? Thanks, Denise Hi denise, Make a Text Box from the drawing toolbar. Select the box and in the Formula Bar, type = and then select the cell, press enter. ( IE: =$C$5 ) The cell can also be on a different sheet, just do the same. ( IE: =Sheet2!C5 ) Dave denise Wrote: > My goal is to have a text box that displays the contents of a > particular > cell. How do I link the text box to the cell? > > Thanks, > Denise -- Piranha --------------------------...

Modify Exchange undeliverable email text
Is there a way in Exchange 2003 to modify the text of the message that is sent for an undeliverable email address? Thank You Jason Shaw Not natively. James Chong MCSE + Messaging, MCTS msexchangetips.blogspot.com Jasons9745 wrote: > Is there a way in Exchange 2003 to modify the text of the message that > is sent for an undeliverable email address? > Thank You > Jason Shaw Not easily done. Jasons9745 wrote: > Is there a way in Exchange 2003 to modify the text of the message that > is sent for an undeliverable email address? > Thank You > Jason Shaw > > -...

Excel 2007
When you set reverse categories for the horizontal axis, I noticed that the legend (which I normally placed at the bottom of the chart), did not correspondingly reverse. Is this WAD (working as designed) ? If not, how can i overcome this as the reader would have to mentally "remap" the legend to the bar chart. Thanks very much. Hi, Reversing the Axis categories will not effect the legend order. To do that you need to change the Series Order. In 2007 this is none via the Select Data Source dialog. Chart Tools > Design > Data > Select Data. Use the Up/Down arrows in the...

how do i split a column
i have a worksheet with three columns but want to split column C in to two from row 37 down while keeping only three columns from rows 1-36. How can i do this? You can't. You could merge C1 & D1, C2 & d2 etc. but it seems a bit of overkill. What is the reason for this, there may be another solution? -- HTH RP "craig.d" <craig.d@discussions.microsoft.com> wrote in message news:973F49DB-EF87-4F64-AA13-7AEB3563757A@microsoft.com... > i have a worksheet with three columns but want to split column C in to two > from row 37 down while keeping only three col...

Disappearing Text
Hi. All my new and existing mail in Outlook Express has the subject line and the from populated, but the body of the message won't come up. We get the hourglass and it never goes away. Any ideas? Please email answers to kevinbrand@earthlink.net. Thanks Kevin Hi, Start --> Run --> Regsvr32 inetcomm.dll Hope this helps !!! With Regards, Sudharson.AN "Kevin" <anonymous@discussions.microsoft.com> wrote in message news:1284e01c41826$c0787480$a001280a@phx.gbl... > Hi. All my new and existing mail in Outlook Express has > the subject line and the from populate...

Inserting a string of text in front of existing text in excel
How do I insert a word or words in front of existing text cells on a multiple cell basis ? Hi try something like the following macro (works on your selection): sub foo() dim rng as range dim cell as range dim str_part set rng=selection str_part="precede_" for each cell in rng if cell.value<>"" then cell.value=str_part & cell.value end if next end sub >-----Original Message----- >How do I insert a word or words in front of existing text >cells on a multiple cell basis ? >. > If a formula will do, try something like this (with the existing text...

Calculate from another sheet
If i in a sheet have a1=1 , b1 =2 , c1 = 3 , d1 = 4, e1 = 5 and so on and in a2 = 100, B2 = 100, c2 = 100 , e2 =100 ans o on Then in another sheet i write maybe 4 then i want a cell there have a sum from 1 to 4 In this case from a2 to e2 if I write maybe 2 The a sum from a2 to b2 Can i do this? If 4 is A2 to E2, and 2 is A2 to B2, presumably 3 is A2 to about half way between C2 and D2? It sounds as if you want the OFFSET function; details in Excel help. -- David Biddulph "alvin Kuiper" <alvinKuiper@discussions.microsoft.com> wrote in message new...

OWA
OWA text field is always 'grayed out' - on new messages and replies. Small "x" button in upper left of text field seems to have no functionality. Subject field is OK. Bummer - do you have a question? If yes, then try asking it in an Exchange news group as Outlook Web Access is a function of Exchange, not Outlook. Microsoft.public.exchange.admin is a good place to start. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted w...

Problem creating full text index
I seem to be unable to create a full text index of either my public or private store. When I try to creat the index I get the error; The directory name is invalid. Facility: MSSearch.Admin.1 ID no: 8007010b Exchange system Manager This seems to be happening for both the public and private stores. The directory does exist. I have looked over the permissions and tried to compare to known working servers and I haven't moticed any obvious differences. Any ideas? Did you follow this article: http://support.microsoft.com/kb/822932/en-us Nue "Jonathan Huber" <JonathanHu...

Chart
Hello, I created a line column chart on 2 axis. I have four data sets. The first 3 should go on the primary Y axis as columns and the 4th should go on the secondary Y axis as a line. However, Excel wants to automatically put both the 3rd and 4th data sets on the secondary axis as lines. I cannot find any command to change the 3rd data set to go onto the primary axis as a column. Any suggestions would be most appreciated! -Patty On Tue, 11 Nov 2003 13:27:33 -0800, Patty = <anonymous@discussions.microsoft.com> wrote: > Hello, > > I created a line column chart on 2 ...

Formula Paste Button
Excel provides menu bar buttons for "format" and "value" pastes, but not for "formula" pastes. I've tried creating a macro that I could then create a menu bar button for, but it doesn't work (VBA says the "selection" is bad.) Does anyone know how to create a simple "formula paste" button for the menu bar? I haven't seen any threads on this. Thanks, -- alandry753 ------------------------------------------------------------------------ alandry753's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26700 V...

Access 2007 Special Effect Text Box Won't show Sunken with Windows XP
Hi All I am just getting to grips with Access 2007 and one of the quirks I have found is that when I design using Windows in XP Windows-Classic colors and set the text boxes on forms' special effect property to "sunken" They appear as sunken in the correct manner. However, when I change to the Windows XP color scheme the forms text boxes appear flat even though the special effect property has not changed. Any ideas or is it a new quirk we have to live with. I haven't tried it with Vista yet. That should be interesting Thanks Bob I am quite sure this is the same for acce...

converting from text to numerical number
I have a spread sheet of entire year schedule. The format as following: 1 2 3 .... 31 row 1 row 2 row 3 Jan row 4 row 5 Feb row x Dec I would like to replace column 1 up to Jan to 1. Replace the cell below Jan to Feb to 2. etc. Would anyone help. Thanks in advance. You could select cells A1:A3, type a 1, then press Ctrl+Enter Select A4:A5, type a 2, and press Ctrl+Enter And so on, till all twelve months are done. james wrote: > I have a spread sheet of entire year schedule. The format as following: > > 1 2 3 .... 31 &...

Input text message (dialog box) for data validation
Hello all: This may sound idiotic to some, but I've already been wasting too much time with this option, and am looking for some guru insight. I am trying to input text into the Data Validation Text Box that looks something like this: ------------------------------------------------------------------------------------------------------------------------------------- Q3) Was this a prospective or retrospective analysis or study documenting the effects of XYZ that compared and reported the data in the Marketing Strategy Program? a) Yes b) No c) This appears to be a concise comparison, but ot...

randomly scatter text
I have pub. 2003, I need to know how to take a phrase and randomly scatter it allover the page, with out placing it manually. I was able to do it on the last version of publisher but I can't seem to find the random button on this one. PLEASE HELP!!! Tabs? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jenice" <jenice@discussions.microsoft.com> wrote in message news:C2C51271-D425-4E3E-8A71-55365A05D774@microsoft.com... >I have pub. 2003, I need to know how to take a phrase and randomly scatter it > all...

Column names are 1,2,3,....
I just noticed today that the column names in my excel spreadsheets are no longer alphabetical, but are numerical. The column marked "1" is defined by excel as column "1C", and row "1" is defined as row "1R". How can I get the column names to change back to the alphabet? Thanks Hi! G! Use: Tools > Options > General Remove check from "R1C1 reference style" -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good...

Splitting multiple cell contents from 1st column into 4 columns
n my first column I have 4 cells (1-4) (5-8) etc. with general content. The content from each of these 4 cells needs to be placed in a separate column to make a list that I can sort etc. Example: COLUMN A 1 01-04-425-001 2 Heatherridge Road #301 3 Harry Smith 4 H25 Condo Fairfax Place 5 01-05-356-041 6 McGrath Street # 56 7 Mary Jones 8 B45 Condo Lemon Circle . . . . . . and so on and so on every 4 cells. I have hundreds of 4 cell descriptions. I just can't do this one by one. Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I use it for e...

Activate email column to hyperlinks
I have a colume of email addresses in an excel 2002 document. I need to make them active hyperlinks - How ? Microsoft instuctinos are useless here. can't they be converted in one batch to active hyperlinks so when the email address is clicked on, the email program opens in a fresh email ? Many thanks for any help http://www.mvps.org/dmcritchie/excel/buildtoc.htm#MakeHyperlinks -- Regards, Peo Sjoblom "DixieWins" <DixieWins@discussions.microsoft.com> wrote in message news:7F59C7F2-63DE-410B-B6FF-0EA29ECC8A35@microsoft.com... > I have a colume of email addr...

Sorting imported text
I have a spreadsheet into which I've imported a great deal of data in two columns only. I set out rows one, three and five of hundreds of such rows. The cells in column A consist of place names. (The "C" following the place name stands for "City", the "A" for "Area".) The cells in column C represent the population of the city or area, as the case may be. I've formatted the cells in column C as numbers. Albury (C) 44887 Armidale Dumaresq (A) 24596 Ashfield (A) 40258 I want to sort the placenames in order of their po...

Invalid column name 'ASSN_ENTSUMPROJID'
I have Project Server 2003 with SP3, SQL2005 database When i am saving a project to the Project Server, i am getting the following message: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'ASSN_ENTSUMPROJID' This is happning to just one project. Please help me understand the reason behind this and if you have a solution to fix this issue. Thanks VM Find the stored procedure executing that SQL command, name of the stored procedure is in the same line of the log (ULS). Go to the procedure and re-compile it, ensure there are no red-underlined lines. ...

Checkbox column in a table
Hi Everyone, I am a newbie in MS Access. I want to write a query for altering a table such that the new column added should be of type checkbox (which can be done by the design by going to Lookup tab in the design form). So when I open up the table that particular column should be like all filled with checkboxes. (By Default it will be Textboxes). And also how to give a default value to the checkbox ? Please help me out... ! On Tue, 25 Sep 2007 12:52:20 -0700, Cormalado <Cormalado@discussions.microsoft.com> wrote: >Hi Everyone, > >I am a newbie in MS Access. I want to w...

Text Function with Different Formatting for Number
I'm trying to use a text function where I reference a number from another cell but I need the text part to be bold and the number not to be bold. This is the formula I'm using right now: ="Sales: "&TEXT(A16,"$#,##0_);($#,##0)") Unfortunately, excel won't let me just make the text bold without making the number bold. Anyone have any suggestions? Thanks M, I think a macro could do this, since it's really a string you're going to wind up with. But if you can put the Sales: part in a separate cell, life will be much easier anyway. then you ...

Inserting Columns
I am trying to insert a column on a worksheet and keep getting the message: : "to prevent possible loss of data Microsoft Excel cannot shift nonblank cells off the worksheet". I am using Excel 2000. I have no protection set and no merged cells anywhere on the worksheet. It does no matter where I shift the cell pointer and try to do the insert, I get the above message. I have used Excel for many years and have never seen this problem before. I'm sure there must be something obviouis I am missing, but have no idea what it could be. Please help Thanks Thanks You ...