• 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. Busymanjohn

    Return Next Instance

    Hi Narayan, sorry it's been a few days since I got back to this, the formula works just fine, thank you for that .. a quick question though, if the same part number_location appears more than once in the report ( which does happen ), how can we ensure that the formula returns the correct date...
  2. Busymanjohn

    Return Next Instance

    Hi Narayan, that seems to work fine ,, one question though, in instances where there is no stock moving out ,,, so has no date, the formula returns 00/01/1900 default ,,, can these be returned as blanks instead?
  3. Busymanjohn

    Return Next Instance

    Hi Naresh, file shared ,,,, let me try and explain a little more ,,, the report is pulled from a system and shows the part and location of stock in and out ,,,, a positive number in the quantity field signifies stock in, a negative number signifies stock moving out, I tend to split the report...
  4. Busymanjohn

    Return Next Instance

    Hi Naresh, this works when the parts are one row apart, but if the parts have rows in between then it doesn't quite work.
  5. Busymanjohn

    Return Next Instance

    Hi, looking for some help on the following ,,,, I have 2 reports, which shows me data on when stock arrives and when it moves out ( dates ) and I currently use VLOOKUP to look at the part and location ( concatenated ) to show me when stock moves out and subsequently calculate how many days that...
  6. Busymanjohn

    Product flow in and out

    Hi p45cal,,, this worked for what I need ,, no need to worry about dates at this stage, I use the dates to calculate how many days product has been in the area from current date, I can do that separately. Thanks again, much appreciated.
  7. Busymanjohn

    Product flow in and out

    Hi guys, I have a query with some data from a colleague and I can't seem to find a solution. There is a sample file attached. Basically, there is a set of data that shows product being transferred into an area and a few days or so later that material being transferred back out ( positive qty...
  8. Busymanjohn

    Lookup last value using 2 criteria

    Thanks guys, both solutions worked a treat ..... :)
  9. Busymanjohn

    Lookup last value using 2 criteria

    Hi Guys, I am having problems with a formula, I am trying to lookup a range of cells and return the last value in that range based on 2 criteria ,,, I can return the last value based on 1 criteria using LOOKUP(2,1/(B3:B16=F6),C3:C16), but how do I insert the 2nd criteria ,,,, also looked at...
  10. Busymanjohn

    Insert rows when data set is extended

    Hi Nebu, yeah, I knew it would involve VBA ( i;m not good with VBA ) and had hoped someone would have the answer. Thanks ▬▬▬▬▬▬▬▬▬ Mod edit : if you really knew that better & faster was to post in VBA forum ‼ ▬▬▬▬▬▬▬▬▬ thread moved to appropriate forum !
  11. Busymanjohn

    Insert rows when data set is extended

    Hi Nebu, not quite what i am looking for, i want the table to update ( add a row ) each time the raw data is updated with new info ,,,, i.e. if the raw data has 12 rows of data then have the table auto update to 12 rows of data instead of having to add rows manually each time.
  12. Busymanjohn

    Insert rows when data set is extended

    Hi PCosta87, thanks for the reply, a PT wouldn't be ideal as the pull through has to be in a certain format, with headers and some extra data as this is used as a form for a work order, but nice idea.
  13. Busymanjohn

    Insert rows when data set is extended

    Sample file attached for clarity.
  14. Busymanjohn

    Insert rows when data set is extended

    Hi All, I am looking for a solution to a small problem ( and I am sure I have come across the solution before but cannot find I ). I have a colleague who has a data set in sheet 1 with a pull through of the data set in sheet 2. The problem is that the pull through in sheet 2 is set to 5 rows of...
  15. Busymanjohn

    Occurrence to equal 1

    Hi Hui, I don't see how the formula(s) ... I don't have Power Pivot .... can be used in my data, or I am missing something.
  16. Busymanjohn

    Occurrence to equal 1

    File attached
  17. Busymanjohn

    Occurrence to equal 1

    Hi all, I have a file that shows over 13,000 lines of data, the data is to be used to work out storage space, but that is irrelevant at this point. Some of the data shows that some part numbers have an occurrence of 4, 7, 10 etc etc .... what I want to do is split these rows out ,,,, so if a...
  18. Busymanjohn

    INDEX SMALL with >

    Thanks guys ,, played around with this some more and got it to work ok ,,, much appreciated.
  19. Busymanjohn

    INDEX SMALL with >

    Khalid, that works fine ,,, now, if I had the formula in another sheet, then the COLUMN piece does not work, any ideas?
  20. Busymanjohn

    INDEX SMALL with >

    Sample file attached ,,,, Ashu, I know about the array ( should have mentioned that already ) ,,,, I have highlighted in the attached the data that should be returned, but one line is missing from the result.
  21. Busymanjohn

    INDEX SMALL with >

    Hi all, I am having some trouble with getting the required results from a multiple VLOOKUP ( INDEX, SMALL ) when I input a > sign rather than an = sign ..... i.e. my formula is INDEX(page!$B$1:$U$2671,SMALL(IF(page!$B$1:$B$2671=3,ROW(page!$B$1:$B$2671)),ROW(page!1:1))*1,2) page is the sheet...
  22. Busymanjohn

    COUNT with multiple Criteria

    Hey Paul ,,,, Sorry for the late reply ,,,, been a busy day today ,,,, this works a treat, I wasn't too far away with a sumproduct and countif ,,,,, much appreciated, thanks again :)
  23. Busymanjohn

    COUNT with multiple Criteria

    Thanks for the reply Paul F ... I just posted an update to my request ,, I have tried COUNTIFS ... even a sumproduct with countif ,,,, it's the unique piece that is throwing me.
  24. Busymanjohn

    COUNT with multiple Criteria

    Hi Hui, apologies ,,, my mistake ,, I should have said that I am looking to return how many unique Names are in Col C ,,,,, so H2 result should be 2, H3 should be 2, H4 should be 1 and H5 should be 0
  25. Busymanjohn

    COUNT with multiple Criteria

    Little conundrum .... maybe i ahve been staring at this for too long ,,,, sample file attached .... I need the results in cells H2:H5 to count the number of Names that appear in Col C that match the date in Col B ( example date in H1 ) and the Code type in Col D ,,,, results should be as per...
Back
Top