arrange data by criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

My new responsibility at work is to align (sequence) the values from column
C to match column A while using column B as the set criteria (key?). For
example, the value from b2-c2 - 14 would move down two rows to be in
alignment with A4. To continue, the value from b3-c3 - 45 would move down
four rows to be in alignment with A7.
Is there a formula to simplify this process?
A B C
8:00 8:00 56
8:30 9:30 14
9:00 11:00 45
9:30 11:30 24
10:00 1:00 84
10:30 2:00 7
11:00 4:00 65
Thank you for reviewing this situation.
If I can provide any additional information, please let me know.
Jeff
 
Hi!

So what happens with the values that will be displaced by "moving"
"matching" values? Will there be matches for all the values in column C?

It looks like the correlation is the time in column B that matches the time
in column A. So, nothing happens to A1,B1 and C1?

Am I even remotely close? <LOL>

Biff
 
Perhaps one way to play with ..

Assuming data is in Sheet1, A1:C7
8:00 8:00 56
8:30 9:30 14
9:00 11:00 45
9:30 11:30 24
10:00 1:00 84
10:30 2:00 7
11:00 4:00 65
Click to expand...

Use an empty col to the right, say col F?
Put in F1: =TEXT(B1,"hh:mm")
Copy F1 down to F7

In a new Sheet2
-----------------
Put in A1: 8:00, in A2: 8:30
Select A1:A2, fill down to A48
This will list all the 48 half hourly times from 8:00 till 7:30

Put in B1:
=IF(ISNA(MATCH(TEXT($A1,"hh:mm"),Sheet1!$F:$F,0)),"",INDEX(Sheet1!B:B,MATCH(
TEXT($A1,"hh:mm"),Sheet1!$F:$F,0)))

Copy B1 across to C1
Format B1 as time, C1 as general
Select B1:C1 and copy down to C48

Sheet2 will return the data from Sheet1's cols B and C lined up (matched)
nicely against col A (in Sheet2), which seems to be what you're after ..

And if desired, freeze the values in cols B and C
with an in-place copy > paste special > values > ok
 
Max,
The formula you have provided here did solve the data arrange (sort) concern.
Thank you.
Jeff
 
Back
Top