colating multi rows of data into single rows - no to pivot tables!

  • Thread starter Thread starter UKMAN
  • Start date Start date
U

UKMAN

the formula below allows me to state the number of days by month i.e. 10 days
from 23rd April means 8 in April and 2 in May.
I have 2 issues with it though.

{=IF($H$16:$H$30=A4,SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B4&":"&$C4)),ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))),)}
Issue 1:
H16:h30 is a list of names, A4 is the name of the student
Using the formula I can divide the dates over the months but for some
reason when I try to match a name (a4) against the list (h16:h30) I only ever
match the first name i.e. what is in h16??? other wise I get a "FALSE"
statement.

Issue 2: How can I amend the formula to only show the "working days" in the
return value i.e. 10 days from 23rd April means 6 in April and 4 in May.

many thanks

(e-mail address removed)
 
UKMAN said:
the formula below allows me to state the number of days by month i.e. 10 days
from 23rd April means 8 in April and 2 in May.
I have 2 issues with it though.

{=IF($H$16:$H$30=A4,SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B4&":"&$C4)),ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))),)}
Issue 1:
H16:h30 is a list of names, A4 is the name of the student
Using the formula I can divide the dates over the months but for some
reason when I try to match a name (a4) against the list (h16:h30) I only ever
match the first name i.e. what is in h16??? other wise I get a "FALSE"
statement.

Issue 2: How can I amend the formula to only show the "working days" in the
return value i.e. 10 days from 23rd April means 6 in April and 4 in May.

many thanks

(e-mail address removed)
Click to expand...

What is in B4, C4 and D1? And what does this have to do with the subject of
your post?
 
Hi

You are not going to get your answer that way.
Continuing from the original layout and the original formula I gave you
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B2&":"&$C2)),
ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0)))
generate the table of data.

On a separate sheet (my data as above was on Sheet3), create a unique
list of names in A2 downward.
In B1:M1 enter dates for each month Jan through Dec
In B2 enter
=SUMPRODUCT((Sheet3!$A$2:$A$10=$A2)*(MONTH(B$1)=
MONTH(Sheet3!$D$1:$O$1))*Sheet3!$D$2:$O$10)

and this will give the totals by employee for each month.

Making the calculation work for only weekdays will take a little more
thought.
I will come back to you on this.
 
My apologies, that formula should have been

=SUMPRODUCT((Sheet3!$A$2:$A$10=$A2)*(MONTH(B$1)=
MONTH(Sheet3!$D$1:$M$1))*Sheet3!$D$2:$M$10)

as column M would be December, not column O
 
Roger,

thanks for input. I did try sending you part of the spreadsheet so you could
see the design etc but got a bounce back on your email address. :( My layout
does have seperate areas which your formula collates the data from simula to
what you suggest.

If you want to email my (e-mail address removed) address I will send you a copy
which may make it easier.

In mean time I will see if I can use your new formula :)

many thanks as ever.

UKMAN
 
Back
Top