limiting results

[JT]

I hope I can explain this properly.

I have a sheet with 100 results but I only want to display the last 4
results on another sheet if there are more than 4 results - so the results
displayed would be 97,98,99,100.

Anyone have a suggestion?

thanks
JT

 

[Ron Rosenfeld]

I hope I can explain this properly.

I have a sheet with 100 results but I only want to display the last 4
results on another sheet if there are more than 4 results - so the results
displayed would be 97,98,99,100.

Anyone have a suggestion?

thanks
JT

You could enter =cell_ref for the last for results, where cell_ref is the sheet and cell where that result is located. Or you could use a formula that figures that out for you, and adjusts as you add more data.
There are relatively easy ways to do this, but without knowing how your data is set up, it is not useful for me to speculate on which solution might be best for you.

 

[JT]

You could enter =cell_ref for the last for results, where cell_ref is the
sheet and cell where that result is located. Or you could use a formula
that figures that out for you, and adjusts as you add more data.
There are relatively easy ways to do this, but without knowing how your
data is set up, it is not useful for me to speculate on which solution
might be best for you.

Your second option would be great, essentially what we have is a field where
text is entered. When the data is displayed it lists everything - if we
only had 4 entries that's what it would display, if we had 500 entries it
shows all of them. We just need to see the last 4 entries or whatever
number we choose to set it at.

 

[Ron Rosenfeld]

Your second option would be great, essentially what we have is a field where
text is entered. When the data is displayed it lists everything - if we
only had 4 entries that's what it would display, if we had 500 entries it
shows all of them. We just need to see the last 4 entries or whatever
number we choose to set it at.

Given the limited information about you data that you are supplying, I can offer the following.

You write you "have a field where text is entered" and you "just need to see the last 4 entries". "A" field implies a single cell, and I find that hard to believe. But if that is the case, you will need to provide the data so that we can see how it is separated, and possibly divise some regular expressions to parse out the last four entries.

If you are entering the data in multiple cells, then any formula to help you depends on the range of cells where you enter the data. But you could set up an OFFSET function where the reference cell is the first cell of data, and the amount of offset is related to the number of entries, derived by a COUNTA formula probably, and perhaps compensating for any blank cells in the range if that is also a possibility.

I am afraid I cannot help you any further, unless, as I have previously written, you choose to share the details of your data and it's set up. If you are not able to figure out how to present that in this setting, you can post a copy of your workbook on some public sharing site (e.g. Skydrive, or others) and then post the link back here. Be sure to make the file accessible.

 

[Cimjet]

Hi JT
If you want the top four all in one cell, do it this way:
=LARGE(A1:A7,1)& ","&LARGE(A1:A7,2)&","& LARGE(A1:A7,3)&","& LARGE(A1:A7,4)
If you want the lowest, change the large for small and the number after the
range will give you the series, first, second etc.
HTH
Cimjet

 

[Cimjet]

I just notice You want it on an other sheet.
=LARGE(Sheet1!A1:A100,1)&
","&LARGE(Sheet1!A1:A100,2)&","&LARGE(Sheet1!A1:A100,3)&","&LARGE(Sheet1!A1:A100,4)
You need to adjust the range to your needs also.
HTH
Cimjet

 

[JT]

Hi Cimjet,

Your formula works pretty close to what I was looking for. The only thing
is the cell with the data (which is feedback) is numbered automatically when
data is entered. I can't send the information but maybe this example will
explain what I am trying to accomplish:


In the field I could type: The sky is blue most of the time but not at
night......... when I save, it automatically gets numbered (which is
hidden) - if it was the first feedback it would be listed 1 if it was the
30th comment it would be given the number 30 and so on. So what I need is
your formula with the feedback associated with the number - so I have a
column A which is numerical and column B with the text associated with
column A.

 

[Ron Rosenfeld]

In the field I could type: The sky is blue most of the time but not at
night......... when I save, it automatically gets numbered (which is
hidden) - if it was the first feedback it would be listed 1 if it was the
30th comment it would be given the number 30 and so on. So what I need is
your formula with the feedback associated with the number - so I have a
column A which is numerical and column B with the text associated with
column A.

OK that makes some sense now.

Let's say your data is on Sheet1, and your summary will be on sheet2.

Try the following, on the Summary sheet:

A1: =LARGE(Sheet1!$A:$A,ROWS($1:1))
B1: =INDEX(Sheet1!$B:$B,MATCH(A1,Sheet1!$A:$A,0))

Select A1:B1 and fill down for as many comments as you want.

If you don't have enough comments (e.g. you pull down 4 and only have two comments), you will get an error returned by the formula.

If you have Excel 2007 or later, you can handle it by changing the formulas to:

A1: =IFERROR(LARGE(Sheet1!$A:$A,ROWS($1:1)),"")
B1: =IFERROR(INDEX(Sheet1!$B:$B,MATCH(A1,Sheet1!$A:$A,0)),"")

 

[JT]

I'm a novice at excel, inherited the spreadsheet. What I was trying to
accomplish was to have the results in one cell --- 96 go to the store, 97
come back from the store etc. but have the numbers in a list with the
associated comment beside each number.

 

[Ron Rosenfeld]

I'm a novice at excel, inherited the spreadsheet. What I was trying to
accomplish was to have the results in one cell --- 96 go to the store, 97
come back from the store etc. but have the numbers in a list with the
associated comment beside each number.

As I have written before, given the limited amount of information you have provided, I'm afraid I cannot advise you any further.

I would suggest, again, that you post the spreadsheet on some public site, e.g. SkyDrive, and post a link here. The posting must include a truly representative example of your data, as well as an example of exactly what you want for "results".