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

    Array formula for sorted & filtered results

    I added a column to your table that displays the row number when valid. Column G displays the list of valid row numbers. Column H and I are the associated dates and values.
  2. Mike86

    Help Please!! how to make this type of analysis?

    Don't forget weight and size of the package. Lots of difference shipping a metric ton versus a pound or a pallet versus a small box.
  3. Mike86

    Not able to create this chart

    Not perfect, but this is close.
  4. Mike86

    How to repeat a formula every X rows

    @ad.dias @SirJB7 Damn. OK, round did a funny thing around Noon on each day. Was trying to get away from doing the Date statement. Shows what corner cutting gets you. Updated sheet attached.
  5. Mike86

    Count records that don’t exist by dates but are inferred by status

    @MediumLebowski It's identifying every person in the A28:E30 table that have start dates before and end dates after the year-quarter in h5:h16. If a person started in or before that quarter, and were discharged after or during that quarter, they're counted. Getting this function to work was...
  6. Mike86

    Count records that don’t exist by dates but are inferred by status

    @MediumLebowski Yes, that's a pain. OK, fixed.
  7. Mike86

    How to repeat a formula every X rows

    Brute force, but here's a way to do it. You don't always have the same number of data points per day, so using the average if and adding a Date column seemed a simple way to do it.
  8. Mike86

    Count records that don’t exist by dates but are inferred by status

    @MediumLebowski I took a shot at this, but was getting some different counts than yours. To get it to work without being a pain, I needed to reformat your Corp Fiscal Qtr column to show the years first. Cells D28:E30 have the min/max Assess Dates converted to quarter format. Here's an issue...
  9. Mike86

    Putting length of service into age brackets

    @Brooksy1 Sorry for the response delay, I don't check here every day. Without getting too messy, the attached file seems to do what you want. Hiding Col J will make it look cleaner.
  10. Mike86

    Putting length of service into age brackets

    Another look at a solution. I've added a name selection and some other things. You can change the age ranges by altering the yellow values. You also need to make sure the ages match for the start/stop of the different blocks. Otherwise, you lose years in those gaps.
  11. Mike86

    Creating monthly, quarterly and annual analysis

    If you check back, the quarter issue is resolved.
  12. Mike86

    Creating monthly, quarterly and annual analysis

    This may be doing what you want, but I'm not 100% sure. If this isn't what you want, a few more examples of the desired output would be helpful.
  13. Mike86

    InTake Form

    There's the basic data form provided with Excel. It's not very elaborate, but it's easy to activate and works well with simple tables: https://support.office.com/en-us/article/Add-edit-find-and-delete-rows-by-using-a-data-form-17bca0a4-3ba5-444a-983c-a8ce70609374
  14. Mike86

    using index/match to find the intersection of 2 variables - not working

    See Bosco's comment in #4. By changing the headers to text (ie using ">", "-", or "<" in the header) you stopped the Match function from operating. I'd recommend using 0, 5, 10, 15, 20, and 100 for the headers. The function works with that. If you must do the headers as you have them above, use...
  15. Mike86

    using index/match to find the intersection of 2 variables - not working

    Check the value in B1. It displays 0.5%, which would put you in the 0% column. When 5 is entered in B1, the rate is displayed as $1.70
  16. Mike86

    Dating sheet! Who is the most suitable match?

    @Dvd42 It was a fun problem to work on. Let me know if you have any other questions or if something doesn't work right.
  17. Mike86

    Dating sheet! Who is the most suitable match?

    @Dvd42 I've done a few things on this. There's a new Resolved Mentor tab that simply pulls the mentor numbers out from the Condensed tab. This is needed for the loading calculation. I also simplified how you were resolving the Mentor names in the Name tab. To try and reduce the file size, I...
  18. Mike86

    Dating sheet! Who is the most suitable match?

    @Dvd42 OK, another concept. I got tired of doing the weightings as the sensitivity shifted too far with the number of locations. This is more brute force, but the end result is a sorted listing of best matches per graduate. The tab Matrix-2 encodes the level of match (4 all factors: 1...
  19. Mike86

    Dating sheet! Who is the most suitable match?

    @Dvd42 Take a look at the attached file. I was playing with different weightings for the three factors and I'm pretty close. I can now differentiate between Institutions, locations, and even departments by varying the acceptable range on the Matrix tab. I don't like the big matrix, but it's...
  20. Mike86

    Dating sheet! Who is the most suitable match?

    Just looking at your spreadsheet. There will obviously be situations where there are too many or too few from the two groups to match up everyone. What were you thinking about as this starts happening through the Institutions and locations? Match the first x and leave the rest or start...
  21. Mike86

    Word string match formula

    If you're talking about just looking at two cells, this will work: =IFERROR(IF(AND(FIND("shoe",A1)>0,FIND("shoe",B1)>0),"yes",""),"no")
  22. Mike86

    Dating sheet! Who is the most suitable match?

    It'd really help if you'd upload an dummy file of approximately what you're working with. Also, is there a priority for the matching criteria? You mentioned location first, but could you use a 2,3,4,....n rating for the other attributes?
  23. Mike86

    IF INDIRECT SEARCH multiple worksheets.

    @e_var Well, here's my copy of your file using the function. I'm not showing any errors on the Schedule tab after picking up data from all tabs. I added headers and a fake ID number to the Sept-Nov tabs to make sure I was hitting the marks, although I don't think that would make a difference...
  24. Mike86

    IF INDIRECT SEARCH multiple worksheets.

    @e_var The code can be adjusted using the same logic. Basically, you just keep nesting the IF statements. With your existing spreadsheet, it'd look something like this...
  25. Mike86

    IF INDIRECT SEARCH multiple worksheets.

    You need to correct for the number of lines in "June" and subtract the current row count (row()). By modifying your function slightly, this can be done. For example (from your cell Schedule!A72)...
Back
Top