Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to parse through the data below and show results like:

Mary notready=483 training=452 break=30 

I have the formulas — what I'm having trouble with is: The names are first, then 7 to 10 rows of data and what I need is some kind of array that will pull 1st Break_100 under that name, even though they are in different rows.

Summary:    08:02:32
() 9/19/2012    
Not_Ready_Default_Reason_Code   00:00:05
Training_3000   07:32:21
Break_1000  00:30:06
daily 9/19/2012 08:02:32
Agent:  08:02:32
Summary:    01:18:33
() 9/19/2012    
Break_1000  00:34:27
Not_Ready_Default_Reason_Code   00:01:37
Personal_4000   00:42:29
daily 9/19/2012 01:18:33
Agent:  01:18:33
share|improve this question
How does G27 relate to A22? Once "tom" is found in A22, do you always want to work with the time that's 5 rows down in column G? If so I'll post an answer with the formula. if not please clarify. – Gimp Sep 20 '12 at 15:14
Tom, please check your formatting. Your data wasn't properly formatted and therefore didn't look right. Take a look at the preview below your post while editing and indent data with Ctrl-K. Thanks! – slhck Sep 20 '12 at 20:43
Its not always 5 rows down that's my problem column a contains the name, column g has data I need, but the rows can change, hope this will clarify – Tom Sep 21 '12 at 13:08

I don’t understand the question (ie “how can you expect to locate what you say you are looking for in the data example provided when the data sample does not contain what you are looking for”) but the following might be a step forward, and it is too long to fit in a comment.

  1. Assumed: You can identify rows with names and Break_1000 entries will never be close together.
  2. Add two columns on the left.
  3. Put =MATCH("Break",$B1:$B10,0) in A1 if that is first Named row.
  4. Filter on Name and copy A1 down as far as required.
  5. Invert filter and add =IF(LEFT(C2,5)="Break","Break","") in C2 (if 2 is first filtered row.
  6. Copy down B2 as far as required.

A horrible kludge but you should now have the relative position of the row commencing “Break” as a value next to the identifiable Name Rows. Whether that is any use to you depends on what at the moment appears rather mysterious!

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.