I have a large Numbers file (art supply inventory) and when sorted there can be rows upon rows of data for a particular item, say, brushes. Is there a way to skip to the next change in value either with a formula or some active function? In other words, my file is sorted by category (brushes, markers and paper to simplify..) and I want to move to the next or previous change in category – from brush to marker or marker to paint… any thoughts? Thank you for helping
—–
Michael P
MacMost Q&A Forum • View All Forum Questions • Ask a Question
Is There a Way To Skip To the Next Value Change for a Given Column In Numbers?
Comments: 5 Responses to “Is There a Way To Skip To the Next Value Change for a Given Column In Numbers?”
Comments Closed.
Interesting problem. What you really want to do is to search for the next row where the item name is the same as the previous row, but the price is different. Is that right?
So you may have rows 1 to 30 where it is "Brushes" and "$9" and then rows 31 to 156 where it is "Brushes" and "$9.50" then rows 157 to 245 where it is "Brushes" and "$9.97." You want to jump to row 31 and then 157. Is that right?
If so, I would add another column, maybe called "Change." Then put a formula into it that uses the IF function to check to see if the name value is the same as the previous row, but the price value is different than the previous row. For instance, if column A is the name and column B is the price, then the formula in C may look like this:
=IF(AND(A7=A6,B7≠B6),"CHANGE","")
Copy and paste that in every cell in column C and you get the word "CHANGE" in all rows where there is a change.
Now just search (Command+F) for the word "change" and you can jump to each of these rows.
Well, not quite Gary. My file is sorted by category and I want to move from one category to the next without clicking a bunch of times. I don't want to move to a change within the selected category. So, if I'm at row 1 and it is a brush item I want to move to the next category, say "Markers"... I think your formula may work and I will try it... Any other thoughts?
Michael: Actually, that is even simpler. Just look for one condition then, A7<>A6 (the category cell on this row is not equal to the category cell on the previous row). So something like:
=IF(A2≠A1,"NEWCAT","")
Here's a few screenshots of the file...
https://dl.dropboxusercontent.com/u/930328/Brush%20Category.png
https://dl.dropboxusercontent.com/u/930328/Marker%20Category.png
So, I want to move from "Brushes" to "Markers" in column A. Thx again for your help and continued success on the podcast.
Ok, I will try these suggestions probably next week, stay tuned...