Sort Unknown Number Of Rows

Sep 30, 2009

I am working with 2 workbooks the first one "LH Enrollment" is used to enter information for new students being enrolled in the program. After the information is entered the macro is then copying the information to the second workbook "LH Children Records". The part I'm stuck on is that I now need to sort alphabetically by last name which is entered on Sheet 1, Column B of "LH Children Records". The number of columns will stay the same but the number of rows will change each time a new child is added. Eventually all this information gets copied over to several other workbooks, but I'm stuck trying to sort. I have attached both workbooks

View 5 Replies


ADVERTISEMENT

SUMPRODUCT Use But With Unknown Number Of Rows?

Sep 15, 2012

I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.

I know it is not trivial, but sure it is possible.

57
file:
HIRES-~1
#VALUE!

58
208
1
1

[Code] ...........

View 9 Replies View Related

Combining Worksheets With Unknown Number Of Rows

Dec 16, 2009

I am trying to find a way to combine two worksheets with identical columns and an unknown number of rows. Both sheets use columns A through K.

I want to add a new sheet called "Combined orders", then copy data and headers from Sheet1, paste it to Combined orders, copy data only from Sheet2, and paste it on the row after the last row of data from Sheet1.

Sounds easy but I am easily confused by the unknown number of rows in each data set. Can someone please help?

Also, are there a few lines of code that I can copy and paste into new macros that will make the unknown rows problem easier for me in the future? (i.e., instead of just showing me the code, can you also please explain the concept behind it so I can learn for future reference?)

View 7 Replies View Related

Total A Column With Unknown Number Of Rows

Jan 24, 2007

In the code below, a formula is placed in column F to compute the total of that column. I first find the number of rows and place the formula in the cell below it. I'm dividing the sum by 2 since there are subtotals in the column.

Sub AddColumn()
Dim NumRows As Long
NumRows = Range("A65536").End(xlUp).Row 'get the row count
NumRows = NumRows + 1
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:F308) / 2"
End Sub

The problem with the code is that I don't really know that the last row in the column is F308. I need to replace that part with a variable. It will be something like this (which I know is incorrect):

Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:NumRows) / 2"

How would I use a variable in the SUM function in place of the cell names? Should I even be placing the formula in a cell, or should I compute the sum in the macro and place the value in the cell?

View 2 Replies View Related

Fill Conditional Cell Values Based On Unknown Number Of Rows

Dec 15, 2009

I have a set of data in column R, with an unknown number of rows that looks like this

Days Late
-28
150
3
16
41
.
.
.

and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:

View 4 Replies View Related

Find Text String And Report Back Data In Unknown Number Of Rows

Apr 26, 2007

For example

Name Address Phone # zip
Danielle 4561
Danielle 9852
Danielle 22
Danielle 69
Joe 895
Joe 28
John 9821
John 1114
John 698

Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?

View 11 Replies View Related

VBA - Copying And Pasting Unknown Number Of Values Each Different Number Of Times

May 28, 2014

I am trying to come up with a macro that selects values from one sheet and inserts them into another sheet. The number of values will change each time based on the user's entry, as well as the number of times that each entry should be pasted.

For example:

Entry: X | Y | Z
Number of Times to be Inserted to New Sheet 3 | 2 | 1

Result:
X X X Y Y Z

I have spent a while trying to figure it out, however the best I can come up with is using an array, but I can only get one value from the array to paste multiple times:

(*Note: In my testing, I didn't insert into new sheet or set up the array to handle different values, I was just trying to get the basic idea to work)

Dim A(1, 3) As Variant
A(1, 1) = Range("C3").Value
A(1, 2) = Range("D3").Value
A(1, 3) = Range("E3").Value

[Code].....

View 3 Replies View Related

Sum Across Unknown Number Of Worksheets

Sep 20, 2007

The colours are just to mark the ranges

As every month is different the number of sheets adding up to the Red sheets(week total)
will change and the same with the Blue sheet(month end total)

Is there a code I can run for this summing up to be done?

Yellow is where data is entered

Red is where the Yellows range sheets need to add up before it

The Blue is where all the Red range sheets need to add up

View 9 Replies View Related

Summing Across Unknown Range In Unknown Cell

Oct 26, 2009

I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!

I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.

Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.

Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?

Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).

Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!

View 14 Replies View Related

Combining An Unknown Number Of Cells Into One

Nov 4, 2009

I have the following setup,

Down column A I have a list of jobs, job 1 , job 2 etc
Down column b I have either a blank cell or other data (will be numbers but is irrelevant)
What I want is a formula for a cell that tells me which titles in column A have any data at all in column b. I need it to look like the following : Job 1, Job 2, Job 4
assuming b1 b2 and b4 have any contents and b3 and b5 and onwards are empty

I am seeing that concentate gives me close to what I want but the number of rows in column a and b change constantly and also may be above 30

View 9 Replies View Related

Accounting For Unknown Number Of Worksheets

Jun 28, 2006

I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?

Windows("0285 WORKING FILE 0406.XLS").Activate
Sheets(3).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R501C1"
ActiveSheet.Paste
Sheets(2).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R1001C1"
ActiveSheet.Paste

Sometimes I will receive this file and there will be additional tabs. I just never know how many.

I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.

I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.

For each new worksheet selected add 500 rows to the last pasted amount."

View 7 Replies View Related

Average Unknown Number Of Columns

May 25, 2008

I am trying to average a range which continually changes depending on the amount of registries entered in a given week. for example, one week there may be 5 registries which would fill five columns while another week there may be 15 registries, once again filling 15 columns. I would like to find the average of x amount of columns.
My initial approach was to select the first blank cell to the right of the data and then find the last filled cell (which should be the first column of data.
with this range i tried hopelessly to use the average function (did not work):

Sub averagemake()
Dim iLastColumn As Integer
Dim Rng As Range
Sheets("All Data(Values)").Select
Range("A6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
iLastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-iLastColumn]:RC[-1])"
End Sub

View 5 Replies View Related

Sum To Unknown Rows With Conditions

Apr 24, 2006

Need to sum through an unknown number of row generated by advanced filter at run-time

Formula at C16
Data starts at row 57 (from copy/paste - advance filter)
Last row unknown

Criteria to match in A16
Data to be evaluated in B57:B (row unknown)

Found this formula on microsoft.public.excel.misc
=SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
Which I modified formy start row

View 9 Replies View Related

VBA Join Function For Unknown Number Of Arguments

Feb 13, 2012

have a function that takes an unknown number of ranges (worksheet cells) as arguments that can then be joined with the provided delimiter.

It should ignore null values.

I saw that someone posted the following line of code:
Replace(WorksheetFunction.Trim(Join(myArray)), " ", ",")

So I guess my real question is how to pass an unknown number of arguments into a function and put the values in an array.

View 3 Replies View Related

VBA Copying Unknown Amount Of Rows

Jul 15, 2009

I need to know the function to copy an unknown amount of rows. After a certain row it should copy everything.

So like after row A6 it copys every row below it with data in them then I want to paste it in another worksheet. I dont want to just do

Rows("6:65000").Select
Selection.Copy

If someone could point me to a good site that explains alot of useful VBA commands that would help me alot. Most of my problems come from just not knowing the full function names.

View 10 Replies View Related

Add Data From Unknown Number Of Sheets To Summary Sheet

Aug 1, 2008

How to I add data to an existing summary sheet, in a specific cells, from an unknown number of sheets?

Tickets will be generated with unknown names.

I want the summary sheet to be able pull the ticket numbers into the top row of the summary sheet, to an undefined number of sheets.

As well I would like the summary sheet to pull the job# on each ticket and the hours for each person on each ticket.

View 5 Replies View Related

Range Of Cells Of Known Columns But Unknown Rows

Mar 19, 2008

I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.

What I've managed to find so far on the forums is

Offset("$A$3", 0, 0, (Match(9.99999999999999E+307, "N:N", 1) - 1), 14).Select

which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).

View 6 Replies View Related

Macro: Create Named Ranges. Unknown Columns/Rows

Sep 9, 2006

I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.

I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.

modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)

For i = 1 To modelcount
Redim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i

This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.

View 2 Replies View Related

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View 3 Replies View Related

To Add Bank Account Number And Sort Code Number In A Box

Nov 23, 2009

is it possible to have boxes for numbers to be inserted into

i want the user to be able to fill in bank account and sort codes numers into the relevant individual box for each number

View 9 Replies View Related

Sort Multiple Rows Without Affecting Others Rows

Aug 10, 2006

I want to sort each row in an excel sheet so all calls are in alphabetical order without affecting any other row. I need to do this for about 500 rows. Is there any quick way of doing this without having to sort each row individually.

View 3 Replies View Related

Sort Number In 1 Cell

Aug 8, 2007

I am trying to figure out how to format a cell to arrange it's contents in numerical order. For example one cell contains 5 area codes formatted like : 75020, 75062, 74865 etc etc. Is there a formula to place those in numeric order.

View 5 Replies View Related

Sort Without Using The Top 2 Rows

Dec 16, 2009

I have a sheet that has some hidden calculations in the top 2 rows. My actual headers start in row 3 (the amount of data always changes so I chose to put the hidden constants at the top rather than the bottom). I want to sort by the headers in row 3 but I can't figure out how.

View 2 Replies View Related

Sort The Rows Of Data

Apr 10, 2009

We have an issue with sorting rows of data that have formulas in them.

---------------
These formulas are using data from a row which is above the column headers.

For example cell H2 has 160 manually entered in it. It's the number of work hours available in January 2009.

The column header for that column is in Q4 and has a value of 'Jan'

Below this in cell H5 is the formula '=+H2' because the resource in this row is available for 100% of the month.

Another resource in row 22 has the formula '=+H2*0.15' in cell H22 because they are only available 15% of the time that month.

---------------
The problem occurs when we try to sort this data. Most of the cells displays #REF!. Some show a value, but the calculation is wrong.

View 8 Replies View Related

Macro To Sort 100 000 Rows

Sep 21, 2008

I've got up to 150000 rows in excel (2007) and i need to sort the data into 2 sheets.
Sheet1 contains all the data. Sample:

1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409
2000999999027020300189640900000000000005740200070063

As you can see, the first row starts with 1000 and second with 2000. That would be the criteria. All other rows goes exactly the same.

I need macro that copies all the rows starting with 1000 onto sheet2 and all the rows starting with 2000 onto sheet3.

Filtering and copy/paste won't help, because the list is toooooo big. Thatswhy only hope is to find macro that loops threw the list.

View 14 Replies View Related

Sort Rows According To A Variable

Dec 7, 2008

I have the following code which sorts rows accoridng to a variable retrieved from a work sheet. When I plug in "SPY" (name of one of the variables in column B), it works fine, but with variable stock_symbol, I'm given an error for some reason.

View 8 Replies View Related

Keeping Rows Together For Sort?

Sep 20, 2012

I have dumped main frame data into excel. It is customer info that is in three rows of data each. I need to know if its possible to group or somehow keep three rows of data together for multiple sorts. The first row is the customer name, the second row is their first line of address, the third row is the city, state and zip. I need to add columns to this dumped data, but I will need to do a number of sorts for different views of the data. But as I've stated every 3 rows go together. I've tried different ways to group (for lack of a better word) the three rows, but nothing I've tried works. I've also done multiple searches, but either I'm not searching on the right word or this can't be done.

View 9 Replies View Related

Sort Rows Into Another Sheet?

Jan 4, 2013

I am trying to 'sort' rows of data into another sheet in the same excel file. For instance, if i have a row that contains the string "SDMH" then it should be sorted to "Sheet2". In the original sheet, the data does not need to be removed, just copied.

View 4 Replies View Related

Trying To Sort Groups Of Rows

Dec 31, 2007

Range("C"&((h-1)*6+2)"G"&((h-1)*6+7)).Select

What is the proper name convention if I want to sort a group of 6 rows at a time. For example if h =1 then I want to select from C2:G7 to sort but I am getting an error when I try to input it as above. I have the sorting code, just need it selected first.

Sorry, I don't know how to turn off smiley faces because the options list isn't available to me, but before the G is a : and a (

Trying to make it Range("C2:G7").Select

View 9 Replies View Related

Sort Rows Without Sorting

May 18, 2009

I have data being fed from an application where it's difficult to sort the rows into ascending order. So is there a way that VBA can sort them and produce a value according to the order?

For example, Column A contains 5 rows.
Row 1's value is 4
Row 2's value is 1
Row 3's value is 2
Row 4's value is 3
Row 5's value is 5

If I were to sort these into ascending order, I'd get 1, 2, 3, 4, 5 in Column A. But I can't sort the rows so I need code to tell me which is the lowest number (1), which is the second lowest number (2), etc.

The purpose of the code will be to put values in Column B according to what's found in Column A. For the the lowest number, 1, the value in the cell next to it in Column B would be "a". For number 2, it would be "b", etc.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved