Return cell locations of all cells with numbers

[stilengi]

Hi -

I need an equation that will return the locations of every cell in a column that has a number entered into it. Is there a way to do this?

 

[mikerickson]

How do you want the locations returned?
A comma delimited string of cell addresses?
Do you want all cells individualy listed, like A1, A2, A3, B1, B2, B3, D5 or should areas be grouped together, like A1:B3, D5 ?

Do you want this as a function for the worksheet or for use in VBA?
If the latter, consider

Set rngNumbers as Range

On Error Resume Next
With outerRange
    Set rngNumbers = .SpecialCells(xlCellTypeConstants, xlNumbers)
    Set rngNumbers = .SpecialCells(xlCellTypeFormulas, xlNumbers)
    Set rngNumbers = Application.Union(rngNumbers, .SpecialCells(xlCellTypeConstants, xlNumbers))
End With
On Error Goto 0

 

[stilengi]

Good point. Here is where it can get complicated.

I have data in two sheets. Each row on each sheet has a unique identifier in Column A, so it is easy to create VLOOKUP functions that carry the data over across multiple sheets.


I want to:

1) Identify cells containing numbers in one column on Sheet A
2) Return the unique identifiers of the rows identified on Sheet A (unique identifiers are listed in column A, numbered 1-100)
3) Use a VLOOKUP function (or another function that might be better) to get the value of a cell for each identified row in Sheet B
4) Average all the values returned (these can range from 1-30 rows returned)

Make sense? I can email an example workbook. It almost seems like you would need a recursive function that can average a variable number of returned values.

 

[Jonmo1]

Welcome to the board...

You have a couple methods given already.

My question is Why?
What is the purpose for gathering these cell locations?

What do you intend to DO with them?

It may be easier than you think to find and use cells containing numbers
Without actually returning a list of cell addresses.

 

[stilengi]

See above.

 

[Jonmo1]

Try this formula entered as an array using CTRL + SHIFT + ENTER

=AVERAGE(IF(ISNUMBER(A1:A100),B1:B100))

 

[stilengi]

This equation would work if I was trying to average the data that is located in the cells whose location I am trying to identify. The data in these cells is irrelevant - I am interested in the data located in Sheet B, which is located with the unique identifier taken from Sheet A.

Here's an example:

Essentially, this is what I want - I want to identify all rows with with numbers in column B on Sheet A. I want to find the same rows in Sheet B and average the numbers (column B of Sheet B) of these rows only. In this case, the end result would be the average of 95.4 and 38.4


Sheet A

Excel Workbook
	A	B
1	Unique Identifier	Value
2	1	*
3	2	120
4	3	*
5	4	48
6	5	*
Sheet1

Sheet B

Excel Workbook
	A	B
1	Unique Identifier	Value
2	1	84.5
3	2	95.4
4	3	94.5
5	4	38.4
6	5	56.4
Sheet2

 

[Aladin Akyurek]

This equation would work if I was trying to average the data that is located in the cells whose location I am trying to identify. The data in these cells is irrelevant - I am interested in the data located in Sheet B, which is located with the unique identifier taken from Sheet A.

Here's an example:

Essentially, this is what I want - I want to identify all rows with with numbers in column B on Sheet A. I want to find the same rows in Sheet B and average the numbers (column B of Sheet B) of these rows only. In this case, the end result would be the average of 95.4 and 38.4


Sheet A

Excel Workbook
	A	B
1	Unique Identifier	Value
2	1	*
3	2	120
4	3	*
5	4	48
6	5	*
Sheet1

Sheet B

Excel Workbook
	A	B
1	Unique Identifier	Value
2	1	84.5
3	2	95.4
4	3	94.5
5	4	38.4
6	5	56.4
Sheet2

I've already deleted a post in this thread. Hope this won't undergo the same fate...

Sheet2

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(MATCH(A2:A6,Sheet1!A2:A6,0)),IF(ISNUMBER(Sheet1!B2:B6),B2:B6)))

 

[Jonmo1]

Try this again with CTRL + SHIFT + ENTER

=AVERAGE(IF(ISNUMBER(Sheet1!B1:B100),Sheet2!B1:B100))

 

[stilengi]

Both of these are perfect! Let's introduce a another component to make this more robust. Say I want to sort Sheet B from smallest to largest. Since the MATCH function is based off of relative location, the values returned will be incorrect. Can we introduce a different function (maybe VLOOKUP would work here) that will match the values based on their unique identifier instead of their relative position?


New Sheet B

Excel Workbook
	A	B
1	Unique Identifier	Value
2	4	38.4
3	5	56.4
4	1	84.5
5	3	94.5
6	2	95.4
Sheet2