Posts by rinconpaul

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

I tried giving the ranges a Defined Name and deleted the Table, but NO, the range still moves down with every update....a real pain!

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

Thanks TheGlovner. Sorry for the late reply, it's been beddybyes time :sleep:

Recently this was published on the same issue: https://excel.tips.net/T002876…ferences_in_Formulas.html

I'll explore his solutions. This is my code called every 5 secs:

Sub Record_data()
'
' Record_data Macro
'
Sheets("Data").Select
    Sheets("Data").Rows("15:15").Select
    Sheets("Data").Range("B15").Activate
    Selection.Copy
    Sheets("Data").Rows("17:17").Select
    Sheets("Data").Range("B17").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Rows("17:17").Select
    Sheets("Data").Range("B17").Activate
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
End Sub

Maybe there is a code work round too?

I was hoping this wouldn't happen?
The problem is I have formulas in row 13 that looks at a range in the Table below. Every 5 secs the contents of row 15 are pasted to row 18 of a Table and the Table expands down a row to accomodate. Take cell O13 for example. When the Table was blank the COUNTIFS formula referred to ranges O$18:O18,K$18:K18. As the Table is populated despite the '$" absolute reference, the range moves down instead of expanding. At present in the screen capture there has been 32 5sec pastes carried out, and the range is now O$50:O50,K$50:K50 instead of O$18:O50,K$18:K50.

Is there a way round this? Cheers

Re: Return bottom value in subsets where criteria <> 0

Overwhelmed by the helpful posts. What a great forum. Feelin loved :sing:

Re: Return bottom value in subsets where criteria <> 0

Thanks for the tip Carim. I'm about to find out, as the use of this formula will apply to a dynamic table?

Re: Return bottom value in subsets where criteria <> 0

Most of it went over my head:thanx:, but your explanation is on the forum forever now, should anyone else seek an answer. Well done again.

Re: Return bottom value in subsets where criteria <> 0

Apologies sktneer for the 'skywriter'! Was just going from memory when I wrote the reply and plucked the wrong name out of my brain.:0ops:

OK, well that's changed things a bit. Wonder why the old formula did work at one stage though?? :question: You've been very professional and kind in sorting the solution. Now I've got two correct answers. Cheering!...and now Carim has weighed in! Three Thankyou all :ole:

Re: Return bottom value in subsets where criteria <> 0

Yes, I will skywriter. Thought you'd be too busy to bother and didn't want to be a pain. All I've done here is move the test data around and adjusted the formula to suit. Your formula is throwing up an answer from further down the column? Like I said previously, can't work out WHY? I've redone your formula in cells J9, 10 and the 'Max' formula alongside.

Re: Return bottom value in subsets where criteria <> 0

Another good solution sktneer! In fact I'm kinda favouring your's more than the earlier one. There are some weird things happening with that formula on later trials, that I can't comprehend and getting an #N/A error? Seems more unstable? Thankfully you replied and all is good.

Re: Return bottom value in subsets where criteria <> 0

Thankyou skywriter for you correct and prompt response.

I have 'Name' subsets in col A. If I want to find the first 'Back' value from the bottom of subset '1. Target Satin', I can use :
=INDEX(B2:B26,SUMPRODUCT(MAX(ROW(A2:A26)*(E2=A2:A26))-1))

in cells F2, F3 I want to add a condition to this and only return the first 'Back' value from the bottom of each subset that doesn't equal 0 in col C

Re: Only Paste Ranges if NOT duplicate of current subsets

Sorry Name2 cell I5 should read Bill-32

In the attachment, sheet "Data" gets data pasted into Row5 every 5 secs from Sheet2 using code not shown.
The code below is called by another sub (not shown) to paste row 5 into row 8 and the past row data shunts down by one row. I'm looking to amend this code to make it more useful. A lot of the time row 5 will have duplicate data, specifically "Value1". The text/values that appear in row 5 as Constant1,2,3 will always be pasted. However, if Name1 or Name2, as they appear in row 5 are already duplicated in the table below, then Name1 or Name2, Value1, Value2 will NOT be pasted.

If the sheet is about to paste (5 secs is up) and looking at the attachment's row 5 you can see that 'Joe-40' has already been pasted below, so 'Joe-40 Value1 40 & Value2 50 will NOT be pasted. However Bill-38 is not a duplicate so will be pasted, plus associated Value1,2.

Sub Record_data()
'
' Record_data Macro
'
Sheets("Data").Select
    Sheets("Data").Rows("5:5").Select
    Sheets("Data").Range("A5").Activate
    Selection.Copy
    Sheets("Data").Rows("8:8").Select
    Sheets("Data").Range("A8").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Rows("8:8").Select
    Sheets("Data").Range("A8").Activate
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
End Sub

Re: Conditional Filter of Subsets

Solved it!

In col A I have subsets and in Col B values. These subsets vary in length (# rows).I'm looking for away to filter the values by using the last value in each subset (cells B5, B10, B14). I'll refer to these as the anchor cells. and only display remaining values ">" or "<" than the anchor cells.

I don't think Advanced Filter or a Pivot Table can do this task? Happy to be proven wrong!

In col A are a series of subsets. They vary in length and number. In col B is a set of values for each subset. The first value is at the bottom of each subset (first as in recorded first compared to value at top of each subset which was recorded last).

I need a UDF or code that looks at col A, identifies the first value for each subset(...Rocky Danlew subset would be cell B8 '2.16') and pastes into col E.Then it compares that value with the second last value B7 '2.06' and pastes that into col E. If B7 is less than B8 then a "<" is defined for the rest of the operation. If B7 is more than B8 then a ">" is defined for the rest of the operation. In this first subset case, 2.06 is < 2.16 so only values "less than" last qualifying value will be posted to col E. In this case only 1.95 qualifies. In col F I've written down the logic argument for each row left in that subset.

Col E displays the desired results. Cols C & D are not required and only used to demonstrate the methodology.
Cheers

Re: Rank values with complex rules?

'Perfection', by any other name. KjBox you have surpassed all expectations above and beyond. Your skillset is nothing short of miraculous. I cannot laud you enough and by way of thanks, I present you with the Excel Cup 2017. Last issued in 2013 and only for talent, rarely seen. Thankyou so much.

Re: Rank values with complex rules?

Continuing with testing Maestro. The UDF trips up when values are tied, it seems? Here is a screenshot, where I've copied and pasted 6. Cherie... alongside 3. Mr Mot.... The first two values for 6. Cherie.. were the same (3, 2.94) resulting in an error in the Rank col. In the top right I started to manually place the values below assigned Rank and the 5th Rank was not returned due to this error.

Thinking about how to handle ties: If they are tied for 4th say, in an 8 horse race, then that's how they should display - two #4's, so #5 is missing, because the odds are that ties are rare and we need to maintain the same number of rank values as horses in a race.

Re: Rank values with complex rules?

Further to my previous post, I'm thinking that if your code works on the initial run, which is supurb, but not on amendments....who cares? I'll never be amending the values anyway. There's probably an explanation as to why that happens but my logic says that it shouldn't make any difference to the code?...Perplexed

Re: Rank values with complex rules?

Something not quite right Maestro? I've changed values in col C deliberately to see how the UDF responds. The Rank cells corresponding to the change are correct, but the other cells that are affected by this do not? In the attachment I changed values in 'yellow', but rows in 'red' didn't change on the UDF. A tweek is all?