LinkBack URL
About LinkBacks
I have attached a file. in Sheet 1 i have made headings and in Sheet2 their is data from which i need to pick up information. Please need support.
Thanks
I have attached a file. in Sheet 1 i have made headings and in Sheet2 their is data from which i need to pick up information. Please need support.
Thanks
Last edited by alvi_gee; 03-03-2018 at 12:53 PM.
There is no attachment.
Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!
- Use concise, accurate thread titles.
- Your post title should describe your problem, not your anticipated solution.
- Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
- Responding to a request to change your thread title by doing so is mandatory.
To change a title go to your first post, click EDIT then Go Advanced and change your title.
No help to be offered, please, until the OP complies with this request.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Done Mam
Last edited by AliGW; 03-03-2018 at 12:57 PM. Reason: Unnecessary quotation removed.
Thank you.
You need to add some expected results as all we have is a list of times: which are "Start", and which are "End" ?
Right - you have given us an empty grid with no indication of your expected results. Please manually fill in what you would expect to see in the first three rows - all columns - and then attach the file again. Thanks!
Updated again please check now.
Last edited by AliGW; 03-03-2018 at 01:20 PM. Reason: Unnecessary quotation removed.
Columns D, E & F are correct. However, you really need to explain where the numbers in column E of your raw data come from. Columns G & H are therefore just a bit of a guess. Your calculations in E are inconsistent and make no sense...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
My cut:
Alan Barb Cain Dana Eric
Entia non sunt multiplicanda sine necessitate
Oops -- I left a UDF in the formulas in G4 and H4.
In G4:
{=MAX(SMALL(IF(Sheet2!$C$4:$C$44 = $A4, Sheet2!$D$4:$D$44), ROW(INDIRECT("2:" & $B4 + 1)))
- SMALL(IF(Sheet2!$C$4:$C$44 = $A4, Sheet2!$D$4:$D$44), ROW(INDIRECT("1:" & $B4))))}
In H4:
{=MIN(SMALL(IF(Sheet2!$C$4:$C$44 = $A4, Sheet2!$D$4:$D$44), ROW(INDIRECT("2:" & $B4 + 1)))
- SMALL(IF(Sheet2!$C$4:$C$44 = $A4, Sheet2!$D$4:$D$44), ROW(INDIRECT("1:" & $B4))))}
Sir, Every thing is OK, But just a small Question, In G & H column, was there data used from another file? I meant do i need to copy all the data of sheet2 to another file? I am saying this becauseOriginally Posted by shg
=MAX(SMALL(IF(Sheet2!$C$4:$C$44 = $A8, Sheet2!$D$4:$D$44), 'C:\Users\AGC\AppData\Roaming\Microsoft\Excel\XLSTART\Personal.xls'!rowvec(2, $B8 + 1))
- SMALL(IF(Sheet2!$C$4:$C$44 = $A8, Sheet2!$D$4:$D$44), 'C:\Users\AGC\AppData\Roaming\Microsoft\Excel\XLSTART\Personal.xls'!rowvec(1, $B8)))
Please support.
Thanks
Use the formulae given in post #10.
Thanks Sir,
Just One more thing, Can i get a Trend column. I have attached the file. can i get trend hours, like how many cakes were made in most hours. If i could get this by Name wise then it would be great.
Last edited by AliGW; 03-23-2018 at 12:50 PM. Reason: Unnecessary quotation removed.
Any One?????????/
reattach the file with expected result and explain how you get the result
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Sir, reattached the file.
Need to check that how many cakes were made hourly by each worker. Thanks
Last edited by AliGW; 03-23-2018 at 12:50 PM. Reason: Unnecessary quotation removed.
j5=COUNTIFS(Sheet2!$C$3:$C$2792,$B5,Sheet2!$D$3:$D$2792,">="&INT($D5)+J$4,Sheet2!$D$3:$D$2792,"<"&INT($D5)+J$4+TIME(1,0,0))
Try this and copy across
Thanks Sir, it works. But one question that in Total Cakes column the formula is "=COUNTIF(Sheet2!$C$3:$C$2792, B5) - 1" why is it - 1 as this results in one lesser . Plz explain.
Last edited by AliGW; 03-23-2018 at 12:50 PM. Reason: Unnecessary quotation removed.
I am also have the same doubt when I saw your excel file
What I think was
for Urban BA Sialkot 5
06-03-2018 11:00:00 is Starting Time of Cake One and
06-03-2018 11:38:00 is ending time of Cake one at the same time Beginning time of Cake two
if Urban BA Sialkot 5 for 5 Times the cakes were 4, if the times were 6 the cakes were 5 and soon.....
This is what I thought
Any solutions??
Last edited by AliGW; 03-23-2018 at 12:49 PM. Reason: Unnecessary quotation removed.
means I did not understand what you want?
How do i remove this - 1 ?
Since Samba's formula matched your manually input results and since you are already summing J5:W5 in X5, perhaps you could populate C5 using: =X5
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Friends, I have attached file. Would like to have following results.
Just like Sheet 1 in which, from I column to Y column i get result how many cakes were made during Clock hours. Same like this I would like to
get results in Sheet 4 from taking data from Sheet 2. That how many cakes were made in 1 min, 2 min , 3 min and so on.
For example
if one cake is made at 1:00 AM the second one is made 1:03 AM then 2nd cake time is 3 min. file is attached. Please check.
Try
Sort Shhet2 by Names then Time
in E4
==IF(C4<>C3,"",D4-D3)
copy down
in Sheet4
in H5
=COUNTIFS(Sheet2!$C:$C,'Sheet 4'!$B5,Sheet2!$E:$E,"<=" & TIME(0,0,30))
Similar formula for other cells
Sir, I have done as you instructed, but the results are not as accepted. As In Time sheet, Column D, Total cakes are shown but as I have calculated
total time cake the total count dose not match (Column AF & AG). Plz check.
Further more in sheet 4, I need to this result.
If a i Put any Value in E4(For example 1, which means 1 min) the below cell against each name should show those cakes which are made within 1 min.
secondly in Column F, if i put any value in F4 (For example 4, which means 4 min) the below cells against each name should show those cakes which took more time then 4 min.
Plz check.
File is attached.
Last edited by AliGW; 03-23-2018 at 12:49 PM. Reason: Unnecessary quotation removed.
You did change these formula to allow for HOURS in AB onward: they only address the MINUTES
=COUNTIFS(Sheet2!$C:$C,$B5,Sheet2!$E:$E,">" & TIME(0,COLUMNS($J:AA),0),Sheet2!$E:$E,"<=" & TIME(0,COLUMNS($J:AB),0))
so change as required.
othing Change sir, I have tried.
Further more in sheet 4, I need to this result.
If a i Put any Value in E4(For example 1, which means 1 min) the below cell against each name should show those cakes which are made within 1 min.
secondly in Column F, if i put any value in F4 (For example 4, which means 4 min) the below cells against each name should show those cakes which took more time then 4 min.
Plz check.
File is attached
Last edited by AliGW; 03-23-2018 at 12:49 PM. Reason: Unnecessary quotation removed.
If I change formulae in AB onward the totals equal those in column C: I don't now why you added 1 in column D
You need to show expected results for first few rows and identify the problem as the formulae are "correct" as far as I can see.
See AB5 in attached.
I will leave you TRY and answer you second requirement and need some expected results.
Alvi-gee - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
I am Attaching a file. which have few issue.
Column c shows that how many cakes have been made totally by Names mentioned in Column B. But which shows one lesser cake. For example According to Sheet "Timing Date" Rural BA Hafizabad Section4 made 24 cakes but in "Time" sheet in shows 23. why ? plz solve.
Secondly Total cakes figure dose not match with figure in column "AE". Because formula from Column H to column AD dose not show all the cakes timing. Plz resolve this issue.
Thanks
Re-read post #27 and change formulae in AA onward.
Also check out formulae in H & I.
Last edited by JohnTopley; 04-06-2018 at 10:19 AM.
Sir, Still unable to pick the mistake, column AE and C column figure is not matching. Plz can you fill one Row and give me the same figure plz.
would appreciate if you help .
Look carefully at the formulae, understand what they do and then try andchange them. I particular in AA onward, look at the TIME function.
in AA (as example)
=COUNTIFS('Timing Date'!$C:$C,$B5,'Timing Date'!$E:$E,">" & TIME(0,COLUMNS($I:Z),0),'Timing Date'!$E:$E,"<=" & TIME(1,0,0))
The same comment applies to columns H & I.
You will learn more by "Having a go" than me (or anyone else) telling you the answers.
Last edited by JohnTopley; 04-09-2018 at 03:59 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks