Return cell locations of all cells with numbers

S

stilengi

New Member
Joined
Jun 21, 2011
Messages
10
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?
 
Sort by date Sort by votes
stilengi said:
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
AB
1Unique IdentifierValue
2438.4
3556.4
4184.5
5394.5
6295.4
Sheet2
Click to expand...

I should have done as such the first time...

Control+shift+enter, not just enter:

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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Aladin Akyurek said:
You are welcome. And thanks for such an interesting question.
Click to expand...

I have added some functionality to my project and now have an additional question. On the Data tab, I have collected all of my raw data. For each ID, I have 1-4 values in columns B-F for each ID, which are labeled for each day of the week. On the Analysis tab, I want to display the lowest number for each ID, IE. 10 - 24, 5 - 36, 22 - 12, 43 - 48, and 21 - 12. You'll notice the IDs are not in the same order on the Analysis tab. I did this to simulate reordering. I intend to reorder both the Data tab and Analysis tab independently after the formulas are entered. What formula can I use on the Analysis tab to show me the lowest number in each of the rows on the Data tab?

Data

*ABCDEF
IDMonTuesWedsThursFri

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]10[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]336[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]120[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]36[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]22[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]96[/TD]

[TD="align: right"]24[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]43[/TD]
[TD="align: right"]48[/TD]

[TD="align: right"]72[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]21[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]720[/TD]

[TD="align: right"]36[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Analysis

*AB
IDMinimum Value
*
*
*
*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:114px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]43[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]5[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
stilengi said:
I have added some functionality to my project and now have an additional question. On the Data tab, I have collected all of my raw data. For each ID, I have 1-4 values in columns B-F for each ID, which are labeled for each day of the week. On the Analysis tab, I want to display the lowest number for each ID, IE. 10 - 24, 5 - 36, 22 - 12, 43 - 48, and 21 - 12. You'll notice the IDs are not in the same order on the Analysis tab. I did this to simulate reordering. I intend to reorder both the Data tab and Analysis tab independently after the formulas are entered. What formula can I use on the Analysis tab to show me the lowest number in each of the rows on the Data tab?

Data

*
A
B
C
D
E
F
ID
Mon
Tues
Weds
Thurs
Fri

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: center"]10
[/TD]

[TD="align: right"]24
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]336
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: right"]120
[/TD]

[TD="align: right"]48
[/TD]
[TD="align: right"]36
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: center"]22
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]72
[/TD]
[TD="align: right"]96
[/TD]

[TD="align: right"]24
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: center"]43
[/TD]
[TD="align: right"]48
[/TD]

[TD="align: right"]72
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: center"]21
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]336
[/TD]
[TD="align: right"]720
[/TD]

[TD="align: right"]36
[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Analysis

*
A
B
ID
Minimum Value
*
*
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: right"]22
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: right"]43
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: right"]10
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: right"]21
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: right"]5
[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

You need to complete Mike's suggestion...

In B2 on Analysis enter and copy down:

=MIN(INDEX(Data!$B$2:$F$6,MATCH($A2,Data!$A$2:$A$6,0),0))
 
Upvote 0
Let's take this another step further. Is there a way to summarize the data by creating a list of days and associated values for each ID? For example, I'd like to select a Name (which is associated with an ID), and then generate a list of Days and the associated value entered into the cell.

Referencing the table below, I would like to select John from a drop down menu, which would generate the following output: Tues - 24, Weds - 48, Thurs - 336 (ignores blanks). The syntax of the output is not important - I just need an easy way to see the summary of the matrixed information. How would I do that?

Thanks again for all your help!



Sheet1

*ABCDEFG
IDNameMonTuesWedsThursFri
John
Dan
Emily
Jose
Alexis

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]336[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]120[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]36[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]22[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]96[/TD]

[TD="align: right"]24[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]43[/TD]

[TD="align: right"]48[/TD]

[TD="align: right"]72[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]21[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]720[/TD]

[TD="align: right"]36[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
stilengi said:
Let's take this another step further. Is there a way to summarize the data by creating a list of days and associated values for each ID? For example, I'd like to select a Name (which is associated with an ID), and then generate a list of Days and the associated value entered into the cell.

Referencing the table below, I would like to select John from a drop down menu, which would generate the following output: Tues - 24, Weds - 48, Thurs - 336 (ignores blanks). The syntax of the output is not important - I just need an easy way to see the summary of the matrixed information. How would I do that?

Thanks again for all your help!



Sheet1

*ABCDEFG
IDNameMonTuesWedsThursFri
John
Dan
Emily
Jose
Alexis

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]336[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]120[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]36[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]22[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]96[/TD]

[TD="align: right"]24[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]43[/TD]

[TD="align: right"]48[/TD]

[TD="align: right"]72[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]21[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]720[/TD]

[TD="align: right"]36[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Sheet1, A:G, houses the data...

Sheet2 houses the processing...

A1: 22 (an ID of interest)

A2, just enter:

=COUNT(INDEX(Sheet1!C2:G6,MATCH(A1,Sheet1!A2:A6,0),0))

B2, just enter:

=VLOOKUP(A1,Sheet1!A2:B6,2,0)

A3, control+shift+enter, not just enter, and copy across:
Code: 
=IF(COLUMNS($A$3:A3)<=$A$2,INDEX(Sheet1!$B$1:$G$1,
    SMALL(IF(ISNUMBER(INDEX(Sheet1!$B$2:$G$6,MATCH($A$1,Sheet1!$A$2:$A$6,0),0)),
      COLUMN(Sheet1!$B$1:$G$1)-COLUMN(Sheet1!$B$1)+1),COLUMNS($A$3:A3))),"")

A4, just enter and copy across:
Code: 
=INDEX(Sheet1!$B$2:$G$6,MATCH($A$1,Sheet1!$A$2:$A$6,0),MATCH(A$3,Sheet1!$B$1:$G$1,0))

Posted from my iPad, I hope without any mistake.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Return where a number is found in a URL string, only if it is found after a certain number of "/" characters
Replies
8
Views
213
felixstraube
F
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
249
henrik2h
H
D
  • Solved
List all non-formula cells
Replies
7
Views
125
Kaper
Kaper
N
  • Solved
Return value determined by multiple criteria
Replies
3
Views
125
smozgur
smozgur
N
  • Question Question
Locate Cell and enter corresponding value into new cell
Replies
3
Views
74
myall_blues
myall_blues

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top