Transpose Multiple Rows to one

Hello,

I'm trying to change the format of my data. Ex:

apple    2
ab        cd
orange  5
ef          gh
banana  4
cd         ef

And I want to get it to the format:

apple     2  ab   cd
orange   5  ef    gh
banana   4 cd   ef

Is there an easy way to do that? I can't figure it out with transpose or the 
offset function.

Thanks!
0
Utf
4/14/2010 2:57:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1437 Views

Similar Articles

[PageSpeed] 26

try this

Sub rearrangeem()
Dim i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
If lr / 2 <> Int(lr / 2) Then lr = lr - 1
 'MsgBox lr
For i = lr To 2 Step -2
Cells(i - 1, 3) = Cells(i, 1)
Cells(i - 1, 4) = Cells(i, 2)
Rows(i).Delete
Next i
End Sub
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message 
news:B37FAEE8-F62E-441F-8550-2723C5A91DFE@microsoft.com...
> Hello,
>
> I'm trying to change the format of my data. Ex:
>
> apple    2
> ab        cd
> orange  5
> ef          gh
> banana  4
> cd         ef
>
> And I want to get it to the format:
>
> apple     2  ab   cd
> orange   5  ef    gh
> banana   4 cd   ef
>
> Is there an easy way to do that? I can't figure it out with transpose or 
> the
> offset function.
>
> Thanks! 

0
Don
4/14/2010 3:12:19 PM
Thanks, but I'd like to do it without using a macro. Do you know of another 
way to do it?

"Don Guillett" wrote:

> try this
> 
> Sub rearrangeem()
> Dim i As Long
> lr = Cells(Rows.Count, 1).End(xlUp).Row
> If lr / 2 <> Int(lr / 2) Then lr = lr - 1
>  'MsgBox lr
> For i = lr To 2 Step -2
> Cells(i - 1, 3) = Cells(i, 1)
> Cells(i - 1, 4) = Cells(i, 2)
> Rows(i).Delete
> Next i
> End Sub
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message 
> news:B37FAEE8-F62E-441F-8550-2723C5A91DFE@microsoft.com...
> > Hello,
> >
> > I'm trying to change the format of my data. Ex:
> >
> > apple    2
> > ab        cd
> > orange  5
> > ef          gh
> > banana  4
> > cd         ef
> >
> > And I want to get it to the format:
> >
> > apple     2  ab   cd
> > orange   5  ef    gh
> > banana   4 cd   ef
> >
> > Is there an easy way to do that? I can't figure it out with transpose or 
> > the
> > offset function.
> >
> > Thanks! 
> 
> .
> 
0
Utf
4/14/2010 4:15:01 PM
I guess you could do with formulas>change to values>delete the no longer 
needed rows>Tedious.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message 
news:60F815BC-59E2-4F08-A971-80047EA20154@microsoft.com...
> Thanks, but I'd like to do it without using a macro. Do you know of 
> another
> way to do it?
>
> "Don Guillett" wrote:
>
>> try this
>>
>> Sub rearrangeem()
>> Dim i As Long
>> lr = Cells(Rows.Count, 1).End(xlUp).Row
>> If lr / 2 <> Int(lr / 2) Then lr = lr - 1
>>  'MsgBox lr
>> For i = lr To 2 Step -2
>> Cells(i - 1, 3) = Cells(i, 1)
>> Cells(i - 1, 4) = Cells(i, 2)
>> Rows(i).Delete
>> Next i
>> End Sub
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message
>> news:B37FAEE8-F62E-441F-8550-2723C5A91DFE@microsoft.com...
>> > Hello,
>> >
>> > I'm trying to change the format of my data. Ex:
>> >
>> > apple    2
>> > ab        cd
>> > orange  5
>> > ef          gh
>> > banana  4
>> > cd         ef
>> >
>> > And I want to get it to the format:
>> >
>> > apple     2  ab   cd
>> > orange   5  ef    gh
>> > banana   4 cd   ef
>> >
>> > Is there an easy way to do that? I can't figure it out with transpose 
>> > or
>> > the
>> > offset function.
>> >
>> > Thanks!
>>
>> .
>> 

0
Don
4/14/2010 4:20:34 PM
With your test data in A1:B6, I used this:

In C1:   =A2
In D1:   =B2

Then I selected C1:D2 (4 cells!)
And dragged it down as far as I needed.

Then I selected columns C:D and converted to values.
(edit|copy followed by edit|paste special values)

Then I selected just column C
Edit|Goto (or ctrl-g)
special
Blanks
Edit|Delete|entirerow



Demosthenes wrote:
> 
> Hello,
> 
> I'm trying to change the format of my data. Ex:
> 
> apple    2
> ab        cd
> orange  5
> ef          gh
> banana  4
> cd         ef
> 
> And I want to get it to the format:
> 
> apple     2  ab   cd
> orange   5  ef    gh
> banana   4 cd   ef
> 
> Is there an easy way to do that? I can't figure it out with transpose or the
> offset function.
> 
> Thanks!

-- 

Dave Peterson
0
Dave
4/14/2010 5:32:17 PM
Reply:

Similar Artilces:

tricky problem with 14800 rows (collating figures)
Hi, I'm trying to sort through a very large amount of data, and to get averages from it. (14800 ows in the full version !!). What I need to do is 1) Find out how many times each DELAY (column B1) appears for each AIRLINE (A1) and give a total for each airlines (This is to show which delay affects individual airlines the most) 2) Add up the CPU time spent on delays for each airline 3) Collate the number of ABENDs-periairline, and CPU time-per-airlines in to a grand total I need hedlp with this one as it is totally out of my depth. A1 B1 C1 AIRLINE DEL...

IF formula with multiple data range in cell
I'm trying to create an IF formula that returns data after searching a cell that contains more than one data range. For ex: Cell B3 contains GCVW-00001, GCVW-00002 I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need the formula in D3 to return Yes if B3 contains GCVW-00002 I'm able to get the formula to return yes or no correctly if cell B3 contains either GCVW-00001 or GCVW-00002, but the formula won't work if it contains both at the same time. right now my basic formula looks like: =IF(B3="GCVW-00001","Yes","No&q...

transposing info.
I want to transfer information from one spreadsheet to another with different titles on the columns. How do I do this? I want to direct the values on the column which has a different title column to the sheet that I want to move it to. Thanks guideme wrote: > I want to transfer information from one spreadsheet to another with > different titles on the columns. How do I do this? > > I want to direct the values on the column which has a different title > column to the sheet that I want to move it to. > > Thanks This is more of moving data to another sheet which has di...

one bar two colors?
I'm doing a simple bar chart, sales per year for the past 5 years, for this year I want the bar to be two colors, one color showing actual year-to-date and the rest another color for projected sales. how could I do this? Column A has years. Column B has actual data, whole year up to last year, and YTD for this year. Column C has blanks except for projected sales minus YTD for this year. Make a stacked column chart with these three columns. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ ...

Sending Direct E-Mail to Multiple Recipients
Does anyone know why a direct send of e-mail (CRM 4.0) would not work when sending to multiple recipients? I can send to one person at a time and the mail is sent with the activity created, however, two or more produces no emails or activities. Any assistance is greatly appreciated! We have been stuck on this one for quite some time. On Apr 23, 7:54=A0am, lhill1...@gmail.com wrote: > Does anyone know why a direct send of e-mail (CRM 4.0) would not work > when sending to multiple recipients? =A0I can send to one person at a > time and the mail is sent with the activity created, how...

Transposing list of numbers
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like to transpose it across 7 columns and 58 rows. Is there an easy way to do this? I understand I can do the copy/paste special/transpose for 7 at a time. Thank you. -- jlhcat Sub ColtoRows() Dim rng As Range Dim i As Long Dim J As Long Set rng = Cells(Rows.Count, 1).End(xlUp) J = 1 On Error Resume Next nocols = 7 'InputBox("Enter Number of Columns Desired") For i = 1 To rng.Row Step nocols Cells(J, "A").Resize(1, nocols).Value = _ ...

Transfer Roth from One Brokerage to Another?
I'm using the Microsoft Money Plus Deluxe Trial. How would I go about telling Money about transferring a Roth from one brokerage to another (I'm letting money download the data from my current brokerage)? Is there some way to, perhaps, change the Financial Institution and account number and then let Money continue as though nothing has happened? Or, do I have to close the old account and then open a new one and somehow account for the shares? In microsoft.public.money, David A. Lessnau wrote: >I'm using the Microsoft Money Plus Deluxe Trial. How would I go about >...

Transpose question
I have a MASTER summary worksheet with this layout 07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total No of late deliveries 36 40 20 10 9 115 No of Drivers 18 32 11 11 4 76 getting its info from WEEKLY worksheets eg 03-Sep 04-Sep 05-Sep 06-Sep 07-Sep Total No of late deliveries 7 8 7 7 7 36 No of Drivers 4 3 1 6 4 18 I would like the master layout to be like : No of late deliveries No of Drivers 07-Sep 36 18 14-Sep 40 32 21-Sep 20 11 28-Sep 10 11 05-Oct 9 4 Total 115 76 But as the values in the MASTER worksheet ...

Pivot table of data in more than one worksheet
Hi I have data whose numbers of rows exceed one worksheet.. ie. there are around 100,000 rows.. so I have this data in 2 worksheets.. the data is in continuation.. How can I pivot the data of both worksheets in one pivot table??? Thanks Hi Rehan this can be achieved using multiple consolidation ranges for your pivot table - check out http://www.contextures.com/xlPivot08.html for details Cheers JulieD "Rehan" <anonymous@discussions.microsoft.com> wrote in message news:0abe01c53053$ca11cc30$a401280a@phx.gbl... > Hi > > I have data whose numbers of rows exce...

multiple sales persons per customer
Some companies are set up so that a certain brand or class of items are sold by certain salespersons, which means that a single customer can be serviced by a number of sales persons. The system should have a relation of Item/Customer to sales person or Item Class/Customer to sales person. This will then allow a single customer to have several sales persons and because there is a relationship to the inventory item, when the item appears on a sales document the correct salesperson will be attributed to the sale. At the moment the system allows several sales persons per sales document but...

Edit classification for multiple transactions?
I have 6 months' worth of data entered in Money, and now feel comfortable enough to venture into the touchy topic of the monthly "who owes what to whom". Both my partner's and my accounts are all in Money, and I have decided to use a classification (For:Charles, For:Michael, For:Both) to label each expense. (Other suggested methods weren't appealing: creating duplicate categories like Food:Dining Out (Both), Food:Dining Out (Charles), and Food:Dining Out (Michael) would be a nightmare; and using splits on joint transactions to allocate half the amount to a separate "...

Addition if one value is greater
I have a dilema. In A1 I have (for instance) $100.00 There are other cells that I need to add to A1. I know what I need to do is use an IF statement and I can do that. I can evaluate IF one cell is greater than the other...but I need the formula to decide WHICH value is the greater of the two and THEN add it to A1. Any suggestions out there in 'Smarter than ME land?" Dave French =A1+MAX(B1,C1) -- Regards, Peo Sjoblom "MS Newsgroups" <dfrench@hopennmachinery.com> wrote in message news:uPFUN987HHA.5984@TK2MSFTNGP04.phx.gbl... >I have a dilema. > I...

Running Money Key Wizard Multiple Files
I need to run the MONEY KEY WIZARD fix on several files. Each time I go back to the fix page and click the link, it simply does nothing. Any ideas on running it against another file? thanks! Bart In microsoft.public.money, Bart wrote: >I need to run the MONEY KEY WIZARD fix on several files. >Each time I go back to the fix page and click the link, >it simply does nothing. > >Any ideas on running it against another file? Perhaps a different passport is on the file you are tying to access than the one you are currently signed into. See if http://groups.google.com/groups...

Multiple cell autocomplete
Anyone know if its possible to have multiple cells autocomplete like a single cell would if you were to type the same word or phrase in the cell. If it could work over mutliple cells by typing in a letter for example: COL1 COL2 COL3 COL4 CAT IN THE HAT If i just typed in "C" in COL1 that COL2, COL3, COL4 Would autocomplete and type in IN THE HAT Using M$ Excel 2002 on Windows 2000 Pro Thanks! Z With all the cells in question selected, type as normal and when your autocomplete gives you the word you are looking for, hit CTRL+ENTER and it will enter that data in all the se...

The OWA client and multiple SMTP domain names
My company has a subsidiary which we are about to add to our exchange server. They have a different domain name than the parent company. The first user from the subsidiary that I set up was not able to get to her mailbox via OWA. She got a 404 - page not found error. Experimentation has led me to decover that if she has a second email address of username@parentcompany.com then OWA will display her mailbox. Is there a way that I can configure these users with a single SMTP address of username@subsidiary.com (without screwing up all the users who work for the parent company) and allow them t...

Multiple Payslips
I have a report that prints out payslips, one to the page. There is however room for at least two payslips per page. I have searched for info on how to print multiple payslips per page without success. Any asistance would be greatly appreciated. Thanks There is only one record per payslip. The query that is the datasource returns a record for each payslip for every employee. This is an old database using access 97 "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message news:DB34A754-1E1E-4477-AE59-FD32B94C843B@microsoft.com... > Are there multiple records per p...

multiple users on same outlook in XP
I am running XP and outlook. I want the family to share the same outlook folder, and I apparantly can't do that. Is there a way around it, where I can only one installation of outlook shared by all that log in to the XP computer? If I am understanding you correctly, each user of the computer has his/her own login to Windows, and you would like all users to share the same data file for Outlook. Is that right? If so, you will need to put the .PST file in a shared folder that can be accessed by all Windows logins (normally the .PST file is installed in one profile only). Once you'...

Multiple columns into 0ne
Hi, We get a lot of information that comes into different columns, say Product 1 - 88. We never know how many values there are in every colum or how many Products there are Now we want a macro that puts all the values into one column, say A - can be in sheet 2. We do not want empty rows or columns but it is very important that the figures are in the original order. Regards More information is needed. Is there a header row? If so, what do you want to become of it? Might the be empty cells within the table? How do you recognize how many columns are used, or is the first row alw...

TSQL Transpose
Hi All! I'm trying to create a query that does a real transpose similar to Excel function - it is slightly different from Pivot/Unpivot functionality of SQL Server. Basically, I have a date dimension table and I need to list corresponding dates, separated by coma for each month. So, for January 2010, my results would be something like this: Month Dates 2010-01 01/01/2010, 01/02/2010, 01/03/2010...01/31/2010 What would be a way to achieve this? Thanks, Pasha Seems you just need to concatenate the date. Here is method using FOR XML PATH: SELECT DISTI...

Go to the last-new row in a datasheet view
What is the code to go to the last row where is added a new record when you type something? I tryed adding a record to the form recordset but I don't want to leave that record empty, I just want it to be ready to load data into. Greetings from Paraguay. Claudio Bogado Pompa. Use the GotoRecord method. It is explained in VBA Help. If you are using a macro, then it is the GotoRecord action, also in VBA Help. -- Dave Hargis, Microsoft Access MVP "Claudio Bogado Pompa" wrote: > What is the code to go to the last row where is added a new record > when you type something...

pivot table with multiple pivot tables as source
Hi, I have a workbook with 4 tabs with data, each tab is for a separate week of the month (week 1, week 2, etc). I've made Pivot tables for each week, and those are place on different tabs (labeled week1 summary, week2 summary, etc). However, I would like to build a pivot table to summarize the month, by creating a pivot table based on the four other pivot tables. However, if I use the pivot table wizard, it'll only let me choose one of the four. Is there a way to summarize the data across all the pivot tables? If you post a reply, please email me with the message as wel...

Outlook sometimes sends multiple e-mails
Recently, some of our e-mails have been sent/received many times. One message got sent 13 times!!! Using Outlook 2003, we've occasionally had messages with attachments get stuck in the Outbox and get sent over and over. We had to set Outlook to NOT send e-mails automatically in order to be able to delete the message from the Outbox. Has anyone else experienced these problems? Thanks. Stephen Green Los Angeles I am experiencing the same problem particular for message with attachment. Anyone bother to help? "Stephen Green" wrote: > Recently, some of our e-mails ...

Question re: Inserting Rows & Columns
I know that a row will always be inserted above the active row, but it is visually confusing to me in which direction new columns are inserted. Are they inserted to the right or left of the active column? You could always put a11,b1 2,c1 3 and insert to find out for yourself same thing on rows -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "sheana" <sheana@discussions.microsoft.com> wrote in message news:3E15C4FD-4F29-49AB-97D0-01E53E12CE5B@microsoft.com... >I know that a row will always be inserted above the active row, but it is > ...

Customizing multiple views
Greetings all, I was recently tasked with adding an extra column to the views on our leads, and initially found myself having to add the column to each of the seven or so views we were using one view at a time. I then needed to add yet another column, which was again applied to each view in turn. This has left me wondering if perhaps I've been stupid and missed something here, such as a "parent" view which the others inherit from (this appears not to be the case for the "Default Public View"), or some other way of making the same change to several views. Than...

transpose #2
How do I do a transpose in excel 2002? Thanks. Highlight your range > Right click Copy > select a blank cell > Right click Paste Special > Transpose > OK "abc" wrote: > How do I do a transpose in excel 2002? Thanks. > > > ...