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
1742 Views

Similar Articles

[PageSpeed] 10

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:

Pasting to Rows with with text only
How can I tell Excel to paste data in column A, but only to those row that contain data? I know there is a way, but how? Thanxs in advance... -- Message posted from http://www.ExcelForum.com Hi one way: - copy and paste your data (including formulas) - select column A and hit F5 - choose 'Special' and check 'formulas' - hit 'DEL' -- Regards Frank Kabel Frankfurt, Germany > How can I tell Excel to paste data in column A, but only to those rows > that contain data? I know there is a way, but how? > > Thanxs in advance.... > > > --- > Message ...

Insert Text and Send
I need a macro will insert specific text into the body of the replying email then send it. Any help is appriciated. Thanks ... Dave This example demonstrates how to change the Subject of a reply: http://www.vboffice.net/sample.html?mnu=2&smp=9&cmd=showitem&lang=en you can write your text into the Body or HTMLBody property. -- Best regards Michael Bauer - MVP Outlook Category Manager - Manage and share your categories: SAM - The Sending Account Manager: <http://www.vboffice.net/product.html?lang=en> Am Wed, 12 May 2010 13:42:01 -0700 schrieb ...

Increasing User's Mailbox Limit Past 2 Gb
In exchange 2000 I can give all users in the store same amount of space. or I can go into AD and change this setting per user under 'Exchange General' tab and then 'Storage Limits'. However I can't change it past 2 Gb. Is there a way of bypassing this through registry or some other method? thank you very much On Thu, 3 Nov 2005 20:59:59 -0500, "asdf" <asdf@asdf.com> wrote: >In exchange 2000 I can give all users in the store same amount of space. >or I can go into AD and change this setting per user under 'Exchange >General' >tab and ...

Changing text size
Hi, We enter all our customers in our database in all caps (text) for the names, and addresses. But when you put in the zip code and the database auto finds the town and state it is put in upper, and lower case. Is there a way to change this default to all caps for the auto fill. I am experienced in editing xml. files ( just a note) Any help would be appreciated.....Dennis No, there's no way to change that in the front end, that's something that is happening at display time and is meant to make the data more readable. You could enter a suggestion on Customer Sou...

Lost Ability to Include Selected Text in Replies
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I no longer seem to be able to select some text in a message, and begin a reply to that message quoting only the selected text. Either in HTML or plain text, the selected text does not appear. I have tried all Preferences>Compose, Reply permutations. Either all of the in-coming message is included, or none is included. Does anyone have a similar issue? On 3/2/10 11:04 AM, AndyBobyarchick@officeformac.com wrote: > I no longer seem to be able to select some text in a message, and > begin a reply to ...

Capitalize the 1st letter of text entered in a cell
I frequently enter text into cells. Sometimes the text is entered in lower case letters. I want the 1st letter to be capitalized regardless of my entry. Are there any suggestions? Thanks in advance. Michael You can only do this with code. There is no formatting command that can be used. Right-click on the appropriate worksheet tab, choose View Code, and paste in the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrH: If Target.Cells.Count > 1 Then Exit Sub End If If Not Application.Intersect(Target, _ Me.Range(&q...

How can I set Outlook 2003 default reply format to 'Plain Text'
Hello, Where is the setting which will default my reply in Outlook 2003 to be in Plain Text format? Currently I have to select the menu options "Format", "Plain Text" to force the reply to text etc. TIA Outlook respects the message format in which the message was received. You will need to force plain text. -- 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 without reading. After furious head-scratching, tech.Nik as...

Copying matched criteria plus relevant columns to new worksheet
I want to use code in a macro to search for criteria in a worksheet which allows the matched data to be copied (along with other relevant columns) to another worksheet in the same file. Would this involve some sort of loop? Any help apreciated. Thanks in advance, Matt there are several ways to do it. if you want to copy a hundred cells for three columns every time something in row 3 is matched somehting like the following could be used. sub try() tar=1 ' target column in sheet 2 for c = 1 to 34 step 3 ' this will be the look up column in sheet 1 if sheets("Sheet1&quo...

paste special keep odbc link
I often use worksheets returning the result of a query, with sometimes a huge number of rows/cells and formulas. In order to work easily on the result without having Excel still re-calculating the formulas or querying the database, I copy/special paste the result into a new workbook. There should be no link anymore and the last example I have is a sheet with +/- 2000 rows, no formulas, no odbc, but which still weights 7 Mo and needs a anormal delay when browsing from one cell to another ... as if there still was something else than just my datas !!!! How can I just have my datas and browse in...

Saving from Excel to text
I am trying to save an excel spreadsheet as a text (tab delimited) file but for some reason chracters are being replaced with a '?', does anyone know why? Any help would be greatly appreciated. Tracey - How are you saving the file? Try highlighting the spreadsheet, copy, open Word, paste. Highlight the text in Word, go to Tools, Table, Convert Table to Text, and choose the Tab delimiter. HTH, Carole >-----Original Message----- >I am trying to save an excel spreadsheet as a text (tab >delimited) file but for some reason chracters are being >replaced with a ...

Why is it picking multiple columns
When I click on a cell, it selects 8 cells. How do I reset it to only pick a column to work with? Thanks in advance. Bob Maybe you have merged cells. Select that group of 8 cells Rightclick|Format Cells|Alignment tab|Uncheck Merge Cells The Dog Breeders List wrote: > > When I click on a cell, it selects 8 cells. How do I reset it to only pick a > column to work with? > Thanks in advance. > Bob -- Dave Peterson try the f8 key or beat up your scroll mouse -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "The Dog Breeders List" &...

numbers as text
Using Excel 2007 I have entered a long numeric string (a bank account number) as text. The entire Excel worksheet is then saved as a .csv file. The file is closed. When the file is reopened, the long numeric string has been converted to a scientific format (1.45684E+11) which can be converted again to text but will not keep that format during the save process. The bank is requiring the file to be saved in .csv format, so I don't have the option of saving in Excel format. Any suggestions???? On Tue, 15 Dec 2009 15:03:38 -0600, "BK" <nospam@nospam.co...

adding new columns sheet with external data
I have a excel sheet that I import data from a external database and then i added columns to the sheet that have entries made by me. When the data refreshes if the data is no longer coming from the external it leaves the data that i entered still there. I want it to delete the whole row and leave no data at all external or internal for that line that now does not exist can this be done? ...

cut and paste filenames from explorer window
How can I cut and paste a filename from an explorer window into an excel spreadsheet. david Select the file in Explorer, then click on it again - as if you're about to rename it. That allows you to right-click and Copy and left-click and Paste in Excel. Andy. "david" <david.champon@comcast.net> wrote in message news:0ed101c37c69$601253d0$a401280a@phx.gbl... > How can I cut and paste a filename from an explorer window > into an excel spreadsheet. Hi david You could try this in a cell on your worksheet =cell("filename") (this is atually "filename...

Rotating Text Boxes on a Chart
Excel won't let me rotate text boxes in my pie chart. When I go to do it the rotate buttons are greyed out. Also I can't figure out how to add text the curves with the outside of the pie chart. Please help! Thanx! Hi Jeff, You won't be able to rotate textboxes. If they are really data labels you can alter their alignment. But they won't curve. If you want text to wrap around the pie you could try using word art. Although a pie with more than a few slices will probably result in text not appearing next to the slice. Jeff wrote: > Excel won't let me rotate...

Pasting range to a graphic
I have in the past, converted a range of cells to a graphic, however, I have forgot. Can someone bring me back up to speed? Select your range and Shift-Edit|Copy Picture???? There's an option for Shift-Edit|Paste picture (or paste picture link), too. "Luther Gunter Jr." wrote: > > I have in the past, converted a range of cells to a graphic, however, I have > forgot. Can someone bring me back up to speed? -- Dave Peterson Thanks, it worked like a charm. "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message news:42685547.B819221A@netsc...

Text Search Macro?
Hi-- I have a large spreadsheet with a great deal of text. The workbook is shared on a network of mostly Excel-phobic users who will need to be able to quickly find text in the sheet. Of course, they could simply use the "Find" feature, but I just KNOW that that will be a trap. I'd like to create a macro that, when run, will generate a pop-up box into which the user will type the text they want to find, and which will find the text in the sheet when the user clicks "GO." (I'll connect up the macro to a big, friendly button marked "Press Me to Search,&qu...

Center Text
Hello all, I have what would seem to be a very small problem, but I am unable to answer it myself. I have a report that I am trying to create and each record can have one of three categories. If there are multiple records for, let's say, Group1, then I want a text box to span both records and center the text within the report. This would be the equivalent of a rowspan in html. I have no idea where to start. Am I missing something really simple? The data is structured as the following: Group1 | Record 1 | Some cool info Group1 | Record 2 | Some cool info Group 2 | R...

Average time from date time column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...

how do you stop excel changing text
When I transfer names from quick address to an Excel spreadsheet it changes Mr to Mrs, Chester to Chesterfield, Lee to Leeds and so on. Is there any way that I can make it accept what is on the quick address. Hi have you checked 'Tools - autocorrection' -- Regards Frank Kabel Frankfurt, Germany "MK Chester" <MK Chester@discussions.microsoft.com> schrieb im Newsbeitrag news:4BB859A1-0871-410E-8DBC-C35EDB5DD071@microsoft.com... > When I transfer names from quick address to an Excel spreadsheet it changes > Mr to Mrs, Chester to Chesterfield, Lee to Leeds and s...

Adding plain text to a database?
I have some instructions (about 2-3 paragraphs) that need to accompany one of my databases. Rather than sending them separately where they might get lost, how could I include them within the database file? Why not simply create a form and enter the text there. then you can add a button so the user can access the info when they need it. -- Hope this helps, Daniel Pineault "Cheese" wrote: > I have some instructions (about 2-3 paragraphs) that need to accompany one of > my databases. > > Rather than sending them separately where they might get lost, how co...

How to Select large amount of data in a colum that equals column from another table.
Ive got a huge table and I want to select the data in one column that is equal to that of a column in another table? is there an easy way to do this? Thanks One way, if you know the parameters of the original, is to type the references in the name box and hit <Enter>. For example, click in the name box and enter: E1:E65000 Then <Enter>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jennifer Burnel" <jenburnel@babooshka.net> wro...

superscript text in legend
I have created some superscript text in some of the column headings in my table, i.e., Gender[a], Education[b], with [a] and [b] being superscriptted. However, in the legend of my chart, the superscript text just appears regular size, so it looks like I have committed typos. Is there a way I can maintain the "superscriptness" of certain characters in my legend text? No. Unfortunately, when a chart uses a cell to title (or text box) it does not take the format with it. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "hogon" <hogon@discus...

Text box visibility
On my form "Finalise Jobs" i have (amongst other controls), "Date ready" - txtDateReady, "Date Required" - txtDateRequired, "WhyDelayed" - txtWhyDelayed, and a search Combo Box - cboSearch. The following code is attached to txtDateReady :- Private Sub txtDateReady_AfterUpdate() If txtDateReady > Forms![Finalise Jobs].txtDateRequired Then Me.lblWhyDelayed.Visible = True Me.txtWhyDelayed.Visible = True Else Me.lblWhyDelayed.Visible = False Me.txtWhyDelayed.Visible = False End If End Sub and the following...

Cutting and pasting annoyance
I frequently copy large amounts of data in msexcel. So often I get the warning that the amount on clipbaord is too large to keep. Is there a way to turn this off in: properties, registry, whatever... I would be happy if it always assumed that it is ok to dump the clipboard contents. When are you getting this message--when you're closing the workbook (or closing excel)? If yes, you can hit the escape key to stop that warning. It clears Excel's clipboard, too. TSH wrote: > > I frequently copy large amounts of data in msexcel. > So often I get the warning that the amoun...