I have 500+ rows of data in column A (Credit), with corresponding data in column B (Cash). I use "=SUM(LARGE(A:A,{1,2,3}))" in C1 to find and sum the three largest "Credit" entries, how do I sum the corresponding cash values associated with those three large credit entries?
View 9 RepliesI have a workbook with 30 worksheets. Each sheet has 84 rows of data (start in 15 columns (A to O). I would like to create a summary sheet that only shows the most important data from each sheet.
The summary sheet would have 12 lines of headers and formatted crap at the top.
The Summary sheet header columns would be:
Site (A), Date (B) Health (C), Status (D), Critical (E), Task (F),
Dependencies (G), Owner (H), T-Date (I), Task Date (J), Mitigation Date (I)
The data in the sheets are not in that order, of course.
That
1. puts the name of the sheet I am copying from in column A
2. the deadline date in Column B (that date is always in C10 of each worksheet)
3. and copies Cells from Column A,B,G,H,I,O in any row in which the value in A is not "good" into columns C through H. I would like to paste those rows into the summary sheet. I have code that loops through the sheets and rows in each sheet to find the rows to copy. I can copy cell values directly from the active sheet to the summary sheet, but because I am copying a cell at a time, it takes 7 minutes. Yes I am impatient :)
Here is the code snippet where the copying is done:
Dim sh As Worksheet 'current worksheet
Dim DestSh As Worksheet 'worksheet in which to paste summary
Dim Last As Long
Dim CopyRow As Long 'row to copy
Dim LastCopyRow As Long
[Code] ........
ExitTheSub:
Application.Goto Sheets("KMARollup").Cells(1)
End Sub
I think there must be a way to use ranges to build an array of cell values and paste only once but I am lost here.
I have a couple of spreadsheets that has several columns each containing several hundred thousand rows of codes. To quickly analyze this data, I am trying to come up with a conditional formatting formula to highlight the respective cells when specific values occur next to each other. A particular code will show up in many cells, but the code that is the respective adjacent cell is always different. I need to know when row A contains, for example, '9928559' and row B contains '36415RT'.
View 3 Replies View RelatedI have 2 columns of data and want to be able to merge as follows
COL A COL B
4123567 123.45
4125467 900.56
4356456 456.32
need to get result of
COL a
4123567
123.45
4125467
900.56
4356456
456.32
I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).
I would like a comma or full stop and then a space between each item merged (cell A5).
The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.
Only where all 4 cells are blank, will I need cell 'A5' to indicate this.
The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
http://img530.imageshack.us/img530/6239/excelspfk0.jpg
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.
I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
Code:.....
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
I have multiple sheets cataloging multiple vehicles' mileages in multiple areas (one sheet per month). I want to reorganize this data by Vehicle ID rather than month.
Here is an example spreadsheet to help explain
I have attempted this by writing this formula ...
I have two simple columns of data: one with names (Column A) and the other with numbers (Column B).
I am looking for the most efficient way of selecting cells in Column B of a certain value and also selecting the cells to the left of those selected cells in Column A.
I have the current code based on SpecialCells, it works most of the time but for some reason it's buggy and sometimes returns an error. Is there another way doing this without using Special Cells?
Code:
Sub SelectbyValue()
Dim Cell As Object
Dim FoundCells As Range 'Range that's found
Dim WorkRange As Range 'Range to search
Range("E1:F4").ClearContents
[code]....
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies View Relatedthe question: i'd like to split a large cell in to smaller, fixed-size cells (columns) by a standard formula.
example: consider cell A1 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' i'd like a formula that created B1 = 'ABCDE' C1 = 'FGHIJ' D1 = 'KLMNO', etc
I used =Left (A1, 5) and got A1 = 'ABCDE' easily enough. I'm stumped at coming up with a formula that provides the next 5, and the next 5, etc.
the setup: if you want to know why i really need to solve the question i'm asking rather than using the split cell function:
i have a large set of data from a vendor. ideally, this worksheet will be used for other vendors that submit data similarly.
the script i'm writing takes a specific format--which i can't logically hold the vendors to. so, my specific formatting is on one sheet, referencing the data from each vendor on another sheet.
there is one field of data from vendors that is large ( >4000 characters, typically). however, this field makes my SQL server puke. i need to break this field down in to chunks of 1000 characters, and then re-concatenate after it leaves SQL to its final destination.
because i want to use some script automation to reuse this project, i'm reluctant to just use the split cell function to break up the data. rather i'd like to use whatever data is provided, and referentially split it up in my pre-formatted data.
I need to link 153 cells from one workbook to another
Is there a code that can do this ? The cells are not contiguous.
Something like.
If not intersect.....
SetMyrange......
With tartget.Copy.... ??
I'm trying to use the Large function with multiple cells to get the highest value of the cells. Excel won't let me use the following formula.
=large({a1,f1,j1,o1},1)
Any other ways to get the largest value from multiple cells with or without an array.
Is there a way to return not only one cell but two with LARGE?
I'm still working on my top 5 sellers list and trying to generate the list correctly. As of now when I have multiples of the same value I get the same name. Here's what I have now:
Name---Sales
Bob---75
Sue---68
Tim---66
Tim---66
Tim---66
What I'm trying to do is this:
Name---Sales
Bob---75
Sue---68
Tim---66
Jane---66
Dave---66
As per the attached file – I am trying to mark (with an X) the first 5 Adjacent cells which SUM up (and above) 480. As you will see, in some instants, one of the 3 formulas is correct. I am looking for a formula to work correct IN ALL situations.
In Col. A I marked (light Blue Background) the FIRST 5 cells which meet the Condition. In Col. F are the sum results and those which meet the Condition
are in Bold RED.
I have the attached spreadsheet which makes the question easier to understand. Basically when you read across and down rows and columns, I'd like the intersecting cell to turn red if the horizontal and vertical row columns letters are different. So in the attached B5 is red because B1=A and A5 is B. Where the intersecting cells match in values, the cells will stay white.
I can create a formula to conditionally format one cell to red: =A2<>B1
But I have a large grid and it would mean re-creating each formula for each cell. How to expand it to the whole range?
My data set consists of 10,000 rows and 5,000 columns. I want to search the Used Range and replace all cells which say "NaN" and make them blank.
Any code i currently use takes way too long.
This is my first post on this forum and I am an Excel novice. I have a worksheet with lots of blank cells and I need to shift all the cells with data all the way to the left (to column A). I cannot get the sort or filter functions to do this for me, and I've never used macros but it appears they might be useful for my problem. I'd like to find an automatic process to move the data left rather than drag and drop hundreds of times. I'm attaching the complete xls file for your viewing.
View 5 Replies View RelatedDoneyOte on this but i have added to the range and im now getting an error that i can't seem to fix
View 12 Replies View RelatedHow to get the LARGE function to ignore hidden cells? Is it possible?
I have a filter in a data set and am using a macro to pull in the top 5 largest values.
I have a sheet with 200 sheets in called workbook x , I need to create a summary new workbook with only data on 1 sheet.
I want to pull specific cells from the large workbook x to 1 single sheet on a summary workbook:
A6 I want to be surname taken from B1 of workbook x
B6 will be first name taken from C1 of workbook x
Then I will copy data from M1 on mastersheet to cell C6 on new workbook
Then I want to copy Row A11 up to T11 and paste on the new workbook (Summary) in D6 staying in the row 6
Not sure if this is the tricky bit I want to repeat as above B11 to T11 but paste in the next empty cell of the same row of D in my summary
I need to repeat this action upto A23 to T23
Then I need it to move on to the next sheet in X and repeat all the steps above but do it on the next row down row 7 and so on
I have a set of data covering cells A8:C71
Column A lists locations - there maybe upto 6 entries with the same location.
Column C lists a unique serial number.
The list is sorted by column A (A-Z).
For this example say location LONDON has 3 entries, with serial numbers 120, 125, 128.
In cell F1, i want to display the lowest serial number for LONDON
In cell F3, i want to display the 2nd lowest serial number for LONDON
In cell F5, i want to display the 3rd lowest serial number for LONDON
I have tried the following formula, with no success..
=LARGE(VLOOKUP(I9,A8:C71,3),1)
What i'm trying to do is to check a large number of cells, if "0" is written down in that cell it stays as it is and if the number is different from "0" i want to put a "1" instead.
View 5 Replies View Relatedwhen I select 30,000 Row and 3 Columns then Edit>GoTo>Special>Blanks , Excel gives me MSG : selection is too large
View 2 Replies View RelatedI heard there is an efficient way of writing large amounts of data at once to a worksheet. My program is writing alot of data (e.g. 600+ items in each row) and takes over an hour to run because it writes to each cell one-at-a-time. I seached this forum and couldn't find the answer. Here is an excerpt from an old 1998 article by Dermot Balson and William Mercer that used code to write to cells in large chunks but it doesn't work for me - I get a method failed message.
'to read in
Dim A As Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)
'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..
Here is the link to the article: http://www.avdf.com/apr98/art_ot003.html. A more detailed explanation how this trick works
I need to have three rows link to each other. The first column cell A is a paste link and is always changing. The second B is a vlookup referenceing A and finding it in a changing table...the source for the paste link. C needs to follow the other two cells and remain "attached" to it. However, C has to be a cell that is hardcoded somewhere, meaning that C is not found in the source data and must be determined by a person. I have tried to link the sheets through vlookups and other formulas, however, this only works when the data is sorted and nothing new is added. I need a static sheet that can take in new values. I need to create some sort of relationship between the cells
View 14 Replies View RelatedSee the attatched spreadsheet. I can not seem to figure out a formula that will calculate the totals for each type of data.
My goal is to have a sum for each location for # of audits, Critical Counts, and Major Counts.
I have list of names of people in a billboards league. Directly to the right of everyone's name in the adjacent cell is their "win percentage". These percentages are sorted in descending order and I need the names to move along with the adjacent cell as the percentages change. When i merge the cells things get really messy.
View 1 Replies View RelatedI need the syntax that allows me to reference the cell directly beside my target cell for each row in a column
Like:
Col A Col B
S D
T F
S F
If i was targeting Col B