Hey all,
I've been working with excel for many years in a professional environment to process data when it is normally done by hand. I've had a particular problem that has made my life difficult on multiple occasions; I'm sure someone has found a way to solve this problem.
I've got a worksheet that has 2 columns with very long ranges, like A3-A3000, B3-B3000. I've got logical statements that return a Date-Time-Group, from a data worksheet, in column A if they meet a critera, else they return a null or zero. Column A is chronologically listed on my data worksheet so returning the lowest non-zero time (lowest row #) is easy with the MIN(A3:A3000) function. But...
It gets tricky when you look at column B. It also has a logical statement that will always return a text value, based upon the same criteria that the A column uses. On this one, the MIN() function is not usable as the range is both alpha numeric and i only want the topmost value (lowest row #) that is a non-zero, which will never be the minimum value.
here is an example of what sort of data I'm working with:
----A------B
2--DTG---VALUE
3
4-20:15--D43D2
5-20:13--A25D6
6
7-20:10--Z28R5
8
9-20:07--M28T3
Desired results:
---A------B
1-20:15--D43D2
As you can see, i've got my two columns. When processing data, these columns are different each time so I need the top DTG and top VALUEs of the two ranges, any ideas?
I had used a filter that removed blanks with an expanded selection, but i had to refresh it every time I put in a new data worksheet, and I also had to copy and paste every set of DTG and VALUE over to my summary page. If i could have thoes top DTG and VALUEs on stationary cells, then i can simply link them onto my summary page.
Also: ive been running into issues where i can copy formulas with the drag or copy/paste, but if i edit any of the formula, it shows as plain text (IE: "=1+3" instead of "4") instead of a formula, never had that issue before but it's driving me crazy. Any idea why excel is doing that?
Thanks,
Zane C.
I've been working with excel for many years in a professional environment to process data when it is normally done by hand. I've had a particular problem that has made my life difficult on multiple occasions; I'm sure someone has found a way to solve this problem.
I've got a worksheet that has 2 columns with very long ranges, like A3-A3000, B3-B3000. I've got logical statements that return a Date-Time-Group, from a data worksheet, in column A if they meet a critera, else they return a null or zero. Column A is chronologically listed on my data worksheet so returning the lowest non-zero time (lowest row #) is easy with the MIN(A3:A3000) function. But...
It gets tricky when you look at column B. It also has a logical statement that will always return a text value, based upon the same criteria that the A column uses. On this one, the MIN() function is not usable as the range is both alpha numeric and i only want the topmost value (lowest row #) that is a non-zero, which will never be the minimum value.
here is an example of what sort of data I'm working with:
----A------B
2--DTG---VALUE
3
4-20:15--D43D2
5-20:13--A25D6
6
7-20:10--Z28R5
8
9-20:07--M28T3
Desired results:
---A------B
1-20:15--D43D2
As you can see, i've got my two columns. When processing data, these columns are different each time so I need the top DTG and top VALUEs of the two ranges, any ideas?
I had used a filter that removed blanks with an expanded selection, but i had to refresh it every time I put in a new data worksheet, and I also had to copy and paste every set of DTG and VALUE over to my summary page. If i could have thoes top DTG and VALUEs on stationary cells, then i can simply link them onto my summary page.
Also: ive been running into issues where i can copy formulas with the drag or copy/paste, but if i edit any of the formula, it shows as plain text (IE: "=1+3" instead of "4") instead of a formula, never had that issue before but it's driving me crazy. Any idea why excel is doing that?
Thanks,
Zane C.
Last edited:
