Select Cells Based Value In Adjacent Column

Jan 4, 2008

How would you select cell values from differing rows based on the name in an adjacent column. ie, if it says John, add that data to the source data, if not move on.

View 5 Replies


ADVERTISEMENT

Count Unique Cells Based On Adjacent Column

Jan 3, 2008

I have a worksheet with following data in 1 tab.

[TABLE]
Seq Class
SE110
SE270

SE110
SE370
SE310

SE110
SE170
SE370
[/TABLE]

In a different tab I want to calculate the unique count of sequence for a specified class.

In above eg: for class 10 it should return 2

View 3 Replies View Related

Select Non-adjacent Cells

May 30, 2008

I've always been able to select non-adjacent cells in spreadsheets, but today noticed that I can't. I need to be able to do so to collect data from one spreadsheet to another for my monthly accounting reports. It makes collection of specific data fast and simple. Without this function, it takes far longer to do reporting and is very un-reliable. With the function working, I click on the cell ranges which are black, leaving the cell ranges which are red alone. We use black and red to identify charges which appear or do not yet appear on our monthly VISA statement.

I select all the black charges along with their corresponding account codes, then double-check the page before copying the non-adjacent ranges to make certain that all of the charge amounts and account numbers which are black on the page are selected and no red ones; then copy and paste the data into another sheet. It often makes collection very quick and easy.

When there are lines interspersed throughout a sheet with black and red charges strewn here and there, it is very easy to select only the black data and skip the red. Doing so by copying the entire range then deleting the red is laborious, and time-consuming.

What has happened to Excel's ability to select non-adjacent cells? Today, I can't select even two separate cells, using Excel's ability to do so by holding down the CTRL key, as I have in the past.

I'm using Excel 2007, on Windows Vista 64 bit; and I've made sure that my Insert function is turned off (thought that might change things?) and my Scroll Lock is turned off (I've bumped that many times in the past and found Excel doing weird things when trying to navigate a spreadsheet with the arrow keys).

View 9 Replies View Related

Select A Range Of Non-adjacent Cells

Jun 29, 2006

I have a series of data values in non-adjacent columns in an excel spreadsheet.

In the following example, assume the | (vertical bar) refers to the start of
a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row (starting with Cell A1) for validating they equal F, and a range of the alternating cells (starting in column B1) for the range containing the data to add. How can I specify these ranges (I can't name each cell individually as I have more than 30 cells to add up in my real life situation and the IF function allows selection of no more than 30 values)?

View 12 Replies View Related

Select Adjacent Surrounding Cells

Apr 23, 2008

If I write some VBA that selects a specific cell, for example the highest value in a coumn of data, how do I then copy a given (say 3) cells above and below (and including) this selected cell.

View 6 Replies View Related

VBA Macro To Select Cells With Certain Value And Perform Action On Adjacent Cell

Mar 10, 2014

Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.

Attached File : LoopingVBA.xlsx‎

View 3 Replies View Related

Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers

Jul 19, 2009

I am trying to select a range based on two variables which store the column numbers. what I have is:

View 4 Replies View Related

Highlight Repeat Cells In One Column If Cells In The Adjacent Column Contain Specific Text?

Apr 14, 2014

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 Related

Formula To Pull Latest Date From One Column Based On Entry In Adjacent Column

Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed
Disposal Order
Latest Decision date for D.O.

06/05/2014
D.O.001

[Code] ........

View 6 Replies View Related

Average From One Column Based Upon Criteria From An Adjacent Column

Jan 13, 2008

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 Related

Sum Based On Adjacent Column Value

Jun 20, 2008

I have a spreadsheet with a list of account numbers and values(sheet1) and on a seperate sheet (sheet2) a list of all unique account numbers that appear in the transaction data sheet.

What I am trying to do is get a total sum for the values of each account number. What I am getting well I'm not quite sure what I'm getting. The problem appears to be when the xSubtotal variable is reset to 0 at the end of calculating all the values for the account number.

View 6 Replies View Related

Color Cells Based On Adjacent Cells Being Empty & Add Text

Apr 2, 2008

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

View 9 Replies View Related

Copy Cells From Column To Adjacent Column If Bold Or Empty

May 20, 2008

I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group.

The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.

View 5 Replies View Related

Fill Empty Column Cells With Reference To Adjacent Cells

Aug 11, 2008

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.

View 9 Replies View Related

Dynamic Averaging Based On Adjacent Column?

Nov 2, 2013

I have a column with random values that are generated my my machinery.I should not sort them, because they provide me with the performance graph. Lets say i have column "A"

0
2
9
9.5
30
90
22
15
9
0

As the max value of this is 90, and its 10% of MAX is 9, which occurs at 3rd and 9 th positions.I need to find the average of its adjacent range, with zeros not considered.It has to calculate the max value in the column, find 10% of max and do average of adjacent column from the sl. no of first occurrence of 10% value in first column to the sl no of last occurrence of 10% value in first column.

the exact 10% value may not always exist. 10% of 90 is 9, but if the max value is 94, the 10% would be 9.4 which doesn't exist. then it has to take the nearest available predecessor in that case.i get values in decimals mostly and only some of them are perfect integers, thus this consideration goes with it,and also, my range actually is B14:B10013

i would most probably have the 10% value within the first 50 "non zero" entries and reoccurring somewhere probably within the last 50 "non zero" entries.

View 7 Replies View Related

Conditional Formatting Based On Adjacent Column Value

Feb 25, 2009

I'm trying to highlight cells a certain color based on the value of the cell 1 column to the left. How would I be able to do this in conditional formatting?

Example)
Highlight the cells in "Visit 2" column if it is greater than the "Projected Visit 2" column.

NameProjected Visit 2Visit 2Person 124-Nov-0820-Nov-08Person 226-Jan-0927-Jan-09Person 32-Jan-082-Jan-08Person 430-Dec-0829-Dec-08Person 46-Nov-0830-Oct-08

View 9 Replies View Related

Total Cells Based On Adjacent Cells

Jun 28, 2008

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 ...

View 3 Replies View Related

How To Conditional Format Based On Adjacent Cells

Oct 18, 2013

Picture1.jpg

How do I create a CF if a cell is blank (in my case represent no sales) i want to have it yellow (ex. C2)

But if it has two consecutive blank cell i want to CF with Red. (Ex. F3)

But i also need to exclude blank cells when we we have no previos sales of that item (Ex.B3).

View 3 Replies View Related

Combining Cells Based On Value Of Adjacent Cell.

Jan 11, 2009

I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.

A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,


What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode

View 2 Replies View Related

Repeating Adjacent Cells Based On Formula

Jun 4, 2014

Based on a trigger point (being a month), and the number of times the expenditure is spread over (factor), can I formulaize the repetition of the figures?

Sheet1 *CDEFGHIJKLMN5***Trigger Points**Desired result via formulae
**6CostFactor*JunJulAugSept*JunJulAugSept7
£ * * * *10,000 2**1**** £ * * * *5,000 £ * * * *5,000 *8 £ * * * * *6,000 3*1**** £ * * * *2,000
£ * * * *2,000 £ * * * *2,000 *9 £ * * * * *8,000 1***1**** £ * * * *8,000 *

View 2 Replies View Related

Find Value Specified & Calculate Based On Adjacent Cells

Nov 27, 2007

I am really stumped at this particular point in my coding. What I am trying to do is to have a user input an ID number. When they click OK, I want to find the ID number and then subtract 1 from a number that is 13 columns over. So if the ID number is "123456789" and that matches the value in A3, I want to subtract 1 from the amount in M3 (leaving the new value in cell M3). So, if M3 was 30, now it would be 29.

View 2 Replies View Related

Gathering Sum Of Cells Based On Text In Adjacent Cell?

Jan 15, 2010

I have a column of dollar amounts (variances) and next to each is the shift they belong to.

I am looking to get a break down of the variances for each shift.

Attached is an example of what it looks like.

View 9 Replies View Related

Conditional Formatting Based On Top 10 Values In Adjacent Cells?

Jun 3, 2014

I have a matrix in excel to show % and $ for multiple columns - set up like this:

Rows Part 1 Part 2 Part 3
Criteria 1 % $ % $ % $
Criteria 2
Criteria 3

the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.

View 6 Replies View Related

Gathering The Sum Of Cells Based On The Text In The Adjacent Cell

Jan 15, 2010

I have a column of dollar amounts (variances) and next to each is the shift they belong to.

I am looking to get a break down of the variances for each shift.

Attached is an example of what it looks like.

View 6 Replies View Related

Delete Rows Based On Adjacent Blank Cells

Aug 8, 2008

I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this?

Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1
If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete
Next r
End Sub

View 2 Replies View Related

Alignment Of Adjacent Cells After One Column Is Aligned?

Dec 31, 2013

Example of Cell Movment.xlsx

The following attachment should explain what I am trying to accomplish.

View 2 Replies View Related

Copying Cells Down To Next New Data In Adjacent Column

May 26, 2006

In column A, I have unit numbers. Column B is blank. I need help copying the unit number in Column A down in Column B until a new unit number appears in column A. For example, in A6 is unit LS2, A12 is unit LS24, A17 is unit LS34. I would like to be able to copy A6 into B6:B11, A12 into B12:B16, and A17 into B17:B22, etc.

I have the code to copy A6 in to B6 and down, but I don't know how to make it stop at A12. Attached is also an example.

View 4 Replies View Related

Merge Adjacent Cells At Each Change In Column

Apr 8, 2008

I have a list of values, some are like values, others are not. is there a way to have the column to the right automatically merge the cells which have like information to their left. Attached is a sample file. I want to be able to have the cells in column B automatically merge based on the values in column A. I'm not sure if this can be done, but figured I would ask before I spend hours merging cells.

View 2 Replies View Related

Macro To Populate Cells With Formula Based On Adjacent Cell?

Mar 31, 2014

I have a spreadsheet with data populated down column B. This size will vary from day to day so is there a macro I can run that will populate Column C with a formula based on their being data in the adjacent cell.

View 2 Replies View Related

Copy Cells To Adjacent Column, Removing Blanks

Dec 8, 2009

In a worksheet, J2:J52 contains a list of unique strings; each cell populated by an embedded formula. Some of the cells contain a valid 'blank' "" given by the formula.

I'd like K2:K52 to contain the values J2:J52, with all the populated cells stacked at the top and the 'blank' cells at the bottom. 'Blank' cells do not have to be in any particular order.

I've been fiddling with the formula kindly provided by WindKnife on the second post in the thread below for the past couple of hours, but to no avail:

http://www.excelforum.com/excel-work...om-a-list.html

Columns A:H in the same worksheet are populated and unavailable for use.

View 8 Replies View Related







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