Re: Function to copy data from a variable range?

I have a sheet with about 20,000 lines of data.

From that sheet I am looking to group the data based on a parameter,
into multiple different sheets. ie: If the category is "1" copy all
that lines data into sheet A, If the category is "2", copy all the
lines into sheet B.

The problem is, the number of lines for each acatgeory type is not
always the same. Some categories may only have 1 or two lines, other
may have 200-300.

Is there any type of function for this?

Thanks!
0
10/7/2009 7:39:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
384 Views

Similar Articles

[PageSpeed] 42

Doable but why not just use data>filter>autofilter

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<cjwenngatz@gmail.com> wrote in message 
news:571f4b6d-82ad-4bd1-b515-501ec805564b@v2g2000vbb.googlegroups.com...
>I have a sheet with about 20,000 lines of data.
>
> From that sheet I am looking to group the data based on a parameter,
> into multiple different sheets. ie: If the category is "1" copy all
> that lines data into sheet A, If the category is "2", copy all the
> lines into sheet B.
>
> The problem is, the number of lines for each acatgeory type is not
> always the same. Some categories may only have 1 or two lines, other
> may have 200-300.
>
> Is there any type of function for this?
>
> Thanks! 

0
dguillett1 (2487)
10/7/2009 10:42:53 PM
It's a little complex to explain, but this has to do multiple things,
so drawing each category of data to a specific sheet is a huge
advantage.

Would you be able to outline how I can do that?

Much appreciated! :)

0
10/8/2009 9:08:30 PM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<cjwenngatz@gmail.com> wrote in message 
news:9554bf06-aa9b-45fc-a50f-380b161de6f7@g3g2000vbr.googlegroups.com...
> It's a little complex to explain, but this has to do multiple things,
> so drawing each category of data to a specific sheet is a huge
> advantage.
>
> Would you be able to outline how I can do that?
>
> Much appreciated! :)
> 

0
dguillett1 (2487)
10/8/2009 9:22:46 PM
Reply:

Similar Artilces:

Updating through subform not functioning properly or as desired
I have a problem with a subform not updating with table information after the initial input. First, let me present the setup of the main form creation. 1. I have created a main form (Employee Training) using two tab pages on the form. 2. The first tab (Employee Information) is used to collect data for the primary employee table. 3. The second tab (Job Competency) has an embedded subform to collect training that an employee has received and stores it in a Job Competency table. Here is the problem in the order experience. 1. Starting up the form and entering the employee information o...

automate copying of mails in outlook
hi, how can i automate copying of mails in outlook public folders to C drive? thanks See http://www.slipstick.com/addins/housekeeping.htm for tools that may be able to help. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Lynn" <MarryLynn@yah00.c0m> wrote in message news:%2394KYEFBFHA.1264@TK2MSFTNGP12.phx.gbl... > hi, > how can i automate copying of mails in outlook public folders to C drive? > thanks > > is there ...

How do I merge similar data across different excel worksheets?
If you have a unique key value that's on each sheet, you could use =vlookup() or =index(match()). You could look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions02.html and http://www.contextures.com/xlFunctions03.html MK wrote: -- Dave Peterson ...

Help to import data from reference workbook
I wasn't sure if this would require VB or not so I've put this in the General Question group. I have 2 workbooks where the first 3 columns of each row (after a title row) consist of an ID number, a North coordinate and an East Coordinate. Workbook A is complete in that every ID for our system is included along with the coresponding North and East coords. Workbook B includes a subset of the IDs and only some of the IDs have their coords in columns B and C. How do I import the coords into Workbook B without having to copy/paste a thousand or more times. Thanks in advance for a...

Activating bold function takes time
I have a PC which is having Office XP installed in it. When i try to make a text in a cell bold the first time in any worksheet, it takes aprroximately 4-5 mins, but the very next moment if i want to make another cell bold, it happens as normal. can u help with the reason why it so happens & is there any solution for the same. I also tried reinstalling Office (different version) twice after removing the previous installed version but the problem is still persisting. I think I've read posts that say that when this kind of thing happens, it's usually because the current printe...

Presenting data from database
I have an idea to allow users to enter data through web interface but I wish to have output in Excell file, normal table with sum values or just data from database. What is the easiest way to make something like this ? regards Peter ...

Advanced Data Validation
Dear all, I wonder how can I type the first letter of a word in a cell which has data validation on a list of names. For example, the list of data is "Apple, Banana, Bicycle, Orange...". If I type "b" in the cell, the 1st word starting with "b" will appear i.e. Banana. Therefore I do not need to scroll the down the arrow to choose "Banana". Hope someone can give advice. Louis Data|Validation doesn't work like this. But you could use a combobox from the control toolbox toolbar to get this effect. Louis.kk.lam@gmail.com wrote: > > Dear all...

custom function that reads user information
Hi, I am looking for a custom function that reads some kind of use information (either author name, or even excel registration number that is somehow unique (so best choice might be registration number and that is stored somewhere on a persons computer. I want to incorporate an If statement, so that the function onl returns a proper value if the information read from the person' machine is identical to that specified in the function. Any help is greatly appreciated! a -- Message posted from http://www.ExcelForum.com Check this previous posting http://tinyurl.com/32nuj -- HTH ...

Copy data into multiple workbooks
Within the QA Master workbook, the boss will have a list of questions (criteria) that will be mirrored in all employee workbooks and the QA Template. If the boss adds a criteria in the QA Master workbook, I need to add it to the QA Template workbook and all employee workbooks so he does not have to go through 80+ workbooks individually. If you can provide the code to do this for one cell in QA Master and place it in QA Template, I believe I can figure out how to get it to the employee workbooks. Also, will I need to open each workbook in order to place the new criteria int...

Copy from many cells and paste to one
I need to copy a range of cells (C7:C20) and past the contents into one cell (B4). the concatenate function is not the solution because I need the results to appear as separate lines in the cell (B4) when pasted. (leaving a "return" between lines) Weird I know but Ahem, "It's for a friend". ;) Thanks, Robert -- eoreality ------------------------------------------------------------------------ eoreality's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24549 View this thread: http://www.excelforum.com/showthread.php?threadid=381448 ...

Linked Text Boxes with Exact Objects copy/paste
Hello All, I am trying to change the layout of a 8.5" x 11" four page document to a two page 17" x 11" document. The four pager consist of four linked text boxes as pages with lots of graphics in each. When I rope the textbox plus the graphics, copy and then paste I only get the an empty textbox plus the graphics, the text is gone. I think this has something to do with the graphic objects Layout set to exact, but I would have to spend a few hours changing all of the objects to inline. Time is what I lack. Is there a way to do this. That is, copy and paste linked tex...

Re-sizing and Dynamic Connectors
I am having some difficultly with resizing and Dynamic Connectors. I have this wonderful end node diagram that is positioned just perfectly for "E" size paper. I am required to print it on "D" sized paper. I have used Dynamic Connectors exclusively. I select all and resize the diagram. The Dynamic Connectors move all over the page, it seems that none stay where they were. I will have to go in and spend a day or two to put them back to where they were, so that it will look as perfect as when it is printed on the "E" size. What trick or feature am I n...

Copy formulas in Column
Hi, I have to copy numbers from cell a1, b1,c1...........z1 to a20,a21,a22,a23... so. for e.g-- a1 = 21 b1 = 23 c1 = 24 etc z1 = 40 i need to copy as follows:- a20 = a1 a21 = b1 a22 = c1 Is there an easy way to copy the numbers instead of typing one by one .... I have many records to copy..... Thanks for your kind help :) Meeru --Select the range A1:Z1 and copy --Select cell A20. Right click>PasteSpecial>check 'Transpose' and click OK -- Jacob "Meeru" wrote: > Hi, > > I have to copy numbers from cell a1, b1,c1...........

Data available for plotting vs already plotted data
Hello -- This charting application has the following features: 1 - user can "import" a time series (into a set of columns in a worksheet); the app automatically adds a plot of the data to the chart 2 - user can delete from the chart a series which was plotted in 1; the source data is not deleted from the worksheet which holds it 3 - user can decide later to add to the chart a series deleted in 2 Problem: I want to present in a listbox the names/legends of the "imported but not plotted" series so the user can select one for 3 If I could use SQL in this app, the source ...

How to copy Contacts data on my Notebook's Outlook 2007 to Addres Book of Outlook Express on desktop PC
I have entered most of my friends' data on my Notebook's Outlook 2007. However, on my home desktop PC, I'm using Outlook Express 6. How do I transfer the Notebook's Outlook Contacts to the Desktop's Outlook Express Address Book? I use Win XP on both machines. I was told Outlook stores its Contacts data in a Contacts Folder, while Outlook Express reads only files with wab extensions and it's tricky to Import a Contacts folder to a wab file. Pse help. Thank you. "Jimmy Hsu" <jimmyhsu@tifventures.com> wrote in message news:fjrb5f$pn2$1@maw...

Insert data from a Dataset into a SQL Database
Hi guys my name is Ralph, i am kinda new to this so please bear with me. I have written a .net web service which accepts a stream of data see relevent source code). Public Function PutBondInfo(ByVal data As String) As DataSet Try Dim zh As New ZipHelper Dim sz As String Dim cn As SqlConnection Dim da As SqlDataAdapter Dim ds As New DataSet Dim xSet As New DataSet Dim sSql As String Dim mySerializer As XmlSerializer = New XmlSerializer(ds.GetType()) cn = New SqlConnection(&quo...

How do I delete cells in Excel without changing the data in others
I want to get rid of some of the rows in my spreadsheet but the data in the ones I am leaving behind are linked by formulas? As soon as I delete them all the data goes from the the others. Gemma, I may not understand your situation. An example of what I think you're saying is: Cell A2: A1+1. You want to delete cell A1, but leave A2 with the value it currently has. If this is the case, you need to copy A2 and the, using paste special, paste the value back into A2. This way A2 no longer has any formula at all and will remain unchanged when you delete A1. Art "G...

Nested IF function
I think I need a nested IF function, within an IF function. Row 5 Column E- Formula I want Row 5 Column F- 10 Row 5 Column G- 10 Row 6 Column E- Formula I want Row 6 Column F- 10 Row 6 Column G- 9 The first part of the formula I have In E5- IF(F5<F6,"A","B") In E6- IF(F6<F5,"A","B") Formula I want: If the numbers in F5 & F6 are equal, Then it should evaluate the numbers in G5 & G6 to determine "A" or "B" Thanks, Tom picktr@wowway.com P.S - This is comparing two persons and numbers t...

Lookup Data in external workbook
Hello: I have a worksheet called "PlantAnalysis". Column A has the "Item ID" and I would like to put the description in column B; starting at cell B3. I am trying to lookup the Item Description from an external workbook called "Item master" on worksheet labelled "List". Column A has the "Item ID" and Column C has the "Description".Is VLookup the recommended way to do this? Below is what I have but can't get it to work. Sub getdesc() 'Inserting vlookup statement to populate material description ...

HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical)
HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical) ---------------------------------------------------------------------------------------------------------------------------------------------- Microsoft Business Solutions (Microsoft Small Business Financials) Need one resource each for Functional and Technical roles. Location : Chicago, IL Duration : 12+ Months Funtional : This person will be responsible for the requirements gathering and setting up the system. Technical : This person will be responsible for installing and maintaining th...

Re: Calling Custom POS button
Thx for the fast replies. That fixed the prob, i just put in "VbTest.Class1" for the command and it worked fine. ...

variable line height and multiple reports
I have a shipping report that needs 7 lines vertically. The lines must fill the entire detail section about 5.5 inches. I have been able to use Me.Line (7.125 * 1440, 0)-Step(0, Me.Height * 1440) in the print event, but that leaves me two problems. 1st, how do I grow that last line to fill the detail section? 2nd, this report feeds from a form that allows my user to choose multiple shipping customers tickets to print at once, but with the above code, my report wants to print all items on only the first customer's ticket. We have been using preprinted report forms, I'm trying to do...

Excel 2003
Hi! I often work with spreadsheets of 4-10 columns and as many as 400 rows. Lately (maybe since I loaded 2003, I can't remember) whenever I try t copy or cut large numbers of cells I get the message: "The picture is too large and will be truncated." I used to cut and copy large numbers of cells in Excel for XP all th time...can anyone help? thanks! Greg -- Message posted from http://www.ExcelForum.com I'm the same guy who posted the question... ...but here's the thing I've since figured out: Even though I get that message, Excel still copies all my data to...

Exporting Data?
How is it possible to export data e. g. from a table in a new created worksheet (*.xls) or a layout (*.xlt)? Hi you may provide some more details what you're trying to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Drunken Alcoholic@talkto.guptaworldwide.com" <spiderIRS@web.de> schrieb im Newsbeitrag news:ciu5i3$oro$01$1@news.t-online.com... > How is it possible to export data e. g. from a table in a new created > worksheet (*.xls) or a layout (*.xlt)? > > ...

create new document, then add data
I added items to my toolbar and menu bars to my app and would now like to do the following: I know how to set up methods to handle the commands. I would like one of my buttons/menu items to create a new document and then add data to it (MDI). Either randomly generated or pulled from a server. What keeps confusing me about the Doc/View architecture and MFC is where pointers to different class instances are, etc. In this case, I need to create a new document first. If I create a method to deal with the command from my new button in the "MyApp" class, that works, but now how d...