is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"
Below is a small sample of data, real data is around 100 rows
Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.
I need to be able to find a value in another sheet based on 2 criteria
The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).
The formula I use is as follow: =LOOKUP(BA2,Budgets!B:B,Budgets!I:I)
and this is what I use to get the column: =+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here
and I use this too: =MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too
If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....
What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..
I have tried index and match and quite frankly, I never made it work, always getting an error.
I've got an interesting problem which I haven't been able to find an answer for.
A B PR Yes PR Yes PR Yes DN Yes DN No DN No
I need to count how many unique values in column a have every row in column b the same value. So counting for yes values, PR would equal one but DN would equal zero.
I used this formula to find out how many unique files had yes values, now I need to find out how many files only have yes next to them.
I have 3 columns. In 1st there is a name of the product.In 2nd there are different types of product (6 in total) and in the 3rd column a figure attributed to the correspondend type of product. I need to create a summary for every type. For this I want to show the 5 largest figures(based on the name of product) for every type.
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time. The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following: Shift 08:00 - 17:00 Tea Break 10:00 - 10:15 Lunch Break 12:00 - 12:30 Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
I need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.
I have a worksheet consist of some 17,000 records - from ColA:ColDZ. In Datasheet, I have all values. From this, based on some values in ColA all records should be copied into another sheet(s).
I have attached a sample workbook in which I have explained the requirement.
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............
I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
I'm facing a big problem and I can't found a solution for days.
I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
Let me Put to you an example for you been able to understand my problem.
Example considering Range "C2:C20" exluding blank cells
(1) - To count unique values =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
(2) - To count unique values based on criteria in range "B2:B20" =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))
(3) - To sum unique values on "E2:E20" based on same criteria =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))
They all work fine. But I need to insert a merge at Row 10 for better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's. I just don't know what to do guys ....
I have a problem with excel that I have been trying to solve for the past 5 days! I have 2 tables (one with 500 rows, the other with 15000) that contain information about pipes.
The smaller table, Drift List, is to be used as a reference to fill up the bigger table. Here is how Drift List looks like:
i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....
Option Explicit Dim lastrow As Long, t As Long Sub Method() lastrow = ActiveSheet.UsedRange.Rows.Count For t = lastrow To 1 Step -1 If Cells(t, 8).Value <> "" Then If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _ 6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20 End If Next t End Sub
Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.
I need to display values from cells of sheet1 and sheet2 as comment in Corresponding cells of sheet3 whose value in sheet3 is FALSE
Sub try() Dim Sheet1cellval As String, Sheet2cellval As String Worksheets("Sheet3").Range("A1:D4").Select Dim cell As Range For Each cell In Selection If Worksheets("Sheet3").cell.Value = False Then cell.AddComment cell.Comment.Visible = False 'Getting the value of Cell in Sheet1 for the corresponding cell in Sheet3 whose value is FALSE Sheet1cellval= Worksheets("Sheet1").cell.value 'Getting the value of Cell in Sheet2 for the corresponding cell in Sheet3 whose value is FALSE Sheet2cellval = Worksheets("Sheet2").cell.value 'Displaying the values of cells in Sheet1 and 2 as comment for the corresponding cells in Sheet3 Worksheets("Sheet3").cell.Comment.Text Text:="Difference" & Chr(10) & Chr(10) & "Sheet1: " & Reportcellval & Chr(10) & Sheet2cellval & Chr(10) & ""
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine
So shortly: Transportnumber double/triple...+ Postcode double/triple...= combine these rows.
if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.
So example of end result for 106200 would be 106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to) 106200 8600 value ( this was unique so it stays unique)
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program Clinic Title Start Date
[Code].....
formula that will automatically give me that answer?
Second Tab Account Name Account Number Account Ship Location Number
On the first sheet each account name and number only appears once.
On the second sheet there may be multiple entries for each Account Name and Number.
On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?
I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.
removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.
Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.
I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria
I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.
What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating Ford Bronco 64 Chevy Corvette 94 Dodge Intrepid 83 Chevy Chevette 34 Dodge Viper 72 Ford Escape 21 Ford Expidition 53 Chevy Impala 67 Ford Fairmont 11 Dodge Dart 33
In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD
I am very new to Macro programming. I want to set up a macro which will copy data from few cells in a sheet and paste them as "Values" to certain specific locations in another sheet based on criteria. This copy paste needs to be done for about 40 different cells. I want to fix this with a button on the source sheet, so that this process happens when the button is clicked. I am attaching a sample file showing what I need.
ok lets say you have thousands of rows of raw data and you are running vlookups to pull specific data from the raw data to update your spreadsheet. Your raw data consist of some new ideas so when you vlook up, you get an error. In my case i get about 50 errors all spaced out amoung the thousands of rows or raw data. I would like to create a formula that would go and find the error and output a code/value that already exist in the raw data and output that in Cell D1 for example. The it will go and find the second error and output that into cell D2 and then the third error and output that into D3 and so on.....
The idea is that when its done a list of all the errors will have been created so then i can use other formulas to simple add the new info to the workbook. Instead of search through thousands of rows for the errow.
