Sort And Subtotal Part Data

Apr 10, 2006

I have data with two types of records: those with "orders" in teh comments field and those with something else in that field. I'm able to sort on comments so "orders" records appear at the bottom, and insert two blank rows, separating the two datasets. Now I have to be able to set the range of the top portion. I worked on the problem, and used some of the code in the response to my post. I need to store two cell addresses.

To determine the range, I find the first blank column, then move one cell to the left, then I need to store that column's letter. AFter I find the first row with "orders" in the comments, I need to insert two blank rows and move back up to the row above that containing data (this much I've done), then store that cell's row's address. I need to use the column and row I record as the range to select for the sort. So, I basically need to things:

1. How to store the active cell's column and row
2. How to stop recording, type in code, then resume recording.

View 8 Replies


ADVERTISEMENT

Sort By Subtotal, Make The Subtotal Stand Out

Feb 5, 2007

way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.

What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?

View 12 Replies View Related

Sort By Part Of Text

Aug 4, 2006

how can i sort a text column by the last words after the "-" automatically. For example, this is a text column:

1. 12634 - Sweetare Dr. - Garden Grove
2. 15234 - Hotflame Dr. - Irvine
3. 16612 - Arigato - Japan
4. 22634 - Saigon - Garden Grove

I need to sort the column by the word(s) after the '-' sign.

View 5 Replies View Related

Script To Sort Part Numbers

Nov 2, 2011

I'm trying to run a script from a CAD software which exports property values into an Excel spreadsheet. I need to then sort the spreadsheet by part numbers (which are located in column G) but my script is not working correctly. My data range is A1:G50.

Const xlCenter = -4108
Const xlAscending = 1
Const xlYes = 1
Const xlSortOnValues = 0

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

View 1 Replies View Related

Sort Into Regular Part Number Order

May 20, 2009

i have a list of part numbers that all have prefixes and i need to sort them into regular part number order and can't seem to figure out why i'm having such a hard time. the order i need them is this example. i need after part number 1-1 to be 1-2 followed by 1-3 etc. not 1-10 followed by 1-100. i hope i'm explaining this correctly.

View 9 Replies View Related

Subtotal Formula - To Show Up In The Subtotal Value Column

Oct 23, 2008

I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....

View 10 Replies View Related

VBA Code To Sort Data - Run-time Error When No Data To Sort

Mar 4, 2010

I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.

Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer

zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

View 9 Replies View Related

Subtotal Data By Day

Oct 26, 2006

I have a large spreadsheet with production numbers on it. The sheet shows the day and time that each run starts and the day and time that each run ends. It also shows the usage of a raw material that each run consumes. What i need to do is figure out how the usage per day not usage per run. Until now i have been doing it manually and with all the data i have, it has taken 4-5 hours each time we need to update our usage. If it makes it any easier, the hours can be rounded - so long as the usage stays the same. (ie. if a run starts 5 min before midnight, we can assume it starts at midnight as long as the usage is not reduced fro the run). I have attached a sample of the data so you can see what i am working with.

View 9 Replies View Related

Subtotal Groups Of Data

Feb 25, 2007

I have attached sample of a series of massive spreadsheets that I am working on.

Item Numbers in Column A
Flag in Column D
Values in Column P

I want put the total of the values in P for each group of item numbers in column C where the flag is YES.

Unfortuantely I prefer not to use excel's subtotal functionality or array formulaes here because of the logistics of how these are used.

View 9 Replies View Related

Subtotal Of Data Table

Dec 27, 2007

My data sheet is attached for reference. The yellow cells show the end result I'm aiming for.

After each change in "NAME", I have inserted 2 blank rows. In the first blank row, column A, I want the text "ENDTRNS". In the second blank row:

column A - "TRNS"
column B - "Bill"
column C - reference the cell below
column D - "Accounts Payable"
column E - reference the cell below
column F - sum the next cell down to the last cell before the next blank row (sum range size will vary)
column G - reference the cell below
column H - reference the cell to the left
column I - "Y"

Then search out the next set of 2 blank rows and repeat, all the way down to the last set of blank rows.

View 5 Replies View Related

Take A List Of Data And Then Subtotal The Value By Certain Groups

Dec 4, 2009

I want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals

So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)

View 4 Replies View Related

Copy Data From One Part# On Sheet 2 To The Same Part# On Sheet 1

Feb 10, 2009

What I am trying to do is automatically move the date received and the total qty received from the Rcvg. Log sheet to the RFMs Release sheet.

The problem is:

1) I don't know what I'm doing

2) On the Rcvg. Log several part numbers that are the same with different Qtys.

3) on the Rcvg. Log the are several part numbers that are the same using different programs.

What I would like to do is combine all the same part numbers with the same program add the total Qty received and paste the date received and total Qty into the RFMs Release sheet in the proper part numbers row.

Is this possible?

Attached is a shortened copy of the spreadsheet normally there about 3000 entries.

View 9 Replies View Related

Outline And Group And Subtotal Excel Data Automatically

May 19, 2014

Is there any way to outline excel data automatically

In the attached excel sheet : test outline.xls

I need automatically Because my data very large ...

View 2 Replies View Related

Subtotal With Condition (only Count Values Greater Than Zero When Data Is Filtered)

Oct 24, 2007

=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.

I have attached a worksheet with the example.

In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.
When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is $1050 in cell C99.

Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.

View 2 Replies View Related

How To Subtotal Data In Filtered Data

Nov 13, 2008

how to subtotal data in filtered data. Here's my issue, I have to filter a huge range of data based on account type, then within that account type, I need to subtotal data for a select number of departments where the departments I need to subtotal all start with the letter d. I can't figure out how to create this type of subtotal on filtered data.

View 9 Replies View Related

Multiple Lookup: List Of Part Numbers Based On The Product Part Code

Feb 12, 2009

I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

View 3 Replies View Related

SUMProduct (182 Matches Part 1 Of Which 32 Match In Part 2)

Nov 18, 2008

Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).

=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))

View 3 Replies View Related

Remove First Part Of String Of Text And Keep The Second Part?

Feb 12, 2014

Is there any way to remove the first part of a string of text in a cell and save the second part?

The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.

The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?

View 5 Replies View Related

Extracting Part Of Imported Data

Mar 28, 2007

to extract a number from an imported cell containing text, a number and date. i need to use this number as part of a formula in another cell and the data needs to be refreshed every hour.

eg. the cell imported is "USD 30.97 (March 27, 2007)" and i need to use 30.97 in another cell.

i've tried using data - text to columns, but i realised that after splitting the cells, the 2nd and 3rd columns can't be refreshed.

View 14 Replies View Related

Find Part Of Data In Cell In Particular

Dec 12, 2006

i can use the VLOOKUP function to find entire cell in one table and paste the required data from the row....

Is there a function to search part of data and paste the required data

View 9 Replies View Related

Extracting Part Of Cell Data

Sep 8, 2008

I need to extract part of the data from a number of cells. I was thinking a vlookup would be the right way to go about this but i don't know how i can make it look for only part of the data in a cell.

example data Required Extraction Data
PXC-WIS-HAR075/001 = I need the data HAR075/001
GXR-Cru-WOO046/001 = I need the data WOO046/001
LST-Wis-KAV001/001 = I need the data KAV001/001
GXR-CAP-EDL001/001 = I need the data EDL001/001
RXW-ACT-WAL081/001 = I need the data WAL081/001
SXS/mf/SMI085/001 = I need the data SMI085/001
SYM-CEN-HAP001/001 = I need the data HAP001/001
SYM-Act-MAY025/001 = I need the data MAY025/001

I should have also wrote that I do have a list of the file numbers for the vlookup to.

View 9 Replies View Related

Extract Web Data From Part Of Page

Dec 31, 2008

I would like to extract some data from a web page, but only part of it. The website is:

[url]

and I have a spreadsheet with many different ProjectId hyperlinks in a column (the final six-digit number). All I want is the large block of text that is in the middle of the page. Sometimes there is no text, as in:

[url]

So I don't know how to deal with that either...

I would just like to paste the text into a cell adjacent to the hyperlink. I know that there is a visibility issue with 1024 characters, but can cope with that (no text will be longer than the 32k+ chars).

Any help would be appreciated - it's for my wife's PhD, and I just want to make it easier on her. I am reasonably ok with macros, but am by no means expert! If you need clarification,

View 9 Replies View Related

Extracting Part Of Data From Cell

Apr 24, 2009

Extracting part of data from cell .....

View 9 Replies View Related

Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet

Dec 10, 2008

I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

View 3 Replies View Related

Run Part Of Macro, Pause, Run 2nd Part

Aug 4, 2007

I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop.

Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...

View 6 Replies View Related

Pulling Data From Files By Part Of File Name

Oct 4, 2008

I have forms by differnet departments each day. The files are saved as the department's name then date Byrd 82708.xsl. I need a command button that will pull data from three cells in each of these forms. The master list will not be in the same folder as the deparment forms, the cells are E20, f20, f25. I have a text box were the user will input the date, by this date I would like all forms with this date in that folder to have their data pulled from those three cells and returned in master list.

View 5 Replies View Related

Moving Data And Deleting Rows, Part 2

Jan 7, 2010

This is similar to a previous post, in July, which was masterfully solved. I have tried to adapt the previous script, without success. Therefore,

I have copied data from a screen and entered it into Excel 2007, which is attached as 'snohomish sample.xlsx The data starts out (sheet 1) in 8 columns (A-H), and only in the odd rows (1, 3, 5, ...) What I wish to do is the following:

Move 'sheet 1 column B' to 'sheet 2 column A'. This is a date which needs to be in mm/dd/yyyy form.

Move the last 14 digits in 'sheet 1 column F' to 'sheet 2 column B'. This needs to be text so that I don't loose any of the zeros.

Move 'sheet 1 column E' to 'sheet 2 column C'.

Leave 'sheet 2 column D' blank as I will be entering a currency amount after running the module.

Move 'sheet 1 column A' to 'sheet 2 column E'. This also needs to be text as I can't loose any zeros or have it in scientific format.

The rest of the data on sheet 1 is not needed on sheet 2.

I will be entering data in 'sheet 2 columns F-K' after running the module.

I am attaching 'snohomish results.xlsx' which shows what I hope the results to look like (note the sample results are on sheet 1 of a different file).

View 6 Replies View Related

Sum Part Of Named Range And Pivot Data

Mar 14, 2012

I have a named range TOT_BUDGET. We use the current month number to access this range's index for current budgets.

=INDEX(TOT_BUDGET,3)

My quandary is that I need to calculate the YTD budget for one of our dashboards. So, I need to get months 1, 2, and 3 and SUM them. Next month I'll need to get 4 months, etc...

I've looked at SUMIF, but it needs to match against some sort of criteria... I don't have any numeric month labels on the budgets page. I cannot figure out what range/array I would match the month number to.

Now, I know the easy way would be to put a month number at the head of each column, reference that array and do the SUMIF. Blech.

Ideally, I would love to just be able to say =SUM(INDEX(TOT_BUDGET, 1-3)). Is there an Excel function that will let me select an array slice?

------------------------------

In addition, we are getting the Total Sales number out of Great Plains via an SQL query. We summarize many accounts using a pivot table. We then access this table by using the GETPIVOTDATA function.

GETPIVOTDATA("NETCHANGE",TSALES,"FISCALYEAR",CurrYear,"FISCALPERIOD",CurrMo)

Again, how can I do a total YTD?

Is there a way I can do something similar to a foreach loop in the cell formula? I'd like to avoid macroland with this if possible.

View 1 Replies View Related

Data Transfer (populate The Green Field Individual Part # )

Oct 20, 2008

This sheet is comprised of 3 different section. The purple section is an input field. Quantities are added based on product type and date. The blue section is a breakdown of all the individual parts that go into the specific product. The green section is a breakdown of all the possible parts in the entire product range.

What I would like is to populate the green field individual part # with the appropriate sum from all the different product types based on quantities desired. I'm sure this is a confusing description but it will make more sense once you check out the sheet.

View 2 Replies View Related

Sort Data And Auto Copy Sorted Data To New Worksheet While Maintaining WS1

Oct 7, 2013

I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?

View 3 Replies View Related







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