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
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.
[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:
Sub CopyStuff()
Dim shF As Worksheet ' From Sheet
Dim shT As Worksheet ' To Sheet
Dim cl As Range ' Cell Pointer
' Initalize variables
Set shF = Sheets("Sheet1")
Set shT = Sheets("Letter")
' Copy from range to range
For Each cl In Range("From_Sheet")
shF.Range(cl.Value).Copy shT.Range(cl.Offset(0, 1).Value)
Next cl
End SubIt's OK, I found a solution: https://trumpexcel.com/group-numbers-in-pivot-table/
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
Perfection Pike, your new name pike! Well done and many thanks. :cheers:
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]
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.
Sub Pastytreat()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")
copySheet.Range("A2:D10").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub LoopMacroPastytreat()
Dim x As Integer
For x = 1 To 3
Call Pastytreat
Next x
Call CandPFirst
Call CandPSecond
Call CandPThird
End Sub
Sub CandPFirst()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("E2:J10").Copy Worksheets("Sheet2").Range("E" & n)
End Sub
Sub CandPSecond()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("K2:P10").Copy Worksheets("Sheet2").Range("E" & n)
End Sub
Sub CandPThird()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("Q2:V10").Copy Worksheets("Sheet2").Range("E" & n)
End SubIn 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
Sub Pastytreat()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")
copySheet.Range("A2:D10").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub LoopMacroPastytreat()
Dim x As Integer
For x = 1 To 3
Call Pastytreat
Next x
Call CandPFirst
Call CandPSecond
Call CandPThird
End Sub
Sub CandPFirst()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("E2:J10").Copy Worksheets("Sheet2").Range("E" & n)
End Sub
Sub CandPSecond()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("K2:P10").Copy Worksheets("Sheet2").Range("E" & n)
End Sub
Sub CandPThird()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("Q2:V10").Copy Worksheets("Sheet2").Range("E" & n)
End SubThere 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]
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]
What a champion you are NBVC! I could've never came up with that solution, well done and thank you.
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]
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
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 Rory 
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:
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
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: