# Posts by rinconpaul

• ## Conditional Index Match array

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.
Thanks

• ## VBA to copy and paste cells to scattered cells on another sheet

Ah Ha! very sneaky and unique way of doing things. Thankyou kindly for solving this mind game Robert. Now that I know, I can put it to good use.

• ## VBA to copy and paste cells to scattered cells on another sheet

[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:

• ## Pivot Table groups of values

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.

Cheers

• ## Input a Row variable pertaining to all macros

Perfection Pike, your new name pike! Well done and many thanks. :cheers:

• ## Input a Row variable pertaining to all macros

Thanks pike for your help. Getting an error and no input box? I've pasted your code into a working example so you can play with it. Sheet2(2) is how my code runs (Module 1) I've put your code in module 2. See how you go, just a tweak or two hopefully? [ATTACH]n1201412[/ATTACH]

• ## Stacking repeating grouped columns

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.

• ## Input a Row variable pertaining to all macros

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

• ## Stacking repeating grouped columns

There was a good pictorial of what I'm trying to achieve attached, but the solutions were beyond me: http:excel-inside.pro/blog/2015/11/16/stacking-non-nested-groups-of-repeating-columns-in-power-query/ [ATTACH=JSON]{"data-align":"none","data-size":"full","data-attachmentid":1201352}[/ATTACH]

• ## Stacking repeating grouped columns

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:

• ## Stacking repeating grouped columns

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]

• ## Complex COUNTIFS?

What a champion you are NBVC! I could've never came up with that solution, well done and thank you.

• ## Complex COUNTIFS?

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]

• ## Overlay Pivot Slicer Graphs

Re: Overlay Pivot Slicer Graphs

Solved it. Simply add the 5 day period criteria to "Columns" in Pivot Data Fields. I can now see the X axis values that consistently produce a profit consistently every 5 days

• ## Overlay Pivot Slicer Graphs

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?

• ## Find first value <>0 in subset

Re: Find first value &lt;&gt;0 in subset

Thanks Rory

• ## Find first value <>0 in subset

Re: Find first value &lt;&gt;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:

• ## Find first value <>0 in subset

I've half got this worked out. The formula I have in cell E7: =INDEX(B5:B14,MATCH(TRUE,INDEX(C5:C14<>0,),0)) finds the first price from the bottom row where the Vol <>0. I want to add another criteria and find the first price from the bottom of subset 'Ben' where Vol <>0. Answer would be 7.
Thanks

• ## Absolute references aren't absolute when pasting into Table

Re: Absolute references aren't absolute when pasting into Table

Following Allen Wyatt's advice, INDIRECT seems to solve my problem. The formula in O13 becomes =COUNTIFS(INDIRECT("O18"):O100,O15,INDIRECT("K18"):K100,">0")
:eureka: