This has been sending me crazy all day.
Essentially I have two columns - one named category and one named time.
There are three categories. These are certain words however for this purpose I'll call these A, B, C (although this final category is always expressed as "C:[sub category]" which tends to make things slightly more difficult).
I need a formula which will search each row of the category column for a specific word such as "A" or "C". If the cell contains this word I need it to copy the corresponding time into a different cell in a separate table.
The purpose being that I need to sort my data from this format:
Category A - Time
Category B - Time
Category A - Time
Category C - Time
to this format:
Category A Category B Category C
Time Time
Time Time
Time Time
Time Time
A simple If statement such as =IF(A1="A",B2,"") doesn't work as it only searches one cell; if A1 didn't contain the word A then the cell would be left empty. I need this statement to repeat until the word A is found and then paste the corresponding time in the next row of the new table and then keep doing this until there are no more instances of A left.
Normally I'd assume that I could use a loop function, however as this is for use on a work computer which doesn't allow visual basic or macros, I think this may not be possible.
Anyone have any ideas? At 5pm on a Friday I can honestly no longer think logically I'm afraid.
Essentially I have two columns - one named category and one named time.
There are three categories. These are certain words however for this purpose I'll call these A, B, C (although this final category is always expressed as "C:[sub category]" which tends to make things slightly more difficult).
I need a formula which will search each row of the category column for a specific word such as "A" or "C". If the cell contains this word I need it to copy the corresponding time into a different cell in a separate table.
The purpose being that I need to sort my data from this format:
Category A - Time
Category B - Time
Category A - Time
Category C - Time
to this format:
Category A Category B Category C
Time Time
Time Time
Time Time
Time Time
A simple If statement such as =IF(A1="A",B2,"") doesn't work as it only searches one cell; if A1 didn't contain the word A then the cell would be left empty. I need this statement to repeat until the word A is found and then paste the corresponding time in the next row of the new table and then keep doing this until there are no more instances of A left.
Normally I'd assume that I could use a loop function, however as this is for use on a work computer which doesn't allow visual basic or macros, I think this may not be possible.
Anyone have any ideas? At 5pm on a Friday I can honestly no longer think logically I'm afraid.
