Rearrange Some Cells On Report Based Of Some Criteria Using IF Formula?
Sep 13, 2013
So I am trying to rearrange some cells on a report based of some criteria using an IF formula and getting Blank cells. Here's what I have:
LastRow = Range("A" & Rows.Count).End(xlUp)
With Range("P2:P" & LastRow)
Formula = "=IF($F2=""/FEDERAL EXCISE TAX"",$I2,"""")"
.Value = .Value
[Code].....
So I just want it to identify whats in a cell and populate a related value if the text is present. Do I need to change formatting to find Tex or something along those lines?
View 2 Replies
ADVERTISEMENT
Feb 16, 2014
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
my output needs to be partno, date due, quantity.
View 4 Replies
View Related
Aug 19, 2013
I have a report (roughly 4000 lines) which I need to rearrange for pivot/power-pivot use.
The structure for each row/record is:
col1 geography
col2 area
col3 customer
col4 product
col5 price
col6-65 monthly sales units - 5 years (columns labelled Jan 2009, Feb 2009 .... Dec 2013)
I want to rearrange the data as:
col 1-5 unchanged
col 6 month (data Jan 2009, Feb 2009, etc.)
col 7 sales units
Currently i have 4000 rows/records (each containing 65 fields). Iwant to end up with 4000 x 60 or 240,000 rows/records of 7 fields each. Is this possible through a data import wizard or VBA routine?
View 5 Replies
View Related
May 31, 2014
I am trying to pull a value from a report based on two criteria. I want to get the most recent date that has a value in Col K
1
Col F
Col G
Col H
Col I
Col J
Col K
2
106
ET
Product A
14-May-2014
[Code] ........
I use
{=IF($K$2:$K$60000>0,(MAX(IF($F2:$F$60000=C2,$I$2:$I$60000))))}
On another sheet (I left the sheet names out of the formula to make it easier to read) with C2 as the matching Col F value and it will return the most recent date, May 19th, not the most recent date with a value in Col K, May 18th. The report itself is thousand of lines long and has a couple hundred unique values for Col F.
View 3 Replies
View Related
Jun 20, 2006
I have the following worksheet (see attached). What I would like to do with it to create a report is as follows:
1. Create separate reports for each estimator (KM, JW, WH)
2. Carry over the information in Columns I, A, B & H
3. Each report will filter column I to leave out COMPLETED and just report the number of days remaining is ascending order.
4. Flag data if the days remaining is <=5
5. Generate an email to send out notification of 5 days remaining
6. New report can be generated on any given day
View 9 Replies
View Related
Sep 21, 2006
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
View 9 Replies
View Related
Dec 8, 2009
Im currently using a formula in my report to track training completed by employees. The formula looks for the employee number in the report and the course name and marks Y to say if completed or N to say if not completed.
The level of the employees dictates what courses they need to complete i.e if its a store manager they need to complete all courses where as a bronze employee only needs to complete the first three courses.
At the moment i drag the formula up to the course they need to complete. I would like it if i could add something to the existing formula that looks at their level and then automatically only populates the formula up to the courses they are required to do.
Its very hard to explain so i have added an example:
View 8 Replies
View Related
Jun 17, 2014
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
View 3 Replies
View Related
Jan 9, 2011
I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.
for example
I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.
I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.
Book1.xlsx
View 8 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Dec 5, 2007
I have an excel spreadsheet which calculates the count and value of line items based on a report. I use the sumproduct function to calculate this as it involves multiple criteria. The formula is use is
SUMPRODUCT(('Report 50'!$C$2:$C$64992= Dashboard!$B172)*('Report 50'!$H$2:$H$64992=Dashboard!G$2)*(LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")*('Report 50'!$L$2:$L$64992=15))+SUMPRODUCT(('Report 50'!$C$2:$C$64992=Dashboard!$B172)*('Report 50'!$H$2:$H$64992=Dashboard!G$2)*(LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")*('Report 50'!$L$2:$L$64992=19))
I think the possible error is coming out of this criteria (LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")
Out of one particular field i am picking those line items in which the first 14 characters are "Credit Transfe", if i eliminate this criteria i dont get error.
But this is the main criteria for my calculation. Most of the time i dont get any error , but sometines i get the # Name Error? When i double click on the cell it goes to one column. I dont know what to do from there.
View 2 Replies
View Related
Jun 21, 2008
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
View 9 Replies
View Related
Apr 19, 2008
I saw a post that needed a solution for moving blocks of cells in order to get fewer rows but more columns. The post is gone but I'd like to think my work wasn't in vain as the algorithm was more difficult than I thought. The problem was wanting make a 5000 X 4 grid into a 1700 X 12 grid, 55 rows and 4 columns at a time. This is sometimes done for visiblity or printing purposes to get more data going across instead of down. An example would be wanting to have more data accross on a print page and the page holds 55 lines of data.
For example, A56:D110 would be moved to E1:H55, A111:D165 to I1:L55, A166:D220 to A56:D110 etc. The code is somewhat generic so different blocks of rows and columns can be specified. To see this code work, enter the number 1 in columns A-D, then fill series -> step by one for a thousand or so rows. Then run the code.
Sub FewerRowsMoreColumns()
'Rearranges blocks of cells so the end result is more columns and fewer rows
Dim RowStop As Long
Dim ColStop As Integer
Dim RowStep As Long
Dim ColStep As Integer
Dim RowOffset As Long
Dim rw As Long
Dim col As Integer
ColStop = 12 'Enter the last column number you want the cells moved to
RowStep = 55 'Enter the number of rows you want to move at one time
ColStep = 4 'Enter the number of data columns you are starting with.....................
View 2 Replies
View Related
Apr 3, 2014
I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.
i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2
(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4
test.bmp‎
View 2 Replies
View Related
Mar 26, 2009
Right to the point. I got two sheets in my file, one named "Order" and one named "Input". In the input sheet there is costs & revenues divided into several different divisions/activities. In column A I got the name of the cost/revenue and then the value for every period Jan-Dec in columns B-K. And the name of the activity is in column A under the cost/revenus for that activity, like a " SUM" row.
Now, in the the "order" sheet, you can select a specific activity from a rolllist and then I want that activity's revenues & cost to be shown. I've attached the file to make it easier for you guys to understand my needs. I think I need some sort of 2 criteria VLOOKUP, that is first search för the correct division, once this is found search for the different costs & revenues and the return a value. But I'm not so good at this so I can't get it to work. Maybe the INDEX function is good here but I don't get that at all. I tried Daves "2 criteria Vlookup" but all I get is #ERROR.
View 4 Replies
View Related
Jan 18, 2008
I have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want. Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet. My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item. Issues to consider:
-Line item 1 may not always be the top item. (because the user may not choose the top item)
-There will be spaces inbetween the line items since the user may not pick all of their chosen items in order.
-Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)
This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references
View 2 Replies
View Related
Jan 29, 2008
I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.
Column 1 = Dates
Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group)
Column 5 = indicates either Start or End of Task ('S' or 'E')
(each Task has 2 such listings - a Start [s] and an End [E])
columns 6 & 7 = Misc. & Notes (unimportant as identifiers)
So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).
Tasks List:
Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes
Jan 1 2008 | AA | def | XX | S | B-11 | notes
Jan 1 2008 | FF | xyz | ZZ | S | C-44 | notes
Jan 2 2008 | DD | def | YY | E | J-55 | notes
Jan 2 2008 | GG | abc | CC | S | C-22 | notes
Jan 2 2008 | BB | xyz | DD | S | M-33 | notes
Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
Jan 3 2008 | BB | def | ZZ | E | A-99 | notes
Jan 4 2008 | GG | abc | CC | E | C-22 | notes
Jan 5 2008 | AA | def | XX | E | B-11 | notes
Jan 6 2008 | BB | xyz | FF | S | J-55 | notes
Jan 6 2008 | DD | abc | AA | S | A-99 | notes.............
View 9 Replies
View Related
Feb 1, 2008
I am trying out a new spreadsheet to log my cycling. I have attached the example so far. I am going to have a form appear and fill it out. Upon clicking 'Add' button I want a macro that will search column B (a list of dates) and match the date the users filled out on the form with the spreadsheet and then paste the corresponding form data in that row.
How would I accomplish this? Does the formatting of the date field complicate this since on the spreadsheet I display "Saturday 1/08" and on the form the user enters in "01/08/2008"
View 9 Replies
View Related
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
View Related
Feb 28, 2008
I have a 5 column LIST that will have new rows added daily. Column A= Date (mm/dd/yy), B=id (s or u), C= ArriveTime (hh:mm), D=DepartTime (hh:mm). E is Delay time (D-C). Arrival and Departure times will vary from 5 AM - 9 PM but will always be on the same day. I would like a count of all s with delay of 0-6 min, 7-12 min, 13-18 min, 19-24 min and 25+ minutes. Also count all u with same delays. I should get 10 results (1 of each) using the following data. Would also like the mean, median, mode, min & max for all C (Arrival), D (Depart) and E (Delay) times.
1/1/08 s 08:00 08:01 1
1/1/08 s 08:00 08:07 7
1/1/08 s 08:00 08:13 13
1/1/08 s 08:00 08:19 19
1/1/08 s 08:00 08:25 25
1/1/08 u 08:30 08:31 1
1/1/08 u 08:30 08:37 7
1/1/08 u 08:30 08:43 13
1/1/08 u 08:30 08:49 19
1/1/08 u 08:30 08:55 25
View 2 Replies
View Related
Feb 6, 2012
I have 3 columns of data
Months Date Description Royalty
1 11/1/2010 XTY 5555
2 12/1/2010 xxx 4444
about 100 or more lines of data
Months is the running count of the number of months
Bottom line: Starting at 3/1/2011, I need to know how many months did it take to earn $125,000 (Royalty)
View 3 Replies
View Related
Jul 29, 2006
I have a dataset in Excel with first names, last names, states, cd sales total cost by state... and I am I started a new worksheet cales total sales y state. Now, in this new sheet I want have the states copied over and in the B2 cell I want to write a formula that will enable me to have the total cost from O2:O112 cells ("Total Sales") to be sumed up by each state with absolute references. How would I do this? Example I am currently using =SUMIF(Demographics!F2:F112, "AK", Demographics!O2:O112) which is giving me the correct total sales for AK, but I want to copy this formula using absolute references and have the states change while keeping the fields O2:O112 the same. I just don't want to enter in each state which is what I was starting to do, but It will take a while =SUMIF(Demographics!F2:F112, "IA", Demographics!O2:O112) as an example here is Totals by State! worksheet:
AK $324.50 <---- Total from Demographics! for AK
IA $428.68........................
View 4 Replies
View Related
Dec 26, 2013
I have a sumproduct formula based on some criteria, but I don't know how add another criteria wherein I need to exclude in the count if the date in column F is 1/1/2009
Attached excel file for reference. LE26dec.xlsx
View 5 Replies
View Related
Oct 24, 2013
I need to change the value returned in one cell based on the criteria of a 2nd cell. In short, if the letter 'C' is entered into A1 and B1 contains a vowel the lookup formula needs to return the value found in column C of a separate spreadsheet, but if the 'C' is entered into A1 and B1 contains a consonant then return the value of Column B. I want this to be applied to C, O, R & S only. If A1 contains any other letter, than the lookup formula should return only values found in column B of the separate spreadsheet.
View 3 Replies
View Related
Aug 15, 2007
Need to writing a formula that will calculate the number of times a contract is used each month. Problem is that RMA numbers sometimes repeat. So I need to count the contract number for every unique RMA in a month......
View 5 Replies
View Related
Apr 20, 2007
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.
The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.
I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.
View 6 Replies
View Related
Apr 22, 2014
I am looking for a formula as per the thread title. I have attached an example workbook.
I have been playing around with SUMIFS and SUMPRODUCT but can't crack it.
sum based on criteria transpose columns to rows.xlsx
View 5 Replies
View Related
Dec 7, 2013
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls‎
View 1 Replies
View Related
Jun 23, 2009
I am drawing a blank. I want to use conditional formatting in cell b2 that will outline the cell(I know how to do that) based on a formula. The formula I need would determine if cell a2 is 26,27, or 28. Or function just returns true/false. I would rather not use 2 formulas.
View 2 Replies
View Related
May 14, 2008
how to search for this so I'm just gonna ask. See sheet below I faked the resulting formula I need) for reference.
We have an engineering database that spits out data. One of the things it spits out is a list of equipment tags along with its associate electrical drivers (I.e. motors) and details for each. I need to do some extra playing around with the official equipment list that we have to produce.
In a nutshell, if an equipment tag only shows FIXED in any of it's drivers, I want the formula to show nothing (blank). However, if the word VARIABLE appears in any of that tag's drivers, I want it to show VARIABLE. I know there's a formula involving IFs and whatnot, I just can't figure it out.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1Equip TagDriver NameSpeed TypeFormula234-AG-900Primary DriverFIXED 334-AG-918Primary DriverFIXED 434-AG-932Primary DriverVARIABLEVARIABLE532-AG-931Primary DriverFIXED 632-AG-910Primary DriverFIXED 732-AG-911Primary DriverFIXEDVARIABLE8 2nd DriverVARIABLE 9 Tert Driver 1FIXED 10 Tert Driver 2FIXED 11 Tert Driver 3FIXED 1232-AG-916Primary DriverFIXED 13 2nd DriverFIXED 1432-AG-918Primary DriverFIXEDVARIABLE15 2nd DriverFIXED 16 Tert Driver 1VARIABLE 1732-AG-921Primary DriverVARIABLEVARIABLE1832-AG-922Primary DriverFIXED 1932-AG-923Primary DriverFIXED 2032-AG-924Primary DriverVARIABLEVARIABLESheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
