Search for values in Excel 2

[dwcasey]

I have a column of numbers with various numerical values. I want to be able to search within the column of numbers to see how many of the values are over 50.

After that, would I be able to highlight those cells once they were found? Thanks.

 

[carrr]

To get your count, you can use =COUNTIF(A:A,">50")

Then, use COnditional Formatting to highlight cells greater than 50.

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[carrr]

Sorry...posted before I finished.

Go to Format, Conditional Formatting

Apply the condition: If cell value is: greater than > 50 , then format to shade the cell the color you desire.

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[dwcasey]

Thanks. I'll give it a go.

 

[dwcasey]

Any way to pull the first cell that is on the same row as my # of calls > than 50 cell?

I have a row that looks like:

Date/Time #Calls AvailMem CPU Util

03/24/2004 9:30:00 62 68 12.55

If I find where #calls>50, could I pull the whole row and paste it to another location on the same or new sheet?

 

[DrBowes]

Try using Data/Autofilter, going to that column and choosing custom.

You man then choose is greater than 50, then copy and paste the range onto another sheet - only the visible cells will be pasted.

 

[dwcasey]

Bingo. Thank you.

If I recorded those steps in a macro, would it record all the
data/autofilter steps?

 

[KenWright]

You could also use a formula to get this data, eg:-

With your #calls data in say Col B in B4:B100, and the variable you wish to compare to (in this case 50) in say F2

then in any other set of cells (say J1:N1)
=OFFSET($B$1,MIN(IF($B$4:$B$27>$F$2,ROW($B$4:$B$27)))-1,-1)
=OFFSET($B$1,MIN(IF($B$4:$B$27>$F$2,ROW($B$4:$B$27)))-1,0)
=OFFSET($B$1,MIN(IF($B$4:$B$27>$F$2,ROW($B$4:$B$27)))-1,1)
=OFFSET($B$1,MIN(IF($B$4:$B$27>$F$2,ROW($B$4:$B$27)))-1,2)
=OFFSET($B$1,MIN(IF($B$4:$B$27>$F$2,ROW($B$4:$B$27)))-1,3)

The last argument will give the you the values from the cells to the (-1) immediate left, (0) that cell, (1) immediate cell to the right, (2) cell 2 cells to the right etc of the first cell that matches your criteria.

Each formula must be array entered using CTRL+SHIFT+ENTER

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dwcasey]

I'm a little confused on your note. I have a header row that describes what each column represents, so I changed the $B$1 to $B$2. Then I put my value to compare to, 50, in F2.

Then, I put one of the above formulas in J1 and I get 0.00. I am using CTRL+SHIFT+ENTER. Here is a sample of my data:

Time CallsActive Available MBytes % Processor Time
03/24/2004 9:00:00 12 85 99.98
03/24/2004 9:00:30 14 85 8.81
03/24/2004 9:01:00 12 99 8.54
03/24/2004 9:01:30 10 99 7.03
03/24/2004 9:02:00 72 100 6.88
03/24/2004 9:02:30 14 100 8.49
03/24/2004 9:03:00 15 100 10.05
03/24/2004 9:03:30 16 100 7.92

 

[KenWright]

If you use $B$2 then you have to amend the formulas to read -2 instead of -1 near the end else you will get incorrect results.

Leave the $B$1 as is, and from your note I am assuming that your data excl headers starts in row 2, so use the following format of the formulas, changing the last row ie the $B$10 to at least cover all your data

In J1 =OFFSET($B$1,MIN(IF($B$2:$B$10>$F$2,ROW($B$2:$B$10)))-1,-1)
In K1 =OFFSET($B$1,MIN(IF($B$2:$B$10>$F$2,ROW($B$2:$B$10)))-1,0)
In L1 =OFFSET($B$1,MIN(IF($B$2:$B$10>$F$2,ROW($B$2:$B$10)))-1,1)
In M1 =OFFSET($B$1,MIN(IF($B$2:$B$10>$F$2,ROW($B$2:$B$10)))-1,2)
array entering all of them, such that when you look at the formula in the formula bar youw ill see it surrounded by curly braces, eg {=Formula}

If it helps I can send you a sample sheet with it on.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dwcasey]

I've entered the formulas and I'm getting the following data:

38070.38 72 100 6.877284611

I then changed the first field to date and got:

03/24/2004 72 100 6.877284611

Will this only show me one row? My spreadsheet is about 5050 rows long. If I change the range, will I get multiple rows generated from the J1, K1, L1, M1 forumlae?

 

[KenWright]

Well that's what you asked for wasn't it?

The first cell where #calls was greater than 50, which in this case was the 72, and then you wanted that row returned somewhere else on the spreadsheet, which is exactly what those formulas do.

Yes it will only do one row. If you want more then you should consider Data / Filter / Advanced Filter if the data is to be returned elsewhere, or data / Filter / Autofilter if it is to be filtered in situ.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------