Excel 2010 :: Selecting Corresponding Values From Multiple Columns

Feb 18, 2014

I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'

I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'

See the attached image to make it clearer..



View 3 Replies


Excel 2010 :: Selecting Certain Desired Columns At Once

Dec 5, 2011

I wish to select all the cells which contain the word " Available " in column A at once . But i didn't find any option to do so using excel's Go TO Special feature.

I am using Excel 2010.

View 4 Replies View Related

Excel 2010 :: VBA Selecting Only Certain Columns In A Table

Sep 10, 2012

I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.

This is the code I have right now, which works beautifully for the whole table:

ActiveSheet.ListObjects("Table36").Range.AutoFilter Field:=6, Criteria1:= _
"=Alan", Operator:=xlOr, Criteria2:="="

What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?

Using Excel 2010.

View 2 Replies View Related

Excel 2010 :: Cannot Unhide Columns By Selecting Entire Worksheet

May 29, 2012

I have Columns A to C which are hidden. I have tried to unhide these bey selecting the entire worksheet but to no avail. I am using Office 2010.

View 4 Replies View Related

Excel VBA - Selecting Range Of Data Considering Multiple Columns

Feb 11, 2014

I would like to loop through cells row wise.

Columns which have data are A:E

modify below code to select the range from columns A to E?

[Code] ........

View 3 Replies View Related

Excel 2010 :: VLookup Based On Values From Two Columns?

Jun 4, 2012

I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.

Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas

Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas

View 5 Replies View Related

Excel 2010 :: Sum Multiple Columns To Get YTD Amount?

Jun 16, 2012

I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-

Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..)
Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011.
Cell B2 onwards, down and to right, all spent amounts by month

What I need is a formula to get year to date number, which will change to Executives requirement.


Year to dateMar 2011( Months will be changed)
Account 4101( Accounts will be changed)
Amount should be 600 What Formula ?

My excel version is 2010.

View 7 Replies View Related

Excel 2010 :: Filtering Multiple Columns (OR)

Jul 18, 2012

I have a worksheet with

Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)

There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.

I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.

For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.

I am using Excel 2010.


Person 1

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

View 3 Replies View Related

Excel 2010 :: Sort Multiple Columns Simultaneously?

Jan 3, 2012

I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).



View 2 Replies View Related

Excel 2010 :: Comparing Multiple Columns Of Data (Last Name / First Name)

Aug 8, 2012

I'm using Excel 2010 on Windows 7.

I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.

Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.

It would look something like this



So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:

1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.

View 6 Replies View Related

Excel 2010 :: Converting Multiple Columns Into Rows?

Aug 24, 2012

I am trying to convert

Excel 2010


View 9 Replies View Related

Excel 2010 :: Creating Scatter From Multiple Columns Using Ctrl

Jun 12, 2014

I am trying to create a scatterplot from some data I have. I attempt this by selecting multiple columns using Ctrl and clicking on the letters above. This results in it plotting only the first selected column on the Y axis with only 1 2 3... on the X axis.

When I however select multiple columns by clicking on a column and dragging to more columns it does plot as I would expect with the first column as X values and the rest Y values.

I know it is possible to create a graph and add data manually but this is way slower and I think that it should be able to be done differently.

I'm using excel 2010.

View 3 Replies View Related

Excel 2010 :: How To Take Data From Multiple Columns And Add Them Onto Single Column

Mar 8, 2013

I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.

Data Looks like:




Would like to look like:








View 5 Replies View Related

Excel 2010 :: Looking Up One Value And Need To Return Multiple Values Horizontally

Mar 28, 2014

I have in column A duplicate values and in column B different responses (Sheet: Lookup). I need to look up the value in column A (Sheet: Results) and bring back all the responses in column B (horizontally).

Nittie Query.xlsx

View 5 Replies View Related

Excel 2010 :: Countif And VLookup Of Multiple Values

Jan 15, 2014

I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.

I have attached a sample workbook as a reference : Sheet1.xlsx‎

View 8 Replies View Related

Excel 2010 :: Showing Multiple Values Per Cell?

Jul 31, 2014

When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.

If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.

View 5 Replies View Related

Excel 2010 :: Compare 2 Columns In Different Sheets - Unique Values Output To 3rd Sheet

Feb 4, 2013

I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.



Excel 2010

View 9 Replies View Related

Excel 2010 :: Count Consecutive Numbers Over Multiple Columns / Rows

May 27, 2014

I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:

0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2

I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:

Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)

Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0

Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0

and so on....

View 9 Replies View Related

Excel 2010 :: Convert Multiple Columns To Rows Depending On Input Value

Jul 6, 2014

I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:


If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result

.. ..
.. ..

It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.

View 6 Replies View Related

Excel 2010 :: Using Multiple Checkboxes To Hide And Show Specific Columns

May 12, 2014

Basically I have a big table containing several columns for the different quarters. (Each quarter has several columns which are not all next to each other)

I would like to use 4 checkboxes which the user can select the show or hide the columns for that quarter. E.g. if Q1 and Q3 are checked only those are visible)

(if quarter 1 clicked than all columns are visible if unclicked hidden).

The code I have written workes well for the first checkbox (Q1), but for the other quarters (Q2, Q3 and Q4) the columns get hidden when checked (which should be the other way round) and don't get unhidden when unchecked.

Sub CheckboxQone()
If Range("$A$1").Value = True Then
Call showQ1
Else: Range("$A$1").Value = False
Call hideQ1

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

View 2 Replies View Related

Excel 2010 :: Conditional Formatting - Highlight Values Based On 2 Columns Of Numerical Data

Apr 12, 2013

I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:

Column F:

Column L:

I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.

Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.

View 3 Replies View Related

Excel 2010 :: Sorting Multiple Values In Ascending Order

Dec 29, 2012

I am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).

View 14 Replies View Related

Excel 2010 :: Sumproduct Of Unique Values In Multiple Ranges

Jul 31, 2014

I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.

I'm using Excel 2010.

View 9 Replies View Related

Excel 2010 :: Sumproduct With Multiple Criteria And Ignoring Text Values

Jun 19, 2014

Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.

Here is the set up:

Column AColumn BColumn C

I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))

The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.

View 5 Replies View Related

Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 Replies View Related

Excel 2010 :: Sorting On Multiple Columns - Method Range Of Object Global Failed

Feb 25, 2014

I need a macro to sort on five columns (Column A, L, P, X, and Y) out of about 33 columns. Sort is all lowest to highest.

The code I have so far is:

Sub Macro1()
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("L2") _
, Order2:=xlAscending, Key3:=Range("P2") _
, Order3:=xlAscending, Key4:=Range("X2") _
, Order4:=xlAscending, Key5:=Range("Y2") _
, Order5:=xlAscending
End Sub

However, I get an error "runtime error 1004 - method range of object global failed".

View 1 Replies View Related

Excel 2010 :: Add Colour To One Cell By Selecting The Other?

Apr 29, 2013

Im creating some labels for users to be able to enter data with ease. i want to add color to one cell by selecting the other. example:

if i put a (date) in cell B1 i would like cell A1 (Day) to colored once a date is in cell B1

Note: I am using excel 2010.

View 2 Replies View Related

Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432

View 3 Replies View Related

Excel 2010 :: Selecting Or Activating Cell Within Loop?

Apr 28, 2014

After adding worksheets, data to ranges, etc. I want to step through each worksheet and "Select" or "Activate" a specific cell (Mainly to clear all the range selections).

My code does the job except the second to last part of cell selection/activation.

Win7, Office 2010

View 3 Replies View Related

VBA For Selecting Multiple Nonadjacent Columns

Aug 8, 2009

in a vba module I currently have this

View 2 Replies View Related

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