locksmith55
New Member
- Joined
- Mar 6, 2022
- Messages
- 22
- Office Version
- 365
- Platform
- Windows
Hello,
I am trying to make a formula that can detect consecutive duplicate values in excel. Once this is detected I want it to only return the lowest value for "LOW" and highest values for "HIGH".
Any way this is possible to do with helper colums and formulas?
In the data below I have shown how I want the data to turn out.
I am trying to make a formula that can detect consecutive duplicate values in excel. Once this is detected I want it to only return the lowest value for "LOW" and highest values for "HIGH".
Any way this is possible to do with helper colums and formulas?
In the data below I have shown how I want the data to turn out.
mrexcel problem .xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATA | HOW I WANT IT TO LOOK | |||||||
2 | VALUE | LOW/HIGH | TIME | VALUE | LOW/HIGH | TIME | |||
3 | 3.46 | HIGH | 10:20 AM | 3.46 | HIGH | 10:20 AM | |||
4 | 2.9 | LOW | 11:01 AM | 2.9 | LOW | 11:01 AM | |||
5 | 3.08 | HIGH | 11:12 AM | 3.08 | HIGH | 11:12 AM | |||
6 | 2.77 | LOW | 11:40 AM | 2.64 | LOW | 11:57 AM | |||
7 | 2.64 | LOW | 11:57 AM | 2.98 | HIGH | 12:55 PM | |||
8 | 2.95 | HIGH | 12:39 PM | 2.53 | LOW | 1:35 PM | |||
9 | 2.98 | HIGH | 12:55 PM | 2.89 | HIGH | 2:04 PM | |||
10 | 2.53 | LOW | 1:35 PM | 2.46 | LOW | 2:47 PM | |||
11 | 2.62 | LOW | 2:04 PM | ||||||
12 | 2.89 | HIGH | 2:04 PM | ||||||
13 | 2.68 | LOW | 2:22 PM | ||||||
14 | 2.46 | LOW | 2:47 PM | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:G5 | E2 | =A2 |
E6:G6 | E6 | =A7 |
E7:G8 | E7 | =A9 |
E9:G9 | E9 | =A12 |
E10:G10 | E10 | =A14 |
C3 | C3 | =TIME(10,20,0) |
C4 | C4 | =TIME(11,1,0) |
C5 | C5 | =TIME(11,12,0) |
C6 | C6 | =TIME(11,40,0) |
C7 | C7 | =TIME(11,57,0) |
C8 | C8 | =TIME(12,39,0) |
C9 | C9 | =TIME(12,55,0) |
C10 | C10 | =TIME(13,35,0) |
C11:C12 | C11 | =TIME(14,4,0) |
C13 | C13 | =TIME(14,22,0) |
C14 | C14 | =TIME(14,47,0) |