Sorting In Spreadsheet Where Lines Are Sequenced By Finished Good And Component Parts

Jul 29, 2013

I have a very large inventory spreadsheet (30,000+ lines) where finished goods are listed on one line and the raw materials (parts) that make up that finished good are listed below. After the last raw material for a finished good is listed, the next finished good is on the next line (and so on). First, I need to sort the lines to eliminate certain common raw materials. Next I need to take the lowest raw material inventory level and have that as the default inventory level for the finished good.

View 2 Replies


ADVERTISEMENT

Finding Component Parts Of A Sum

Apr 2, 2007

I often come across a situation where I know a given total, and I know it comes from numbers within a given list, but I do not know which particular numbers they are. The onyl way to find out is to add every single combination of all the numbers in the list. I want to know if there is any formula within Excel which would help me to do that, given that the numbers of combinations are:

Number of items in list Number of combinations
1 1
2 3
3 7
4 15
5 31
6 63
7 127
8 255
9 511
10 1023
Obviously, I would like to be able to do this for any number of items.

View 4 Replies View Related

Spreadsheet That I Have Variable Data In And I Want To Transfer Specific Parts Of The Workbook Into A Word Doc

Nov 25, 2008

I have a spreadsheet that I have variable data in and I want to transfer specific parts of the workbook into a Word doc.

Not a problem with the Excel part, I can select each and every cell that I want, copy them and then...

Recording a macro hasn't done the trick as the code I have only copies and doesn't paste. I know I can edit the code to open Word but the problem is that when in Word, the data needs to go to very specific places, some of it goes in the header, most goes in the main body, I can't see how this could be done as there are no cell refs as in Excel!

is it possible to paste to say halfway in a sentence, in a macro?

View 9 Replies View Related

Sorting Multiple Lines Of Data

Nov 20, 2007

I have a large spreadsheet I need to sort into alphabetical manager order.

As there are between 2 and 20 rows per manager I would like to know if I am able to sort this into alphabetical order!

View 9 Replies View Related

SORTING - KEEPING TEXT TOGETHER ON SEPARATE LINES

Aug 3, 2007

I have 5 columns. The first 4 columns only contain 1 line each, the last column can contain anywhere from 1-6 lines. How can I do a sort and keep all the text in the last column together? By way, it was imputed on separate lines.

View 9 Replies View Related

Selecting Non Sequenced Cells In A Range

Mar 1, 2010

I'm trying to use the SUM function with non sequenced cell but the Range or any other function won't allow me to activate the selecting cells if there are more then 41 cell, I wrote somthing like so:

Sub Test()
Dim str AS String
Dim R AS Range

str = "A1,A3 ...... ,A57" 'this is only an example, assum that there are _
more then 41 cells
Set R = Range(str) 'Here is the Problem, I get a Runtime error 1004 _
Application-defined or object-defined error
R.select
End Sub

But if the str input was a 41 or less Cells address string then it works

View 9 Replies View Related

Remove Lines From Spreadsheet That Have The Value 0

Oct 27, 2009

I have enclosed 3 pictures with my question.

1. Is where the user is putting in data.
2. Is a spreadsheet with the data choosen from the action of the user, but it is containing also the non-choosen data from the input spreadsheet.
3. IS how I want the data shown to me either automatic or thru a macro. So is there any code like IF or some Macro who would make this happen?

What I want at the end of the day is a list of products that the user have put in and nothing else, with the QTY and price and the sum of that.

View 2 Replies View Related

VBA Code To Add Lines In Spreadsheet?

Jan 5, 2012

Have created a userform for people to use to populate a spreadsheet and based upon the values filled in cells, and date segregators, formulae determine whether the line of data is classified as new / increased or reduced.

Obviously I can sort this in excel quite easily by this criteria.

At the moment it is about 500 rows deep,and is sorted by N/I/R.....

What I ideally want to be able to do is create 3 new worksheets, N/I/R and then select all the N's and bung them in sheet N, row 2, and then repeat selecting all I and then all R, and pasting in respective sheets.........

Obviously it can be done, but I am far too rusty to do this.

At the moment, all is sorted in one worksheet, alpha ascending...

View 2 Replies View Related

Print Parts In Portrait Parts In Landscape

Dec 21, 2006

I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.

View 3 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

Spreadsheet Sorting..

Nov 14, 2008

I'm trying to sort this spread sheet so that each email address will have its own column assigned to it. Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D. The problem is I've around 1200 rows of infor and the email addresses are all over the place..

View 5 Replies View Related

Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet

Dec 27, 2011

I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.

If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.

View 4 Replies View Related

Sorting Spreadsheet By Priority Columns

Jun 14, 2014

I'm managing a World Cup Prediction League and have been trying to create a spreadsheet that will eventually feed in to a league, positioning each player in descending order. However, I would like to use three columns to determine how to position the players.

EG.

Priority 1 - Column D - Total Points (Rank from Highest to Lowest)
(if there are players with equal Total Points in column D, then)
Priority 2 - Column F - Correct Scores (Rank from Highest to Lowest
(if there are still players with equal value after Total Points and Correct Scores, then)
Priority 3 - Column F - Incorrect Scores (Lowest to Highest)

I've tried for hours to work this out but can't. It would save me hours of work each day

I have attached the document : WC Prediction League EXAMPLE.xlsx‎

View 14 Replies View Related

VBA - Sorting Data Into Spreadsheet Named Yyyy / Mm / Dd

Sep 5, 2012

Here is my VBA code so far, it sorts relevant email with data in it into spreadsheet titled "Mon 1st" "Mon 2nd" "Tue 1st" "Tue 2nd" ... and so on

But I've changed the name of the spreadsheet into "yyyy-mm-dd" format, amend the codes.

olMDate = olMail.SentOn
'Amend this line
wsDest = UCase(Left(WeekdayName(Weekday(olMDate) - 1), 3))
If TimeValue(olMDate) < #12:00:00 PM# Then
wsDest = wsDest + " 1st"

[Code].....

View 1 Replies View Related

Sorting- Spreadsheet That Shows The Items That Appears In Each Catalog

Nov 9, 2008

I have a spreadsheet (rows 1-15) where column A is a list of items in ascending order, and column B shows the catalog# that the item appears in. An items can appears in multiple catalogs.

I would like to develop a spreadsheet(rows 18-21) that shows the items that appears in each catalog.

The items should still be in ascending order.
The order of the catalog# row18 does not matter.
I have several hundred items and 30 catalogs....

View 12 Replies View Related

Strike Through Finished Items

Jan 12, 2010

Is there a way I can format a spreadsheet so that as soon as someone highlights a row, that row automatically gets a strikethrough?

View 9 Replies View Related

Search Parts Of Columns Looking For Matches In Parts Of Other Columns In A Row

Mar 1, 2014

I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...

Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith

In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.

In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.

VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.

View 3 Replies View Related

Whats A Good Way Of Learning VBA

Sep 1, 2006

I would like to build on my Excel VBA but not sure where to start. P.s I want to aim towards reporting side of things so make reports look good, copy data from sheets onto another sheet, formatting all done through vba, macros

View 8 Replies View Related

Counting Components In Each Finished Part

Feb 13, 2014

I receive a list of finished part #'s everyday at work. With this list I need to have excel figure how many components are in each finished part. In the table below, COM. 1 goes into Parts A,B and E one time each. COM. 2 goes into Parts B, C and D twice each. COM. 3 goes into each part once. I need a formula that will find the QTY needed for each component multiplied by the QTY to build.

Finished Part #
QTY to build
Component #
QTY needed

Part A
4
COM. 1

PART B
5
COM. 2

Part C
8
COM. 3

Part D
2

Part E
6

View 1 Replies View Related

To Return A Value- Whether It Is PLANNED, STARTED Or FINISHED

Nov 27, 2006

I have 4 columns: X, Y, Z, AA.

X - Start month e.g. 1, 2, 3, etc
Y - Start year e.g. 2006
Z - Finish month e.g. 1, 2, 3, etc
AA - Finish year e.g. 2006

In column AK, I need to return a value, whether it is PLANNED, STARTED or FINISHED.

PLANNED is if start date and completed date are both > than now()
STARTED is if started >now() but completed date is > now()
FINISHED is if completed date is < now()

Issues:

1. Start date is computed using Y as year, and X as month and the day is assumed to be 1. Same princilple for the finished date.

2. Column X, Y, Z, AA can be blank. If month (X and/or Z) is blank but year is not, then date is beginning of the year. If either start year is blank but finish year is not, then status should be based on the finish year and vice versa.

3. If both year (Y and Z) are blank, then status should be "NOT SPECIFIED".

View 9 Replies View Related

Find A Good Book On Formulas

Jan 29, 2009

if I could find a good book on formulas, I might be able to raise the bar on my questions.

View 3 Replies View Related

Formulas To Occupy All Rows. Good Or Bad?

Jul 2, 2008

Cell A1 can have only 0 or 1. Cell B1 can have any numeric value. Cell C1 takes the value of B1 if A1 is 1, otherwise it is 0. So, C1's formula can be either "=A1*B1" or "=If(A1=0,0,B1)". Lets say this formula is continued down to the 65000th row. My question is which formula is faster? The first one or the second one?

View 2 Replies View Related

Transferring(Transposing) Data To A Finished Sheet

May 25, 2006

I am trying to find an automatic way to create a finished report format that draws information from a different sheet within the same workbook. The trouble I'm running into is:

The raw data consists of some lines without any data, and some lines with data. The purpose of the final report is to display only those rows with data in them.

I have a sample spreadsheet attached, with the first sheet containing raw data, and the second containing a rough Idea of what I'd like the final infomation to look like. Somehow this seems like it should be really simple, maybe doable with an if statement, but I can't figure out how to get it to just ignore the empty rows without skipping a row in the final doc.

View 2 Replies View Related

Visibility Of Cell A1 On All Worksheets When Macro Finished

May 8, 2007

How can I loop through all worksheets in a workbook and, not only select cell A1 in each worksheet at the end of a macro, but have the visibility of cell A1, no matter where you last clicked on the worksheets, such as a remote cell like BK244.

I would like the user to see the first row and further most left cell (cell A1) when they click any sheet in the workbook, because my macro causes several rows of data to be written, leaving the viewer to see something like row 244, instead of the top row which is where the titles are.

View 5 Replies View Related

Macro To Go To A Hidden Worksheet. Good/Bad Idea?

Feb 22, 2010

I have an excel document displaying graphs that i would like to link to from a menu page (via a number of buttons). The problem is that i have about 12 of these graph sheets (along with 8 other sheets) and i'd rather not have them cluttering up the worksheet tabs list at the bottom of the screen.

Does anyone have any suggestions about how to handle this? Is a macro to open a hidden sheet and then close it once it is navigated away from a reasonable option? Or does Excel protocol suggest that they should remain visible, despite their cluttering..

View 6 Replies View Related

Good Way To Reset Variable Each Loop To Empty?

Apr 15, 2012

I'm using a variable to loop down a column of data. Each time it loops the variable may or may not perform an operation - it will if there is something in the cell but won't if the cell is empty. It writes the answer of the operation to an ajoining cell, before looping again.

The issue is that if the row cell is empty, what's written to the ajoining cell is the pre-existing value of the variable, because its value hasn't changed.

how best to 'empty' the variable each loop to ensure either that the correct value or nothing at all is written to the ajoining cell?

View 5 Replies View Related

Out Of Memory Error After Previous VBA Code Successfully Finished

Mar 8, 2014

I experience "out of memory" error after previous vba code is successfully finished. So quick "solution" is to restart Excel.

My current PC configuration:
HP Compaq dx7300 Microtower
Intel(R) Core(TM)2 CPU
6320 @ 1.86GHz
1.58 GHz, 1,99 GB of RAM

View 3 Replies View Related

Aggregate Number Of Good And Bad Results Per User On Different Sheet

Jul 4, 2014

I have a database file where users name can be located in 2 neighbor columns. Against each user name (in next 2 cells on the right) there is a evaluation (good or bad).That database will be growing and new additions will be added each month. That means one user can appear several times on a different row with different evaluation.

In the same file I have a separate sheet where I need after running a macros to have aggregated list of users (in one column) and against each user I would like to see how many how many good and bad score he have. It will be good if users are sorted in alphabetical order.

I am providing an attachment with example file.

View 2 Replies View Related

Good, Average, Fair, Poor: Automatically Use One Of The Numbers That I Have Set And Add It To Total

Jul 20, 2006

In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)

Here's what I need:

Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.

View 4 Replies View Related

Active X Component Error 429

Nov 29, 2009

I keep getting this error message: Error 429: ActiveX Component Cannot Create Object

I fond a site that explained the problem: "If Microsoft Excel 97 is installed after an application created with Microsoft Visual Basic 5.0 that uses DAO is installed, Excel does not add the DAO design-time licensing key. This licensing key is required by Excel, but is not required by applications created with Visual Basic."
But I can not figure out how to fix it. I changed the registry and added the licensing key, but I still got the error.

View 3 Replies View Related







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