|
colating multi rows of data into single rows - no to pivot tables!
UKMAN wrote:
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(INDI RECT(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 What is in B4, C4 and D1? And what does this have to do with the subject of your post? |
colating multi rows of data into single rows - no to pivot tables!
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. -- Regards Roger Govier UKMAN wrote: 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(INDI RECT(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 |
colating multi rows of data into single rows - no to pivot tables!
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 -- Regards Roger Govier Roger Govier wrote: 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. -- Regards Roger Govier UKMAN wrote: 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(INDI RECT(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 |
colating multi rows of data into single rows - no to pivot tab
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 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 "Roger Govier" wrote: 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 -- Regards Roger Govier Roger Govier wrote: 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. -- Regards Roger Govier UKMAN wrote: 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(INDI RECT(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 . |
| All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com