# Finding Numbers Of Rows And Columns?

May 5, 2013How I can know the numbers of rows and columns in a excel sheet?

View 5 RepliesHow I can know the numbers of rows and columns in a excel sheet?

View 5 RepliesADVERTISEMENT

I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.

If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.

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.

I 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

I am trying to find some number which i have from a list of numbers available in various rows, for eg

11

7

11

13

17

i am tryng to find 11,13,14,15,16.... 38 from columns given i.e. 7,11,13,17....

I have named ranges and I want to get the total number of columns/rows or the last cell so I can index relative off off it to get at a value of an individual cell. No I dont want to go definning all names for this specific location, I want the flexibility to relative address it from the row/column or last cell of the named range. I looked at the column and columns props. The first gives a number that makes no sense and not a total of all columns and the last gives back a Range of columns, wonderfully recursive.

View 7 Replies View RelatedI have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below

a b c d

1 Tom 333

2 Sam 22

3 Sam 22

4 John 5

5 Sam 22

6 Sam 1

7 Tom 3

8 Tom 333

So the answer would be

Tom = 2

Sam = 2

John = 1

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:

A B C D E

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

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 RelatedIs there a function in excel that can look at an array of values and find the two values that equal a number.

I have a number 159. I have an array of 100 numbers but only two of them added together can equal 159. Rather than sort through every possible combination is there a function in excel that can do that?

So I have multiple columns of numerical data,

I have to find the sum of all numbers between 10 and 40 within a column, NOT cell 10 to 40 but the actual number 10 and 40.

How do I do that?

Is it SUMIF? I cannt seem to grasp this.

I have another post here on this forum, but I'm afaid the formula is getting so complex that nobody is able to fully understand what I want. Instead I want to find a value between two numbers and add it to some IF sentences. It will do what I want, even if it's not that elegant.

I've looked at the SUMIF function but it did not do exactly what I wanted. It finds a number or adds numbers only if they are in range it seems.

What I want is the following:

Return sum between 500 and 1000 in one cell.

Create a form where i would enter 2 numbers into 2 seperate boxes, and those numbers would be highlighted/bolded/shown in red from a list of 30 rows of numbers x 20 numbers in each row. these 2 numbers will not be the same all the time, below is just for an example

ie. number: ... (5) number: ... (59)

1 28 19 20 5 9 80 72 12 38 50 53 59 83 ...

8 4 38 29 11 39 27 38 3 49 59 80 13 12 49 4 5 ...

and so on....

....

So picture something like that, but with 30 rows of numbers x 20 numbers in each row.

I have a large list of numbers in two columns. I need to know what numbers are in column A but don't appear in column B. For example if the numbers are 1 2 and 3 in column A but column B only has 1 and 2 I need to know that 3 is missing.

the only thing I can think of is doing =IF(A1=B1,"TRUE","FALSE") however typing this a thousand times would not be practical.

Is there a macro that can check column A against column B and tell me which ones are missing?

Find smallest numbers in the range ignoring zeros.

I want it returned as value "1", all others - as value "0".

Example: range A1:J1 contains - 5,0,7,3,6,5,8,0,9,3

desirable result in the range A2:J2 - 0,0,0,1,0,0,0,0,0,1

I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:

Function NumbersInString(Word As String) As Integer

Dim i As Integer

Dim FirstNumberInString As Integer, SecondNumberInString As Integer

For i = 1 To Len(Word)

If IsNumeric(Mid(Word, i, 1)) Then

FirstNumberInString = Mid(Word, i, 1)

If IsNumeric(Mid(Word, i + 1, 1)) = False Then

Exit Function

Else

SecondNumberInString = Mid(Word, i + 1, 1)

End If

End If

Next

NumbersInString = FirstNumberInString & SecondNumberInString

End Function

I am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?

View 3 Replies View RelatedI am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.

I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.

I do a lot of work in excel to do with accounts and this often needs checking against sage. When the invoices/petty cash sheets are put into sage the total amount is put in, but in my spreadsheets I need to split the reciepts. So I was wondering if there was a formula/VBA code, that if I only knew the total of the invoice would find which cells added up to this total?

View 4 Replies View RelatedIn a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.

for example

in column A i could have "Hello how are you 01237232 I am fine"

I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.

The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.

How I an achieve this?

I have an excel file which contains two sheets. In one sheet there are some numbers in a single column. In the other sheet again there are certain numbers span across rows and columns (a number may be duplicated). I need to check whether there are numbers which exist in both the sheets. If such a number is found it may be marked (say with a color) in both the sheets.

View 2 Replies View RelatedI have got the following issue. I have got a large list of values in a column. I need to detect the the ones which are in non-consecutive order and display the difference in single numbers. For example:

1 fine

2 fine

3 fine

7 - 4,5,6

10 - 8,9

In other words I need to find the missing values and get them displayed.

I am building an inventory simulation and have run into a problem. What i want is, when i change a number in cell H4, i want excel to find the sum of C25 and the cells "H4" up. If H4 is 5 then i need the sum of C20:C25...if H4 is 10 i need C15:C25. Does anyone have any thoughts on how to do this? I have attached a sample sheet to make it more clear.

View 2 Replies View RelatedHow can I find prime numbers in a range and highlight it if a number in a cell is a prime.

View 9 Replies View RelatedI am looking for a formulas to first find the middle number in a column of numbers eg 1,2,3,4,5 3 is the middle (similar to median) thats where the calculations start...

it then assigns values of minus to the numbers above the middle and plus values to the numbers below the middle

1 -50

2 -50

3 0

4 +50

5 +50

now when it comes to even numbers eg 1,2,3,4,5,6 if i use median it divide 3 & 4 and comes up with 3.5 ........ i want it to recognize 3 and 4 as the middle numbers

and assign plus and minuses above and below the middle numbers

1 -50

2 -50

3 -25

4 +25

5 +50

6 + 50

I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

Using Office 2010.

I am trying to find duplicate numbers in sets but so far I can only highlight the ones that are in exact order. I need to find each set that has the same numbers, in any order. Example..

I will provide an example of sets of 3. But I get 3, 4 usually but sometimes 5 or 6.

I get them from different people.

Person A- 234, 569, 498, 849, 848,343,567,347 etc...

Person B- 432, 596, 677, 566, 565,433, 455 etc..

Now I need to find each set that has the same numbers, any order. Like 234 from A and 432 from B would be the same, so I would need to highlight them 2 sets. But I can not figure out how to do this. For Excel to highlight it they have to be 234 and 234. Does not recognize same numbers, different order.

I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

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