Combinning Multiple Lists

I am trying to combine three different lists into one without bein
limited to the space between the number of the lists. For example:

Fruit         Veggie         Meat
Orange    Carrot          Beef
Pear         Eggplant      Chicken
Apple        Broccoli        Veal
Lemon      Cabbage        
Lime

I want the three lists to become one like this:

Orange
Pear
Apple
Lemon
Lime
Carrot
Eggplant
Broccoli
Cabbage
Beef
Chicken
Veal

Having quite a hard time on doing this. The real hard part is that th
lists have variable lengths. And would perfer to not have a limit o
the length of the lists.

Thank You in advance

T

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 11:07:44 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
179 Views

Similar Articles

[PageSpeed] 10

I gleaned this code from the News Group or from a website.

Apologies to originator for not attributing.

Sub OneColumn()

''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet  '
''''''''''''''''''''''''''''''''''''''''''

Dim ilastcol As Long
Dim ilastrow As Long
Dim jlastrow As Long
Dim colndx As Long
Dim ws As Worksheet
Dim myrng As Range
Dim idx As Integer

    Set ws = ActiveWorkbook.ActiveSheet
    ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column

    With Sheets.Add
        .Name = "Alldata"
    End With

    idx = Sheets("Alldata").Index
    Sheets(idx + 1).Activate

    For colndx = 1 To ilastcol

        ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
        jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
                .End(xlUp).Row

        Set myrng = Range(Cells(1, colndx), _
                Cells(ilastrow, colndx))
        With myrng
            .Copy Sheets("Alldata").Cells(jlastrow + 1, 1)
        End With
    Next

    Sheets("Alldata").Rows("1:1").EntireRow.Delete

End Sub


Gord Dibben Excel MVP

On Wed, 7 Jul 2004 18:07:44 -0500, TK84916
<<TK84916.191qmv@excelforum-nospam.com>> wrote:

>I am trying to combine three different lists into one without being
>limited to the space between the number of the lists. For example:
>
>Fruit         Veggie         Meat
>Orange    Carrot          Beef
>Pear         Eggplant      Chicken
>Apple        Broccoli        Veal
>Lemon      Cabbage        
>Lime
>
>I want the three lists to become one like this:
>
>Orange
>Pear
>Apple
>Lemon
>Lime
>Carrot
>Eggplant
>Broccoli
>Cabbage
>Beef
>Chicken
>Veal
>
>Having quite a hard time on doing this. The real hard part is that the
>lists have variable lengths. And would perfer to not have a limit on
>the length of the lists.
>
>Thank You in advance
>
>TK
>
>
>---
>Message posted from http://www.ExcelForum.com/

0
Gord
7/8/2004 12:46:05 AM
Reply:

Similar Artilces:

Multiple Exchange Servers
Is it possible to install ms-crm email router on more then one Exchange Server in the same domain that will work with a single crm server? Also, is it possible to install the email-router on 2 exchange servers that one of them is in a different domain and both of them will work with a single crm server that resides in one of these domains? Is there a change in this cases in verion 3.0 (vNext)? There is definately a change for v3. The support for multiple servers is much better in v3, but not sure how it will handle the multiple Domain question. For v1.x, it could work, but depends a lot ...

Summary sheet for multiple sheets (difficult problem)
Here's the scenario: people owe money to companies. We assign each of these people a number, and create a separate sheet for each of the many companies. We need to compile a summary sheet (within the same workbook). Here's a sample sheet (let's call it Sample Company, which is the name of the sheet): Name ID # Amount John Jones 555 500 Jim Smith 123 175 Mary Long 232 100 Thus far the workbook has about twenty sheets (not counting the summary sheet) each named after a company and additional sheets are added freque...

Multiple Payees
I am using Money 2002 v10.0 My banking institution sends its transactions inclusive of transaction fees. At the moment I am 'Split'ing categories to show the fees, which makes my budget look OK. However the bank fees are showing against the Payees and not the Banking institute, so my Payee balances are all out. Is there a way I can also 'Split' a transaction into multiple payees as well as multiple categories EG.. Paying the phone bill: Phone company $20.00 Bank Fee $0.50 Total Trans. $20.50 Although I only paid the phone company $20.00 it shows as $20.50. ALter...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

How can I create a chart to compare multiple data series?
Using Excel, is there a way to merge multiple charts with different data series into one to demonstrate a direct comparison of the data series within one single chart. I've got 4 charts of the stacked column type. magnoliak77 Tufte calls them small multiples, Cleveland calls them trellis displays. I call them panel charts. Take a look at the examples on this page. http://processtrends.com/toc_panel_charts.htm Another option is to stick with your 4 charts, however, you can size and align them. Here's a link to a simple macro that sizes and aligns all charts on a single sheet....

changing colours of multiple items in Publisher 2000
Does anyone have any idea how I can change the colour of multiple items on a page? It is so time consuming to have to select and change the colour of each item individually - it would be so useful to be able to select all the items I need and change the colour en bloc. Do the newer Publisher packages have this facility (2003, 2007?) What items are you trying to chage? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Fluffbrain" <Fluffbrain@discussions.microsoft.com> wrote in message news:0B67BD6A-B0E8-45A9...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Multiple calls for Campaign
Hello I am attempting to set up a telemarketing campaign by using quick campaign in Microsoft CRM 4.0. I have set up the marketing list with multiple contacts and created a quick campaign for this marketing list. While creating the quick campaign, I selected phone call to all of the contacts within the marketing list. However, a phone call task was not created for every contact within the list. Only one phone call task was created for the entire marketing list. How can I create multiple phone calls for each and every contact within the marketing list. Kindly advise. Thank you ...

Filling list boxes
I am trying to use a table value function in a SQL server to fill a list box. I keep getting the following error message “Microsoft OLE DB Provider for SQL Server --> The parameter is incorrect.” When I the objCmd.Execute is Executed the statement in the code snipe below. Dim objConn As New ADODB.Connection Dim objCmd As New ADODB.Command Dim objParm1 As New ADODB.Parameter ' Set CommandText equal to the stored procedure name. objCmd.CommandText = "GetByModelUnassignedOptions" objCmd.CommandType = adCmdStoredProc ' Conn...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

multiple account registers
Been using Money for years, now I'm taking on my mother's finances and would like to track them in Money but in a separate account register. How can I do this, or other options for keeping her accounts separate from my own? In microsoft.public.money, Needinghelp! wrote: >Been using Money for years, now I'm taking on my mother's finances and would >like to track them in Money but in a separate account register. How can I do >this, or other options for keeping her accounts separate from my own? Use File->New->Newfile (or similar with an older version) to cre...

Average IFS (Multiple Criteria)
I have used the following formula and it works prefectly, however, one of my columns contains % and I want an average not a sum. Is there a way to use AVERAGEIFS to get the average only if other cells contain certain criteria? =SUMIFS(AF$4:AF$169,$G$4:$G$169,$G174,$F$4:$F$169,$F174,$J$4:$J$169,"<=12/31/10",$J$4:$J$169,">=01/01/10") ...

Emails to multiple recipients not being delivered
Windows 2000 Server SP4 Exchange 5.5 SP4 We've had multiple complaints about people not being able to send to multiple recipients in the "To:" field. For example: John Doe, Jane Smith, and Mike Brown are all listed as the "To:" recipients of an email. Jane Doe gets the email but nobody else listed does. We have had a few people tell us about this problem. Somtimes all recipipients will get the email but sometimes not. Are there any settings in Exchange 5.5 that will prevent this? Thanks in advance. Mr. Mike On Mon, 8 Aug 2005 16:02:02 -0700, "Mr. Mike"...

How to consolidate/sum a list
I have a simple spreadsheet in the following format ... Cell A1 with a heading Part Number, B1 heading Description and C1 heading Quantity. Under the headings is a list of 100 rows and a part number can appear more than once. What I would like to do is take all the unique part numbers and put them into a separate list and then sum the totals for these part numbers. Can anyone give me and idea how to do this please? Many thanks. -- Larry Wallis. Select the part numbers header included, do data>filter>advanced filter, select unique records only and copy to another location, assum...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

To Do List Miissing ?
When I open Outlook (2007), I get a dialogue box that says "There was a problem reading one or more of your reminders. Some reminders may not appear." Additionally, when I click on Tasks, and then attempt to view my To Do List, the screen says "Cannot display the folder", and when I click on Tasks, the bottom right screen says "The operation failed. An object could not be found." What happened? Is it easy to correct? SECOND REQUEST. Does anyone have an answer ? "Jan Groshan" <jangro@pacbell.net> wrote in message news:PuYyl.27204$ZP4.2...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

Category lists
I am using Windows XP home with Outlook 2003. I have set up two profiles "Home" and "Work" and set it so when I start Outlook I am asked which profile I wish to use. Which every profile I select when I start Outlook they both use the same category look up list and the same calendar colouring scheme i.e. what colour each appointment is in relation to the label. Is there anyway to get the two profiles using different look up lists and if so how? Thanks very much Shane No, unless you create separate user accounts. -- Robert Sparnaaij [MVP-Outlook] www....

Opening Multiple files in separate Excel folders
Can this be done? Hi what do you mean with 'separate Excel folders'? -- Regards Frank Kabel Frankfurt, Germany Paul Dunn wrote: > Can this be done? Yes. Dim aryFiles Dim oFSO Sub LoopFolders() Dim i As Integer Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object D...

List of items with no distributor
When I started with RMS, I didn't use the distributor field - big mistake. Now, I have approximately 1,000 items in my DB with no distributor listed. I was wondering if there was a way to figure out what all these items are, and preferably, a mass way of being able to change them to reflect having a distributor. I have found a few exports that seem to work using the wizards, but simply can't find one that will allow me to see all the items without a distributor. On top of this, maybe there is a "master" export that literally has every field available for an items. This e...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

newb question
Hi all, new Outlook 2003 user here with a problem. Here's the issue: I have 2 email accounts in my life, a hotmail account, and my work email account. I've set them both up as seperate email accounts in Outlook, and both work just fine. But here's the rub: In order to connect to my work email account (which is set-up as an exchange server email account) I have to first connect using VPN software on my PC. I only really want to check my work email once or twice a month, so therefore I don't want to fire-up my VPN software every time I open Outlook. My hotmail, on the other...

combinations
I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

Multiple Conditions
I am trying to populate a field on a form if certain conditions are met such as: If checkbox1 is true then display x If checkbox 2 is true then display y If both are true then display z If both are false then display w Any help will be greatly appreciated! Use Switch(), Choose() or nested IIf() expressions. See the help in Access for details an examples. If you actually have lots more checkboxes than the 2 in your example, you might get better results by redesigning the table: http://allenbrowne.com/casu-23.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for ...

Set up multiple email accounts on Outlook 2002, so that the mail .
I would like multiple email accounts, in separate folders, so my family, business and consumer mail do not comingle, whether sending or recieving. At present,, they are comingled, and I cannot find the right way to correct this. The easiest method is to use profiles, one for each purpose. Otherwise, you can use rules to move mail when receiving and sending. --� 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 searching...