-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Collecting data by date
- Thread starter mucah!t
- Start date
taigovinda
Well-known Member
- Joined
- Mar 28, 2007
- Messages
- 2,639
Try this:
Excel 2003Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
| Excel Workbook | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | J | M | N | |||
| 5 | 1-Jan | 2-Jan | 3-Jan | 4-Jan | 5-Jan | 6-Jan | 7-Jan | 3-Jan | ||||
| 6 | ||||||||||||
| 7 | ||||||||||||
| 8 | JOHN | A | A | A | A | JOHN | ||||||
| 9 | ALEX | B | B | B | B | B | ANDREW | |||||
| 10 | ANDREW | A | A | A | A | |||||||
| 11 | PETER | B | B | B | B | |||||||
| 12 | MATHEW | B | B | B | B | B | ||||||
| 13 | MIKE | D | D | D | D | D | D | |||||
| 14 | WERNER | D | D | D | D | |||||||
| 15 | FERRY | |||||||||||
| 16 | ||||||||||||
Sheet1 | ||||||||||||
#VALUE!
Upvote
0
Thank you taigovinda.
However if I copy the formula down in column N it gives a #NUM! when all occurrences of the character A are already met.
secondly, I have to use a lot of these formulas in my sheet. as it is a array formula it really slowsdown the calculations in my sheet.
On another sheet I make use of a similar formula which is slightly different from the one you gave me. Although it is also a array-formula, because of the "offset"-function in it, it doesn;t really slowdown the calculations on my sheet, neither does it give a #NUM!. I cant however amend it to fit the situtation given in my previous post.
This is the code:
However if I copy the formula down in column N it gives a #NUM! when all occurrences of the character A are already met.
secondly, I have to use a lot of these formulas in my sheet. as it is a array formula it really slowsdown the calculations in my sheet.
On another sheet I make use of a similar formula which is slightly different from the one you gave me. Although it is also a array-formula, because of the "offset"-function in it, it doesn;t really slowdown the calculations on my sheet, neither does it give a #NUM!. I cant however amend it to fit the situtation given in my previous post.
This is the code:
Code:
=INDEX($17:$17;COLUMN($O$1)+LARGE(INDEX(ISNUMBER(MATCH(OFFSET($P$1:$IV$1;MATCH($C$5;$N$3:$N$2000;0)+1;;);$D$5:$D$11;0))*(COLUMN($P$1:$IV$1)-COLUMN($O$1));0);ROWS($5:16)))
Upvote
0
I opened a similar thread on the following forum]
Upvote
0
Ron Coderre
MrExcel MVP
- Joined
- Jan 14, 2009
- Messages
- 2,316
Please see my proposed solution at the link to the other forum.
Upvote
0
Similar threads
- Solved
