Given that a worksheet has frozen rows and/or columns, how do you programmatically determine what they are?
There are plenty of examples around that tell you how to create frozen rows/columns by setting ActiveWindow.FreezePanes = True but, given this is only a Boolean value, reading it does not tell me where the freeze is.
How I can know the numbers of rows and columns in a excel sheet?
View 5 Replies View RelatedI have a code in software that exports results to excel. But it stores the numbers associated with indices as text. I need to change all of them to numbers. So I have written the following code, that search through the folder, converts text to numbers and colses the file.
Sub ConvertText2NumberFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C: ...analysis"
[Code]....
The problem is it takes time becuase it is going through A LOT OF cells. I only need at most the first 4 columns and the first 2 rows. So I wanted to use the union. I wrote the following:
Set IndexColumns = bk.ActiveSheet.Range(Columns("A") & ":" & Columns("A").SpecialCells(xlLastCell))
Set IndexRows = bk.ActiveSheet.Range(Rows("1") & ":" & Rows("1").SpecialCells(xlLastCell))
Application.Union(Range("IndexColumns"), Range("IndexRows")).Select
Which doesn't work! Morover it is only for one column and one row.
NOTE1 : Like any matrix, the intersection of index columns and index rows is blank!
NOTE2 : The lenght of indices is different from one file to another
I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.
My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.
I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
Excel 2000. Windows 98. I have a workbook w/33 worksheets for a business I have.
All worksheets formulas have quit working. I opened Tools>Protection, but none are protected. What must I do to correct this and get this w/b back into operation?
I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:
=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.
So, keep cell A clear untill something is written in cell B. This works fine.
But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.
I'd like to be able to search a spreadsheet for a specific word or series of characters. Once all of the ROWS that contain the characters are found, I'd like to be able to have another spreadsheet with ONLY those rows (all columns).
View 8 Replies View Relatedmaxvalue = Application.WorksheetFunction.Max(Range("D:D", "A:A", "G:G"))
This gives me 'Compile Error:Wrong number of argruments or invalid property assignment'.
I am using the following code to find an emtyRow, but I actually need to find an emptyRow and test all the cells of the rows from A thru O to insure that there is nothing in any of the next emptyRow. How would I change the code?
VB:
Private Sub SubmitButton_Click()
Dim emptyrow As Long
'Make sheet1 active
Sheets(1).Activate
[Code] ......
If a number in cell M1 is also anywhere in column A (column A has unique numbers), then cell W1 (date) should equal the content of cell V (also date) in the row that that number appears in column A. If M1 is blank (or there is no match in column A), then W1 should equal V1.
In W1, I currently have...
I'm trying to condense my email lists in order to stop people receiving the same email having signed up to several lists. How do I compare 5 different columns to find email addresses which appear in more than one...
View 1 Replies View RelatedI need to find the which two columns that give the greatest combination.
Row 2 shows some possible combinations, a total of combinations is 21.
Attached File : test.151515.xlsx
This should be an easy one, it has slipped my mind.
I have columns A and B, I want to find any names that appear in both column A and column B. I want them to show in a certain color.
I currently have two tabs. Tab A (Error Report) spit out a bunch of records that I need to find in the raw data/remove them. The value in column C in Tab A refers to Column A in Tab B (Data), and Column D in Tab A matches with Column N in Tab B, and Column E in tab A refers to Column C in tab B.
I want to know how I can find the records that match those 3 columns in the First Error Report Tab as I need to find those records and delete them as they are, as you can see, errors!
I have a spreadsheet with two columns:
The first one will always have a 'W' or a 'E'.
The second one will have a three digit code. Like this:
W 507
W 507
W 507
E 504
E 504
W 505
W 505
W 504
W 504
Is it possible to have output that show how many of each combination there is? Like this(for the example above):
W 504 2
W 505 2
W 507 3
E 504 2
Hi I have a process in which I have to create a selection without bias. There are 9 categories in this selection process. The 1st and 2nd category already have code and they work differently from the rest. For the 3rd through the 8th categories I have to choose one from the 3rd category, then one from the 4th, etc. After the 8th category I start back at the 3rd and go to the 4th, ect. It ends when cell A1 reaches 30%.
Its a little difficult to explain but the process is simple enough to understand once you see it. I have attached an example of the process. I have also started the code and left explanations within the code.
Could someone please help as I need to get the totals and whether its negative or positive balance.
To be more specific in a workbook in sheet1 I have Client data columns A,B & C in A,B,& C should be match with the companys data in the next sheet.
If this both sheets sheet1 & sheet2 and columns A,B,& C is matched then in column D in both the sheets should be Matched and Column E should be 0 in both the sheets.
And,The problem starts when both the sheets are not matched if the Column A,B,C is not matched in Column D it should show as "NOT Matched " and difference in Column E in both the sheets.It may be positive figure in one sheet and Negative figure in another sheet.
Please advise If any Forumulae which can help me to sort this out or should I need to go for an Macro.
Please find an sample sheet is attahced.
I have a column with text + string (e.g., T1000, T1001, etc.)
I have another column that's the same, but sometimes this column has new codes.
Right now I have to "eye" it side by side to see if there are any new codes, but I'm sure there's a better way to do it. I'm thinking of asking Excel to compare the columns and if they aren't equal, then it would be necessary for me to eye it. If they are equal, then I dont have to bother because there are no new codes.
A
B
C
D
E
[Code]...
In the example above I am trying to look up a value from columns C-E. I need to be able to search/index using 2 criteria to figure out which row to match with the given column. for example: If I want to know the invoice qty. for R&D for Jan-2012, so the returned value would be 13. I have tried several different combinations of match and index to get this to work but have had no success. Ultimately what I want to do is have a drop down for the month and year that our VP can select and it will give him the given values for that month.
I have 2 pretty large zip code lists, we will call them columns A and B. I know that I have some from A that match to zips in B, but I have alot more zips in B that don't match to column A. I need to identify all of the zips in B that do not match a zip in column A.
View 9 Replies View RelatedI have a little problem - as you probably guessed.
I have a spreadsheet in which i need to find the value of the last used cell in a row.
e.g spread sheet uses columns "a" to "l" and rows "1" to "75".
Over time rows are filled in with text ("tom" "dick" "harry") from a to b to c, the most recent being to the right but the rows can move at different paces.
I want to count how many many times each value has come up most recently.
I am trying to delete columns or clear their contents after I meet a certain criteria in row 4. For example: I have one " Total" in row 4 that can be in any column. I wanna be able to delete whatever comes after "Total" in any row or column. Is there a macro that can do that?.......
View 2 Replies View RelatedI'm using multiple data tables to run sensitivity analyses on a very large model. At the end of the analysis, I've got a matrix that tells me which scenarios are TRUE or FALSE (based on multiple criteria). I'm looking for an elegant way to extract the combinations that give TRUE.
Column1
Column2
Row1
FALSE
TRUE
Row2
TRUE
FALSE
Desired Output
True Scenarios:
R1, C2
R2, C1
I have a file with immunophenotypic markers and their relationship to certain blood cells.
What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.
I have attached the file (I think!)
I have a dataset that looks like below -
Header1 Header2 Data
100 101 20.50
102 19
103 1003.2
101 100 20.50
200 300
102 100 20
200 104 30
Now, what i need to do is for each of the rows in my dataset, I need to find the reverse entries (consider first row where header 1 is 100 and header2 is 101, I need to search the entire dataset to find if I have some data for header1 is 101 and header2 is 100). In this case, I do have such data where header1 is 101 and header2 is 100.
Header1 Header2 Data Reverse Entry Difference
100 101 20.50 20.50 0
102 19 20 -1
103 1003.2 Not found Not found
101 100 20.50 20.50 0
200 300 30 270
102 100 20 19 1
200 101 30 300 -270
let me know if that's possible through excel formulas?
I need a column D with such data and column E for differences. So that, my final output should look like below -
I have a table as follows: [URL] - note: cells containing times and temperatures are numeric values, I've used custom formatting to add " mins" and "°C" on the end
In one cell, a user can input a time period (in mins), and in another cell, I would like to output the start and end columns (in letter/s) for the chosen time.
For example, in the link above, if the value inputted '30' then the start column is 'C' and the end column is 'G'. If the user had inputted '60', then the results would be 'H' and 'K', etc.
I am able to find the start column with the following equation:
=SUBSTITUTE(ADDRESS(1,MATCH(VALUE,C2:P2,0)+2,4),"1","")
I am struggling, however, to find the end column for the chosen time.
If each time had the same number of temperatures then it would be a simple case of adding the number of temperatures per time, minus 1, after the "+2".
My first idea was to copy the above function, but to change the "0" in the MATCH function to "-1", in order to find the next greatest time value. However, it appears that using "-1" in the MATCH function only works if the range is in descending order - my times are always in ascending order.
I have a further problem in that the time periods are not always the same values (i.e. it won't always be 30-60-90-120, it could for example be 30-45-60-75). So as far as I know, there is no way in advance of knowing the next time period up (and then subsequently taking one column to the left).
One possible workaround I have considered involves the following:
- Add a new row between rows 2 and 3, which has the time values in each cell (i.e. from column C, values of 30-30-30-30-30-60-60-60-60-90-90-90-120-120) - effectively duplicating the time row, but not merging cells
- Use the COUNTIF function to find the number of cells in that row containing the chosen time
- Add this value, minus 1, after the "+2" in the above formula, i.e. =SUBSTITUTE(ADDRESS(1,MATCH(VALUE,C2:P2,0)+2+COUNTIF(C3:P3,"="&VALUE")-1,4),"1","")
I am not sure if Excel is able to do this but basically I am looking to find out which rows have some duplicate values. I have just read this back and it doesn't make a great deal of sense so I have attached an example spreadsheet.
Basically I am looking to find if E1:G1 duplicates further on down the list, hope this makes a bit more sense with the example attached.
Trial Upload.xlsx
For my job I have to take hundreds of codes and compare them to other codes. For example, in column A I'll have 453 codes, in column B I'll have 352 and in column 97. I want to find common codes for all three columns. Sometimes, I'll have just two columns and sometimes it's multiple columns. I have tried a few formulas but nothing works that well. Any formulas or MACRO
View 6 Replies View Relatedhow to find partial match of text in 2 different columns.
For example:
Column A:
Boston
Chicago
Los Angeles
New York
Detroit
Miami
[code].....
I want to be able to create a column that finds all the matching cities from A in B.
