# Turn To Values Every Nth Column Or Based On A Criteria?

Apr 14, 2014I 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.

View 1 RepliesI 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.

View 1 RepliesADVERTISEMENT

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.

If i have the following layout of data

Column 1 Column 2

January 1000

January 1234

February 1300

March 1600

January 15

March 123

April 234

January 3000

I would like a formula that adds all the January numbers together returning a result of 5249

To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.

I'm sure i have done this before using a countifs maybe but my mind has drawn a blank

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.

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.

The below sample is an accurate example of a much larger set of data. Column H will be my desired result.

I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.

A

B

C

D

E

F

G

H

1

Item#

Description

Quantity

Month

Year

2

1001

[Code] ........

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 would need an excel formula not macro

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.

Sample Workbook.xlsx

i had a database from which i need to extract data from COlUMN B, select the

View 2 Replies View Relatedis 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.

View 2 Replies View Relatedi m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function

Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

CriteriaHeader 1Header 2Header 3Header 4

ZZZ5.0015.0050.00130.00

ZZ5.0015.0050.00130.00

Z5.0015.0050.00130.00

YYY5.0015.0050.00130.00

YY5.0015.0050.00130.00

Y5.0015.0050.00130.00

DDD5.0015.0050.00130.00

DD5.0015.0025.0075.00

D5.0015.0020.0065.00

RRR5.0015.0015.0045.00

RR2.5010.0010.0030.00

R1.503.0010.0025.00

UUU0.751.505.0020.00

UU0.751.505.0020.00

U0.751.505.0020.00

P0.751.505.0020.00

T0.100.105.0018.00

CriteriaNumberValue

DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

View 1 Replies View RelatedEvery so often I get a spreadsheet form some one else, and when I start to edit it formulas turn immediately into values.

For example, I enter a =10/2 ,when pressing enter, the cell displays correctly the result, 5. But when I click into the cell, instead of having the formula

=10/2, the only value inside is 5.

How do I keep this happening ??? I want the cells to keep the formulas.

is there a way or a formula that turns numbers into their respective words:

ie Cell A1: 8500

Cell A2: Eight Thousand Five Hundred

Big ask I guess but its for a banking model so I can't be the first to have considered this

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4

5/1/2013 1:0012.4

5/1/2013 2:0012.5

5/1/2013 3:0012.5

5/1/2013 4:0012.5

5/1/2013 5:0012.4

5/1/2013 6:0012.4

Here is a table

Col1 Col2 Col3 Col4

8821015m14:44.0E__uMail

9266321m34:13.0E__uMail

92677165m23:43.0E__uMail

9309415m07:24.0E__uMail

[Code] .....

Here is some context

Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).

In this table the number of tickets that were:

submitted via Email is 14

submitted via Voicemail is 1

submitted via Walk In is 2

submitted via Ask IT is 1

Col1 Col2 Col3 Col4

8821015m14:44.0E__uMail = 1

9266321m34:13.0E__uMail = 2

92677165m23:43.0E__uMail = 3

9309415m07:24.0E__uMail = 4

[Code] ....

What I need is a formula that will produce this result.

I am trying to sum values based on 2 criteria -- the date (using a fiscal month) and a Category (A,B,C,D). Here's a small portion of the source data:

Sheet3

JKL1DateValue Plant21/28/2012 50.00 A31/31/2012 10.00 B42/6/2012 40.00 C52/10/2012 35.00 D62/15/2012 8.00 A72/17/2012 100.00 B82/21/2012 50.00 C92/23/2012 10.00 D102/27/2012 40.00 A113/2/2012 35.00 B123/6/2012 8.00 C133/8/2012 100.00 D143/12/2012 50.00 A153/16/2012 10.00 B163/20/2012 40.00 C173/23/2012 35.00 D183/26/2012 8.00 A193/28/2012 100.00 B

And here's the result I want:

Sheet3

NOPQRS1StartEndABCD21/28/20122/3/20125010 32/4/20122/10/2012

403542/11/20122/17/20128100 52/18/20122/24/2012 50106

Feb Total58110904572/25/20123/2/20124035 83/3/20123/9/2012

810093/10/20123/16/20125010 103/17/20123/23/2012

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

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

View 12 Replies View RelatedI have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.

Here is what I have:

Option Explicit

Public Sub RatingbyDept()

Dim Dept As Range

With Sheets("Master")

For Each Dept In .Range("I2:I1000")

With Dept

I keep getting error 92 - "For loop not initialized".

I have a workbook where I am trying to COUNT the values greater than zero in column "J" based on a selective criteria of column "B".

In other words, if the date in a column "B" cell (B3:B78) is greater than the date in A86, I want to count the number of cells whose corresponding value in the "J" column is greater than "0". And then I want to take this selective COUNT and divide it by a COUNT of all the cells in the range "J3:J78".

So, what I am trying to do is:

FORMULA /COUNT(J3:J78)

I have 2 worksheets with the below data.

Sheet 1

Part Number Client X Client Y Client Z

127-003104-03 $38,835.00 $27,756.75 $38,835.00

127-002114-03 $31,600.00 $27,037.50 $31,600.00

127-010113-01 $25,000.00 $24,270.00 $25,000.00

127-001113-03 $25,000.00 $24,270.00 $25,000.00

138-001113-01 $27,900.00 $26,790.00 $27,900.00

Sheet 2

PurchaserPart # Outward Rate

Client X 127-002114-03

I want a formula for "the Outward rate" which will check the 2 criteria of the client name and product code and return the unit price for the product.

Add Multiple Values Based on Criteria

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.

Here is the sample worksheet (Please click).

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.

=SUMPRODUCT(($B$2:$B$14="Yes")/COUNTIFS($B$2:$B$14,$B$2:$B$14&"",$A$2:$A$14,$A$2:$A$14&""))

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.

Like this:

AAA GK 40

BBB CD 50

CCC FD 55

DDD GK 60

EEE FD 70

.....

I have 1 column of pasted values that basically look like this

c

1:a

2:b

3:c

1:d

2:e

3:f etc...

And they need to look like this:

c1 c2 c3

a b c

d e f

I know how to separate the first column into two columns by separating the values by the colon. But how do I rearrange the values as above and combine all "1"s in c1 so it becomes the headers and the column is populated only with the values? The table is too big to do manually (thousands of rows).

I have 2 columns; 1 with "dates" of incidents, the other with "number of incidents". The dates are not in any order but I want to be able to count "number of incidents" for certain time periods. e.g. If dates are between 01/02/14 and 28/02/14, what are the number in incidents. This number would be represented on a new worksheet.

I've looked at COUNTIF/S, SUMIF but just cant get there.

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