One Entry to Multiple Rows

I have data that looks like this:
X1  | Y1 Y2 Y3 Y4 
X2  | Y4 Y5 Y6 Y7

And I need to get to:
X1 | Y1
X1 | Y2
X1 | Y3
X1 | Y4
X2 | Y4
...... etc.

I can change the 2nd row's entries to more columns, but that doesn't seem to 
get me much closer to the needed format (and there are thousands of lines so 
I'd rather not do it manually).  Any ideas?  
0
Utf
1/21/2010 9:31:11 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
708 Views

Similar Articles

[PageSpeed] 49

should do it. change mc to suit
'=====
Option Explicit
Sub lineemup()
Dim mc As Long
Dim mr As Long
Dim i As Long
Dim lc As Long

mc = 3 'col c
mr = 1
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
lc = Cells(i, Columns.Count). _
End(xlToLeft).Column - mc
Cells(i, mc).Resize(, lc).Copy
Cells(mr, mc - 1).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
mr = mr + lc
Next i
Application.CutCopyMode = False
End Sub


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Pandaura" <Pandaura@discussions.microsoft.com> wrote in message 
news:518F98FB-D269-42F4-A42B-E2304E8ADB65@microsoft.com...
>I have data that looks like this:
> X1  | Y1 Y2 Y3 Y4
> X2  | Y4 Y5 Y6 Y7
>
> And I need to get to:
> X1 | Y1
> X1 | Y2
> X1 | Y3
> X1 | Y4
> X2 | Y4
> ..... etc.
>
> I can change the 2nd row's entries to more columns, but that doesn't seem 
> to
> get me much closer to the needed format (and there are thousands of lines 
> so
> I'd rather not do it manually).  Any ideas? 

0
Don
1/21/2010 10:39:37 PM
Reply:

Similar Artilces:

Error Entry Point Not Found.
When I start Outlook with default to show outlook today, I get the following error message: "The procedure entry point GetIUMS could not be located in the dynamic link library MSDART.DLL. If I switch it back to starting in inbox I do not get this message. I only get it when it tries to access outlook today. Can anyone help? Thanks I'm getting the same exact thing. If you find a cure, could you repost. Thanks >-----Original Message----- >When I start Outlook with default to show outlook today, >I get the following error message: "The procedure entry >poi...

deleting multiple rows
Hello: I need to be able to delete multiple rows out of a worksheet. What I need to be able to do is have a function/macro that can identify a string of characters and then delete all the rows that have those characters. For example, if row 4, 18, and 45 all had "blue" in them (assume column is A), then I need something to go delete row, 4, 18, and 45. At the top of my worksheet I will have several, probably about 3, criteria, such as, "blue", "red", and "green" that will all need to be deleted. Now here's where I really need help. A row may ...

Multiple name appearances
Have a list of 1000 names trying to sort by appearances >5. Not sure if Lookup or Hlookup? Pat318 wrote: > Have a list of 1000 names trying to sort by appearances >5. > Not sure if Lookup or Hlookup? If your list is in A1:A1000, put this in B1:B1000 and sort by column B: =COUNTIF($A$1:$A$1000,A1) ...

Decreasing Row Height MS Project 2003
Hi, This is very very lame, but I think I have done something to "block" row heights in MS Project. I want to decrease them, but I simply can't! I can increase them though... Please help!! Thanks Hi, Through Bar or Bar Styles, did you add text above or below a bar? That wil force teh row height to no less that 2. HTH -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "Diogo" <Diogo@discussions.microsoft.com> wrote in message n...

Copy cell to next sheet in next available row
I have a two sheet workbook. The first sheet named "Financial_Data" performs the calculations and saves the total to a cell named PTotal. I am trying to develop a macro to open the next sheet (Historical_Data), insert the date, then move into the next column and paste the PTotal cell contents. Everytime the macro is run, preferably daily, I would like it to drop to the next empty row and paste the current info. When I run the macro below, it overwrites what was previously there. Can anyone help with this? Sub HistDataMove() Sheets("Historical_Data").Select ...

Does anyone know how to assign tasks to multiple individuals?
We use organizational boxes and have ocassion to send tasks out to other org boxes (6 usually). The problem is that you (apparently) lose the ability to track individually when you do this. Does anyone have any ideas, other than assigning 6 separate tasks (1 for each org box...). Thanks! ...

Outlook - CPU Spikes
I've got a Dell XPS M1210 - Outlook 2003 Pro We have 4 of these machines - I personally have one as well. Mine works great no problem.... however one of the others is a problem child. I've personally re-installed the OS 4 times on this machine. When you bring up outlook (with Exchange account) I get CPU usage spikes (40%-60%). Explorer.exe shows in the task manager as the culprit. Shut down outlook and it chills down to 0%-2%. Bring outlook back up .... it has a CPU spike heartbeat every 5 seconds or so. I've set the email account on several other machines ... no problem.. so I...

Copying inbox from one account to another on exchange 2003
Hi I need to copy the inbox of user from one account to another account. Is there a way to do that? I tried copying to the public folder then copying from public to new account - but the dates changed to todays date for all emails! Any thoughts? Use PSTs. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Ali" <Ali@discussions.microsoft.com> wrote in message news:B67F087A-39D7-4DBA-947D-F20F63574952@microsoft.com... > Hi > I need to copy the inbox of user from one account to another account. Is > t...

Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths. Is this possible? -The 1st sub-report has 1 column that occupies the entire width of the page -The 2nd sub-report can fit 2 columns in the page width -The 3rd sub-report can fit 3 columns in the page width Subreports can have any number of columns that don't have to be the same from one to another. Typically your columns should display across then down in order to render properly as a subreport. -- Duane Hookom Microsoft Access MVP "SheldonHinds" wrote: > I want to create a r...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Sync multiple e-mail accounts
I run Outlook 2007 and Windows Mobile 6. How do I get my contacts and calendar to syncronize between my phone and two e-mail accounts on my computer (G-mail and kiwilink)? "Need to Sync" <Need to Sync@discussions.microsoft.com> wrote in message news:2ED27720-6208-41A4-BED3-F875E32D4FEB@microsoft.com... >I run Outlook 2007 and Windows Mobile 6. How do I get my contacts and > calendar to syncronize between my phone and two e-mail accounts on my > computer (G-mail and kiwilink)? Google Sync should work for gmail. I have no idea what "kiwilink"...

Adding Hyperlink to multiple values within a cell
My spreadsheet contains a list of people. The cell next to each nam contains multiple numeric values for identifying a specific piece o information. I would like to be able to click on one of those number (value) and a comment window pop up with the information associate with it, or be hyperlinked to the information further down th speadsheet. I want to avoid using multiple cells for this. Is this possible? Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this...

How do you import data from one Excel document to another?
Hey everyone, I work at a school and I'm trying to update their attendance reports from hand written to excel, but I'm running into several problems. For one, I need it to be able to calculate how many "Ab"s (absents) there are in the worksheet (for the individual student's attendance page). And THEN, I need to have the sum show up on a completely new document (for the overall attendance report). Is that possible? I've fooled around with data importing and several other things, but I'm just too new at this. If what I'm asking is possible, can anyone he...

Data Entry
I know that you can go to tools/options/edit to change the next cell after you enter. I would like to go across the row instead of down the column. Is there any way to make that selection just for a workbook, . The change seems to be global until you change the preference again. Thanks, one way is to add this code in your sheet code Private Sub Worksheet_Change(ByVal Target As Range) ActiveCell.Offset(-1, 1).Activate End Sub play with it. Cesar Zapata John wrote: > I know that you can go to tools/options/edit to change the next cell after > you enter. I would like to ...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Combine rows to print on one label
I have imported student information into an excel spreadsheet (use Excel 2003). Each student has one row of information for each course they take. I need to print one label for each student and each course & grade must print on a single label. How do I combine the rows to cause this to happen? assuming the info in the row is divided into separate columns for name, course, grade, etc you can do a mail merge in word using the spreadsheet as the data source "Clearcreek" wrote: > I have imported student information into an excel spreadsheet (use Excel > 2...

How to get rid of unwanted entries in the Outlook CRM tree view
Hello, If you use the crm outlook client you can easily create a new entry in the tree view of Microsoft CRM. But it how can you remove this entry again? Only by re-installing CRM? Thanks, Alex Are you talking for CRM 3, Humm, that actually sounds like a bug in outlook. -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Alex (crm-resultants.nl)" wrote: > Hello, > > If you use the crm outlook client you can easily create a new entry in the > tree view of Microsof...

My recipients recieving multiple copies
I'm using Outlook 2002 with Win XP Pro. I have dial up (POP3 & SMTP) in a little town with old phone lines. My recipients (never more than 50) sometimes recieve from 2 to 10 copies of the same message. I have increased the server time-out time from 1 minute to 9 minutes ( I figured that out on my own) but I'm not yet sure if that's the solution because my ISP guy tells me that Outlook really wasn't designed to send one message to more than 3 recipients esp. Bcc (I find that remark very hard to believe). My ISP guy tells me I should get an email program designed t...

Multiple stationeries #2
Hi We have three divisions in the company and need different stationery for each of them. Outlook, as far as I can see, only supports one stationery at a time. Is there a way to create three stationeries as html files and then load them somehow, may be via vba code, using buttons on toolbar? Is there a better way? Thanks Regards If you create three .html files and store them in the C:\Program Files\Common Files\Microsoft Shared\Stationery directory, they will be accessible from within the stationery chooser. -- Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.) Try Pic...

All in one!
It is a lot of all - Hottest Offers - Gifts, Discounts, Sale, Fre Stuff, Freebies, Coupons and more you will find at us. UK Best offers Discounts, Free Stuff, Freebies, Coupons, Gifts, Sale, Hottest Offers 'Coupons, Gifts, Sale' (http://www.freestuffleague.co.uk -- maxaoff ----------------------------------------------------------------------- maxaoffi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3779 View this thread: http://www.excelforum.com/showthread.php?threadid=57355 ...

Multiple Email Accounts in Windows Live Mail
I added a yahoo.com email address within Windows Live Mail and decided against it. I removed it from the Live Mail properties but yahoo emails continue to post within Live Mail desktop. I've checked the obvious settings but can't find anything to change to make this stop. I want to totally remove yahoo.com email from Windows Live and access yahoo emails from the Internet. Do you by chance have a Hotmail account and in the account on the web site told it fetch mail from Yahoo? Hotmail website: Options, More Options,. Send and receive mail from other e-mail account...

deleting duplicate rows
I have an import file that might have duplicates that I don't want to deal with. I'm creating a temp table from the import file and want to delete the duplicates from the temp table before proceding with other code. Here's an example table: create table #temp (itemnum int, firstname varchar(10), notes varchar(50)) insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some note') insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some other note') insert into #temp (itemnum, firstname, notes) values (2, ...

is it possible to host multiple CRM databases on 1 SQL server?
Hi, Is it possible to host two separate (on premise) CRM server databases on one SQL server? I am trying to achieve the above scenario, but there can only be one MSCRM_config database on any one instance of the SQL server. The Implementation guide isn't too clear about what to do in this situation. - Can two CRM databases share the same MSCRM_config file? - Do I need to install another instance of SQL server for the second CRM database? Or is there another solution? Thanks Depends on your CRM installation. If you are running the Enterprise version, then you can add a new org...

remove carriage return in multiple cells
Hello, I have an excel sheet with lots of data. Unfortunately lots of cells contain carriage returns. Is there a way to remove the carriage returns in those cells (more returns in a cell possible)? Thanks, Andy > Andy Select your range, then CTRL+H. In Find What, type 0010 (number keypad) while holding down ALT. Click Replace All. HTH, Andy Try running this macro: Sub RemoveCRLF() Cells.Replace Chr(10), "" Cells.Replace Chr(13), "" End Sub Afterward you may want to turn off Wrap Text (under Format, Cells, Alignment) for the affected cells. -- Jim...

CComboBox shows only one item at a time !
Hi All, I am using CComboBox control and have tried both dropdown and dropddown list styles. The problem is that I have several items in the combo box but dropdown shows only 1 at a time (i.e. the arrows of v-scrollbar are stuck together).. How do I remedy this ? Thanks in advance. Ashish Got it... Thanks "Ashish" <abc@def.com> wrote in message news:eqRb$LSyDHA.2304@TK2MSFTNGP12.phx.gbl... > Hi All, > I am using CComboBox control and have tried both dropdown and dropddown list > styles. The problem is that I have several items in the combo box but > dropdown sho...