• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. Swapnil

    Insert multiple rows at change in value

    Sub Count_Change() Dim I As Integer Dim StrtRow As Integer Dim rng As Range StrtRow = 6 Do While ActiveCell.Value <> "" If ActiveCell.Offset(1, 0).Value <> ActiveCell.Value Then Set rng = Rows(ActiveCell.Row) For I = 1 To 2 rng.Offset(1).Insert xlDown...
  2. Swapnil

    How to derive hours from date and time value

    Hi Sachin, May be below formula can address your problem , Considering Cell A1 has the Date+Time value , Midnight will give result as Zero while Noon will give result as 12 =IF(TEXT(A1,"hh")+0<13,(TEXT(A1,"hh")+0),(TEXT(A1,"hh")-12)) I hope this helps
  3. Swapnil

    Transpose Cells and create a new Table

    you are welcome
  4. Swapnil

    Transpose Cells and create a new Table

    Sorry Have uploaded old file. PFB the updated file.
  5. Swapnil

    Transpose Cells and create a new Table

    Hi, Please see attached file. I have managed to come up with the required result. Still I feel this is not the best way to achieve the desired result. Looking forward for more efficient inputs that can better the solution. the only catch here is that on topmost left cell we need to provide...
  6. Swapnil

    Lookup with mutiple conditions

    Hi, It seems there is a mismatch in the output which you desire and the data you have provided ( Account not matching , 3000 entry shown under 450060 & mar'15 , is actually for account 450061 in your base data. ). Also months in base data are mixed of text and dates. For desired output it...
  7. Swapnil

    multiple if criteria with 3 results option

    Hi, Please try =IF(AND(D2="Pending with UIDAI(DQC)",E2=""),"Pending with customer",IF(E2="","Pending with team","Closed")) Attaching file for your ready reference.
  8. Swapnil

    Travel Date Mapping with Billing Date

    Thanks Luke, I intently excluded one date as I did not wanted to include the same for my calculation purpose as a nature of the analytics. By d way If there a way through which I can address my problem through mix of existing (Inbuilt) functions?? ( Just wanted to know ).
  9. Swapnil

    Travel Date Mapping with Billing Date

    Greetings !!!!!! This is a recent problem I came across. I have two sheets here , One containing employee bill details ( along with desired Output ) named " Employee Billing Data". While the other have their travel details along with Geography in which they traveled named "Travel Data". I need...
  10. Swapnil

    add 1 to the duplicated value

    @Luke M Sorry to interrupt , But I think formula in B1 should be =A1+COUNTIF(A$1:A1,A1)-1 Please correct If my understanding is wrong
  11. Swapnil

    Many many returns of the day Luke Sir !

    Hi Luke ... wish you many many happy returns of the day :)
  12. Swapnil

    OUESTION ONE

    @LOKESH KUMAR Below is the non VBA Array solution. I have converted your data range in to table to take care of any additional record creation.
  13. Swapnil

    #Value! error in formula

    You are getting error because you cant add blank ( "") with Zero ... Try to use following =IFERROR(IF(ISBLANK([Date1]),"",[Date1]) + IF(ISNUMBER([Inv. Payment Terms]), [Inv. Payment Terms],0),"")
  14. Swapnil

    Weighted average - time spent

    Can you please check.... I have populated the formula which can give you weighted average. Please confirm whether this meets your expectation.
  15. Swapnil

    Basic Problem involving Sum

    Abhiroop , I have used simple conditional formatting & sum formula to derived at following sheet. Request you to please see and suggest this meets your requirement or not.
  16. Swapnil

    Automating time difference

    Below is the solution with the help of Array formula ,
  17. Swapnil

    Formula to Count from a table based on maximum variable number

    Just have one concern , In Result amount column , formula should be =IFERROR(IF(C5=F5,D5,D5*F5/C5),0) , This will give you proportional cost. In your formula , If I look at Row 19 , Cost for 3 People is less than what is there for 1 people. Please correct me If my understanding is wrong.
  18. Swapnil

    count the number of times a particular situation has value

    Thank Somendra , He gave you the better solution to problem:)
  19. Swapnil

    count the number of times a particular situation has value

    Was jus @ somendra We may miss out blank cells and might end up counting the same , Just a bit modification to formula can take care of such omissions. =COUNTIFS($A$2:$A$7,A10,$B$2:$B$7,"<>0",$B$2:$B$7,"<>")
  20. Swapnil

    Formula to Count from a table based on maximum variable number

    @rajkenya1 Can you please check and confirm whether this is what you wanted. I have put solution in sheet 2.
  21. Swapnil

    How to count blank cells in filtered condition

    Dear Arup , Sorry to interrupt , But formula mentioned by Mike works on different sheets of the same workbook as well. Only change you need to do is to append sheet name in you reference. i.e. =SUBTOTAL(3,Sheet1!$A$2:$A$54)-SUBTOTAL(3,Sheet1!$B$2:$B$54)
  22. Swapnil

    I want to get % of one item in pivot table ( % Of Total)

    Same procedure ... Please see the attached file
  23. Swapnil

    question

    Please check
Back
Top