Suppose I have a column of numbers in an excel sheet. Something like this:
–A B C
1 0
2 0
3 1
4 0
5 0
6 0
7 2
8 0
9 4
10 3
While still keeping this column (column A) intact, I want to have another column ( I’ll say column B) that lists all the non zero numbers. However, if it was a zero, I want it to “shift up” so that the numbers is sorted without a large series of empty cells. For example:
I hope that makes sense and it comes out ok on these forums after any text editing. Anyway, is this sort of thing possible? If It isnt clear what Im asking, please ask so I can clarify!
Oops. I mean, I want to have a way for column B to handle this with a function. So that if the data in column A changes, say I replace a few of the zero’s with a non zero number, I won’t have to copy and paste it over again.
Your list of numbers should be in a column with a heading name in the first row. In the Advanced Filter window, choose the “Copy to another location” radio button and check “Unique records only” at the bottom. Your “list range” is the range of cells containing data (do not include the header row). “Copy to” is the area where you want the numbers copied; in your example I thin kthis is row B. For now leave the “criteria range blank”.
Press “OK” and all unique numbers in your list range will be copied into the “Copy to” area. There is a way to use “criteria range” to filter out zero, but I haven’t been able to get it to work correctly. So there’s half an answer to your problem.
Of course, if you are just looking for all non-zero numbers in the list (including duplicates), then I haven’t been of much help–you’ll have to de-check the “unique records only” box and figure out how to use the criteria range to eliminate zeros. Good luck…