Posts by rinconpaul

    I have this array formula: {=IFERROR(INDEX(Table!$C$2:$C$1000,MATCH(1,(Data!CT45=Table!$B$2:$B$1000)*(Data!CN45=Table!$A$2:$A$1000),0)),0)} but I only want to proceed with it IF (Data!CX43="False") otherwise return "0"

    How do I incorporate the two formula? The array part has thrown me.

    [ATTACH]n1202926[/ATTACH] I was sent this workbook. When the macro is run the cells on "Sheet1" are copied and pasted to various cells on sheet "Letter". Sheet "Map" has a table that specifies the from and to for each cell. It works great BUT, I can't understand HOW! The code identifies the From and To sheets, but I see no reference whatsoever to the sheet "Map" or it's table. Is something missing here, or I just don't understand what's going on? The code:

    Picture a column of decimal fractions representing implied probability scores and alongside a column of 0 or 1 when that decimal value scored

    2.00 1
    2.02 0
    1.90 0
    2.00 0
    1.85 0
    1.90 1
    1.80 0
    2.00 0
    1.90 1

    Ordinarily if I inserted that data into a pivot table and had Value; Count and Sum of col B it would look like this:
    1.80 1 0
    1.85 1 0
    1.90 3 2
    2.00 3 1
    2.02 1 0

    Using the results in cols B & C I could divide Sum by Count and work out actual decimal probability scores (real life scenario, there are 100's of thousands of these scores) To nullify the odd peaks and troughs of results, I want to treat scores as groups of 2 say, to average out results, something like this:

    1.80:1.85 2 0
    1.85:1.90 4 2
    1.90:2.00 6 3
    2.00:2.02 4 1

    You can see I'm grouping and incrementing. The question is how do you define a grouping scenario in a pivot table analysis providing it's doable? I could do it manually but looking for an easier approach, and perform the task daily as fresh data added.


    Solved this with this code. it's rough and ready and not variable as in specifying number of rows. I've started a new thread to ask for help on that. I changed the sheet names in the example.

    In the following code, there is one variable that needs to be input each time I use it and that's the last Row number. In the example code it's 10 for all macros. I want to be able to input "xx" prior to running

    Sorry Kj,
    I call a table the contents of say cells E2:J6 or K2:P6 or Q7:V10. The real life scenario each one of these so called tables will have 12 headers and there will be 14 'tables' side by side using the same 'Event'. I coloured the cells to try and help differentiate? Hope this helps add to the confusion NOT?:chase:

    To KjBox.
    Back last September you helped me out. I adapted your code to work with my real time situation. Unfortunately my example wasn't truly representative and each day I use your macro I have to do some manual cleaning up to get it right. Not your fault though.
    I've attached another example, I need to transpose groups of table data displayed horizontally and stack them vertically keeping common col A,B,C,D reference. The attached example shows sheet "Display1" as the current situation and sheet "Display2" the desired situation.
    The real life scenario differs in that there are 14 tables horizontally, each with 12 headers. Some data in cells of tables will be blank! The row count for each "Event" varies and the number off. I'm wondering if you could redo your code to suit as I must say the advanced level of your previous code is beyond my comprehension/skill to amend?

    Cheers as always [ATTACH]n1201336[/ATTACH]

    I've tried COUNTIFS to solve my issue to no avail. In the attached sheet I've shown 4 examples and desired answers. Basically I have 3 values that reside at the top of a table, with data beneath. A formula in G1 (example 1) should provide a unique number 0 or 1. In the "Blue" quotation box, I've listed the formula parameters. For each desired answer, I've worked through with text narration, why the "1" or "0" resulted? Hope you can follow this?
    Cheers [ATTACH]n1198832[/ATTACH]

    I have a data table and pivot slicers of various criteria. If I select say 3 like criteria and then the last criteria is the running total of profit/loss for a 5 day period, this displays on a line chart. If I pick another 5 day period it displays another line chart. Is there a way to overlay all 5 day periods onto one chart. In the attached screenshot, I've taken a picture of three 5 day period profit/loss line charts, and overlayed them The X and Y axis have different ranges which makes it difficult.

    The idea is, if the X axis all correspond then I can see in an instant what range is profitable and what's not?

    Re: Find first value <>0 in subset

    Thanks guys. Amazing how many different ways to get the same result? I couldn't add more stars to your reputation sktneer, as I've already awarded you stars for your earlier efforts, but thanks again, you're very helpful. :yourock: