I'm trying to find an easier way of summing alternate columns without having to pick each cell out individually.
i.e. the example table below I could easily do A2+C2 and B2+D2 - in reality my table is much wider so I'd rather not have to go along and add up 50+ columns.
ABABSum of ASum of B
1234
2141
1222
3112
I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.
Here's what I have as start info and where I want to get to.
Sheet1
A
B
1
Ranges
[Code] ......
Excel 2003
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
I have data in 2 columns on which I need to do vlookup. This range for vlookup increases. I want to name this dynamic range. How do I do it?
View 1 Replies View RelatedI am searching for a formula that will evaluate a criteria that would be entered into a cell. I then need the total from the columns up to the point the criteria is met.
A simple example below. The criteria is in (what would be B1). Since that criteria was entered then the sum of the values in columns B, C, & D would be totaled. Excluding column E, or 516. If the value was changed to July-14, then 516 would be included in the sum.
PeriodJun-14
Apr-14May-14Jun-14Jul-14Total
XYZ 506 509 512 516 1,527
If I have a range of cells, say B1:BA50, how can I find the number of columns that have data entered?
For example, if cells B1, E2, E20, K25, R15, R33, R45, T3, Z44, AA20, AZ16, AZ22 all had data entered, I'd have 8 columns with data.
How can I find the value 8?
This is what I have tried but doesn't work:
I'm trying to name a dynamic range for A2:B10000.... I know how to use the OFFSET function but was wondering to do it using the INDEX function
Both column will always have the same number of used rows.
Another question:
If I have dates in Column A, and values in Column B....what formula do I need to get me the last value (last row,most recent Date)
I am trying to use defined range names in a vlookup function. The target ranges contain non-adjacent columns. The formula wizard gives me an error msg "#value" and the vlookup returns #N/A.
View 2 Replies View Relatedi'm trying to do some analyzing on an excel sheet and was seeing if there was a function that I could use to speed this up.
So in the file...
I want to Group what's in Column A So the 1's together and the 2's together... Once that is selected I want to know what the largest number in Column C is...
So if the function can select 1 in column A then tell me that 10 is the biggest number in Column C....
For example if you want C23:C99 and column H23:H99 as well
Meaning a For each loop would display contents of
C23
H23
C24
H24 etc.
I have a countif formula on another tab collecting data from these cells. I want to define a combined name DecBestFL with cells A1:A14 and G1:G14. Can I do that in Excel 2007?
On the other tab I would use =countif(DecBestFL,G5) G5 = "Y"
I'm in the midst of preparing a Year-To-Date (YTD) simple financial report in MS Excel.
Firstly:
I allow user to enter a number in cell A2.
Secondly:
Cells from A4 - L4 are linked from Sheet1. Cell M4 will give sum of A4:L4. Users are not allowed to change data in cells from A4 - L4. Users are only required to enter a number between 1 to 12 in cell A2. Upon update cell A2, the VBA shall sum the number of columns as entered in cell A2.
For example, if user enter 3 in cell A2 then cell M4 must give sum of A4:C4. If user enter 8 in cell A2 then cell M4 must give sum of A4:H4.
In a sheet where the longest column is A, I want to delete the rows between the last value in Column A and the last value in Column C.
I am trying to hide columns in a range, "P8:ET1087" but it isn't working. After I autofilter a value, every row will be hidden except for the rows where the value is found. This is always 6 rows, won't be more or less.
The 6 cells in every column are the same and contain from 1 to 6:
Text
Text
Date
Number
Text
Date
What I am trying to do is to hide the column if all cells in that column are blank/empty after it's autofiltered. That for the 135 columns, from P to ET.
I was messing around with the following code:
[Code] .....
But it doesn't seem to work.
I set my range as follows :
[Code]....
This gives me two ranges with multiple columns of data, which should work fine.
Then try to gather my data as follows :
[Code] ........
But this DOESN'T work - it seems like SumIfs doesn't work with a range with multiple columns... ?
The code DOES work if the rng2013/rng2014 ranges have only one column of data instead of more...
I have this code to set a copyrange, which is throwing up a 'Wrong number of arguments' error
[Code] .....
Correct syntax for the code?
I am using the following range to copy data from Column A (from rows 3 on the the last row). How can I include Columns B and C into this range also, but still using Column A to find the last row?
[Code] .....
I do hav a sheet in which i do hav some column;s n data
Worktype
Metrics
April
May
June
in work type few rows are hidden which i don't need what i need is i want to populate only comman metrics in other range named metrics i had build a macro for this and that's working fine but the problem is it also reads metrics from hidden rows too. i had tried a loop in which i had placed as hidden column property condition.
I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.
I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.
I have a spreadsheet with data organized into columns in sets of two - the first column is an X value (Pixel number) and the second column is the Y value (pixel density). I'm trying to make a macro that will select the first set of columns, chart it, move the chart to the next sheet, then select the next two columns of data, chart them and move the chart to the next sheet etc. I've gotten as far as having it make the chart and move it, but I can't seem to make it select the next set of columns - I've been trying different things for a couple days now.
Code:
In .Range("A8:CJ8") I want code to delete all columns within that Range that do not have any values in the cells.
View 9 Replies View RelatedI need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.
My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:
=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)
I am trying to create a data wall without using filters and manual drag drop
I have a sheet with 1000 sets of data such as below in 2 columns Name and Score
Name score
Fred 101
Barney 104
Wilma 110
Betty 94
Dino 96
Pebbles 112
Bam Bam 120
What I would like to do is sort the data into another worksheet with score as rows in a range and an automated way to list the names under the columns as names such as below (each range is a different) (not displaying correctly below bu names are stacked under the score ranges they correlate to)
95 - 99 100 - 104 105-109 110-114 115-119 120
Betty Fred Wilma Bam Bam
Dino Barney Pebbles
Is there an easy way to do this using formulae or other? Tried pivot table and it got a bit messy with the only way of doing it being names and data as columns with multiple repeated data.
I have a sheet (sheet2) with up to 60000 rows of text. I want to compare column A and B on sheet2 to a range of text (B1:B50) on sheet1. If column A or B on sheet2 doesn't match any of the range of text on sheet1, I want to hide the row on sheet2.
I'm currently using VBA to import sheet2, but haven't gotten really anywhere with hiding the non matching rows.
I am trying to add a gantt chart feature to a project summary worksheet. Ideally I'd like to search Col C (Start date) and Col D (End date) for all projects, and based on the earliest start date (ESD) in C and latest end date (LED) in D, repeatedly insert columns labeled with the value of the ESD (ie Feb 6, 2011), increment by 7 (1 week), insert the next column with ESD+7 and continue on until reaching LED.
View 4 Replies View RelatedI need making a macro which compares a particular range of columns in one sheet to other sheet in same workbook.
It is like i have two worksheets named "Working Copy" and "Cleaned Data - (Working)". In this I have to compare Columns (A:AG) from Working Copy to columns (B:BH) of Cleaned Data - (Working). and if there is any difference between any cell then it should be highlighted with a colour in both the worksheets.
I wanted to lookup value A5, and return all columns in the range A1:D5, I could use the formula =vlookup(A5,$A$1:$D$5,2,false) and copy this formula 3 times changing the third parameter 2, to 3 , 4 and 5. i.e 4 VLOOKUP formulas.
I could, instead, convert the VLOOKUP into an array and enter it CSE i.e =vlookup(A5,$A$1:$D$5,{2,3,4,5},false). This returns the right answer but "appears" to have only used 1 VLOOKUP.
What if this was extended to say 26 columns so instead of "hard coding" the array constants I used =vlookup(A5,$A$1:$D$5,COLUMN(B:AA),false). Is this better than 26 indivisual VLOOKUP's in term of speed?
Anyone know code to count columns in a named range? Got some code that i want to utilize for multiple things so i cant hard code the column count in anymore.
Dim intColumnCount As Integer
Dim X As Integer
x=0
Range("Poo").select
intColumnCount = magic voodoo code
For Each cell In range("Poo")
x = X+1
If ((x / inColumncont) > 2) And ((x / intColumnCount) <= 3) Then 'if on actual row
If IsDate(Cell) Then ' if there is a date in actual
Cell.Interior.ColorIndex = 15 ' shade actual gray
Cell.Offset(-1, 0).Interior.ColorIndex = 15 ' shade prom gray
Cell.Offset(-2, 0).Interior.ColorIndex = 15 ' shade Sched gray
End If
End If
If x = (intColumnCount*3) Then 'went thru Sched, Prom, & Actual
x = 0 ' Reset x
End If
Next
left off a bunch more if statements that formats other stuff but u get the idea... Need Magic Voodoo code replaced with working code
create a Dynamic Named Range for Pivot Tables
the pivote table takes data from A1 to Kx
x is the last row of column K that is not 'blank' ( whenever I insert a new row),
I already know how to define a zone but not know how to to make it "dynamic"
How would I modify this macro to handle a Dynamic Named Range In the example below A27 is the last row in my data set, but its last row number is dynamic.
I'm using this code just after finding the last row in the dataset, but found it breaks if the last row doesn't fall in the A27 range.
I find the last row prior to executing the TextToColumns code.
Range("A65536").End(xlUp).Select
Sub TextToColumns()
Selection.TextToColumns Destination:=Range("A27"), DataType:=xlFixedWidth, _
FieldInfo:= Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
End Sub