My table is as follows:.....
If the columns A, B, and C were constant and never changed and only column H changes from job to job, how do I: SUM the TOTAL number of pieces (pcs per X qty) if the value in column B is "A"? I need to do this without creating a seperate mutliplication column for the sum of the pieces. It all needs to be done without adding any new columns.
I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.
View 9 Replies View Relatedi would like to be able to use a macro that will delete all columns in the sheet that do not contain one of 5 pieces of text in cells 1 to 20.
It is driving me mad, i have managed to find macros that delete empty columns, but they do not remove the columns that do not contain any of the 5 words. often the cells are empty down to row 5, when the data starts
I am trying to do is, to sum up the values in an array, given that the cell value is not an error.
If the cells were in order, the following array formula would solve it easily:
{SUM(IF(ISERROR(A1:A3),0,A1:A3))}
But my problem is that, my cells are not in order. To be more specific, I want to look at A1, B12 and C13, and sum them up with an array formula given the condition that cell value is not an error. Of course, in my case, I have too many cells.
I have two spreadsheets. I need to be able to match information from detailed spreadsheet for specific information from a lookup spreadsheet.
The detailed sheet (call it Purchases) has information about what was purchased during a month.
Columns: ...
I'm trying to come up with a single formula to create a single column list from a table with blanks.
a
b
c
d
e
f
g
To
a
b
c
d
e
f
g
I know I've done this before but having trouble visualizing today.
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
Attached file where i m not able to split data in seperate seperate coloumn
View 3 Replies View RelatedMy goal is to macro the creation of pivot tables into seperate worksheets based on each particular record in the "Office" (location). Ultimately, I will apply an email macro to send out each pivot table (and source data if necessary) to various recipients. I need to make sure that each recipient of a pivot table is limited to only drill into the detail for their particular "Office" and not able to view other location's information in the Source Data worksheet--is this possible, or do I need to create separate source data worksheets for each Office's pivot table in order to limit the viewing?
Items I need guidance on:
Creating a macro to breakout pivot tables into seperate worksheets based on "Office" locationPlease advise on how I can secure Pivot Tables so that the user can only drill into the information originally presented in the pivot table they receive and will not be allowed to view all of the source data.
Please find attached a file with my source data and an example of a pivot table for one of the Office locations (Chicago).
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View 9 Replies View RelatedI have data as follows in Column A:
Part Number: 0000000-1 ARTEC-GH-56S 12A
SPARES in Repair: 20
On-hand: 100Location: BNCD
I need the data under different columns as follows: I also want an extra column before Column A labelled as Common number.
A B C D
Part Number SPARES ON HAND LOC
0000000 20 100 BNCD
Is there a simple formula that can be used to seperate text in a column.
Example:
ABC - 123456
I want to return the text before the "-".
I have columns with titles at the top, and with numbers in the below cells.
What I would like is a formula for a column seperate to these that firstly, looks if there is a quantity in the relevant cell on that row, secondly takes the title (top cell) of that column, and thirdly puts the number in the cell on that row after the title:
A_____B_____ C_____ D_____ E______ F
1_____UK____ Ger___ M/E___ Rus_____ Nor
2_____80_____250___ 300___ 350_____200
3____ 60_____200____350____350____ 150
So in cell A1, I would like a formula that reads the subsequent cells and their titles, and gives a one-cell summary of the data seperated by commas. So in A1 it would say:
UK 80, Ger 250, M/E 300, Rus 350, Now 200
And in A2:
UK 60, Ger 200, M/E 350, Rus 350, Now 150
See attached spreadsheet. I need to have all the data which is held in 1 row in column A seperated into its own column automatically. The order i need the data to be in is as follows
Date(pink), Time(red), Racecourse(green), Race Type(black), Class Of Race(purple), Going(blue), Prize Money(yellow), Distance(grey), Number Of Runners(brown). the text in bracket is a key for the info for your ease of use.
I have a spreadsheet with two sheets, 'Sheet0' & 'Sheet1'. In 'Sheet0' I have a column named 'A_Sheet0' containing a list of numbers from Rows 2 - 50. In 'Sheet1' I have 3 columns, the first containing numbers 1 to 50, the second, 'B_Sheet1' containing a list of ascending numbers and the third is the column of interest. This column entitled 'Nearest Value' must read the corresponding value of Column 'B_Sheet1' then find the closest number to that from the numbers in 'Sheet0', column 'S_Sheet0'.
I have included a sample file with this thread, and in the 'Nearest Value' column I have manually input some of the nearest values for your information.
I am looking for a way to ease a 24,000 line duplicate list into showing just the files from a specific folder/subfolder and its dupes. I have marked the files that reside in that folder by adding a column and entering a "1" in it. What I am looking for is a way to add that "1" to the others of that unique numerical ID duplicates, so I can sort and trim the list to just those files. End result is to show files in one folder and the locations of duplicates of those files elsewhere for eventual cleanup.
Very loosely, if C2=C1 and B1=1, then make B2=1 (and so on)
(A is blank and only there in case a formula needs input there)
A B C
110121
10121
118134
18134
18134
124232
24232
I'm trying to extract pieces of a part number and place each section in another cell. It's kind of like a text to columns, but in a formula and removing part of the data. This is what I'm starting with: Cell 3A: HB149E.505
I'm trying to figure out a formula that will fill the following which are currently blank:
Cell 3A: HB149
Cell 3B: .505
Cell 3A will always have 5 digits prior to the decimal and 3 digits afterward so that is not a problem.
How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.
View 4 Replies View RelatedI have a homework assignment where I have a list of companies with the products they have with one other company. The original company and their products are listed on the same row, with a dollar value assigned to the product. So Cell A1 has company name, cell B1 has product a, and Cell c1 has the amount they pay for that product. Is there a function I can use to split that one row into 3 separate rows, to show the company name 3 times and the product and dollar amount as it's own row, next to the company?
View 1 Replies View RelatedI work in HR MI and am compiling an equality spreadsheet. Out of everyone who is on the list I want to see how many men and women we have which I have figured out using = SUMIF(GENDER,"MALE",NUMBER) but from that I want to say theres 50 male in total and out of that 30 are White and 20 are Indian. Is there anything I can add to this formula so it looks at more than two defined sets of information??
View 2 Replies View RelatedPart 1: Pricelist
The user presses 1 of 3 macro buttons to access the price list.
The buttons represent 1 of 3 parts of a construction project.
The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro,
depending on which button is pressed, hides all but the relevent quantity
col & total col.
Each job phase needs its own total & having it setup this way makes it
easier to enter new items into the table (only have to enter
the item once, rather than 3 times).
The first html shows a simplied example of the "price list" described above
(before & after the quantitiy & price cols are hidden).
The user adjust the quantity & the sheet calculates the price total.
Part 1: Material listThe material list is a list of items & quantities the user selected in the
quantity col of Price list. This list is to be printed & given to the workers.
It does not show price, only item & quantity.
This list, unlike pricelist, must be visiablly broken down by job phase.
I dont want the material list to be the price list repeated vertically
on a sheet 3 times. This would take up too much space & require new
items to be entered once in the pricelist & then 3 times in the mat list.
I would rather the mat list be on a seperate sheet from price list & have
a formula in cell matlistA1 that searches the phase 1 quantity col
(then phase2 & 3) for the first #>0.
When a #>0 is found, the formula
would return an item name into the cell on matlist. Then the formula
would run in matlistA2, & search for the next #>0.
(& so on until a complete mat list table, broken up into phases is created)
Sheet2
ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73
..............................
In column E of row 1 I have one column with a long line of text containing information in that should be separated into cells for the following fields:
Year, make, model, drive, part, name, phone, text, email, location, notes, stream, date/time, disclaimer
Here is an example of the text in column E:
Year: 2001 Make: Nasa Model: big spaceship Drive:
Automatic 2 Wheel Drive Spaceship:
4 12.7 Liter ----------------------------- Part: Hood -----------------------------
[Code] .........
The data in each field can be different lengths, and I would like to take pieces of text out of Column E, and put it in other columns in the row.
Each field has its own column, and I would like to copy the data for that field from the column containing all data to its respective column.
=MID(E1,SEARCH("Year:",E1)+5,SEARCH("Make:",E1)-SEARCH("object",E1)+5)
The formula above was inspired by an "extract text between two strings" post, but will not work.
I tried to make a formula that would extract everything between the strings "Notes:" and "Team Stream:" or for example, so I could get the result "Thank you for using our excel forum! thesmartestpeopleever.com" or "Year" and "Make" so I could get the result "2001" if using the example above. I was not able to do it.
I have attached a sample photo and an excel file.
How do I separate/extract each columns data to the appropriate column?
Attached Image : excel string pic.jpg‎
Attached File : excel string formula conundrum.xlsx‎
1-The employee takes 13 minutes to build each piece
2-The employee starts at 7:15 am
3-The employee goes to lunch from 12:15 pm to 1:00 pm
4-I need to give the employee a date line of time of when pieces should be finished. Taking into account the time he/she takes lunch.
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
I am creating a spreadsheet for my company that lists each one of our 14 stores' performance for a month. This is shown as a (£) figure in one cell and then a percentage underneath.
Question is, is there a way of listing (somewhere on the spreadsheet) the order of branches going from top performer to worst performer (1 - 14) so I know who is the best and who is the worst in the company.
Like this:
1st Place - Br 2
2nd Place - Br 12
etc
I have not found a complete excelsheet that works to my wishes... so I thought maybe you guys could help me.. This is quite a big request. What I want is to add an sheet with a automatic league table to my spredsheed. I already have all the fixtures and the reports will be filled in as the season goes on.
(so what i want is that when i fill in the results of the fixtures there will be an table that automaticlly updated. The +/- statistics isnt essential. I was regarding to +/- in total sets...)
I attached the spreadsheet.
The games will be played as a best of three stets so there will be no draws.
How can I create a rule for a table that finds a specified word and replaces it with another such that the rule would continue to apply to the table even when a new record (or row) is added. There has to be a simple way to do this without a macro.
View 3 Replies View RelatedUsing array
Part 1:
1. Create a Multipication Table up to 10x10.
2. Store the values in a multidimensional array.
3. The program should ask the user what two numbers from 1-10 would he like to multiply.
4. The program should not multiply the two numbers but instead use the two numbers as reference for the element number and locate the corresponding element.
5. The corresponding element should have the value same as the product of the two numbers entered by the user.
Part 2:
1. Do the same as Part 1 but this time automate the creation of the multiplication table using the concept of array.
Here's what I've done so far for part 1
[Code]...
But when I enter the two numbers It just displays the value in cells(2,6)
Attached File : table.xls‎ ..
I have the following code to create a pivot table in VBA
Sheets("test").Activate
Dim data As Range
Set data = Range(Cells(1, 1), Cells(1, 1).End(xlDown).End(xlToRight))
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="data").CreatePivotTable(TableDestination:=Sheets("summary").Cells(1, 1), TableName:="ItemList")
When I run this I get the error message Runtime error 1004: Reference not valid.
There is something in the "set pt=....." that it does not like.