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

Find the first % value > greater the 1% for each day

jazntail

New Member
Need help figuring out a formula to dynamically find the first % value > greater the 1% for each day.
And also the record number or line.
column A-D is the data set. Very large data set.
column H-J is on a different sheet.
I've used "match(" to find a record number but can't figure out how to get it to dynamically lookup the dates in column A.
I've used "index(" and "match(" to find the record of the first % value > greater the 1% for a day not dynamically.
Any help would be amazing.
83076
 
Absolutely, column J. thank you..
Column I. I want to extract the line or record the number of the % is each day.
Would you use =countifs( formula?
 

Attachments

  • Untitled spreadsheet.xlsx
    52.9 KB · Views: 6
Thank you for your reply..
18-2-23 column n = "2" second record for that date etc,
17-2-23 column n = "2" second record for that date
The data will change it won't always be the second record for each date.
 
jazntail
That can be true too ...
Did You test it with my file?
... change eg cells D8 value to 4% >> then cells I3 value will come to 8
... change eg cells D6 value to 2% >> then cells I3 value will come to 6
... okay?
 
Column I in red is the answer I am looking for. The number of records for that date to get value %
 

Attachments

  • jazntail.xlsx
    11.4 KB · Views: 4
jazntail
...hmm?
if I-column values (row from sheet) are something else that You're looking for then ...
if L-column values are something You're looking for check P-column formulas.
 

Attachments

  • jazntail (1).xlsx
    11.8 KB · Views: 3
Power Query solution in table at cell F2.
Have your data fully included in the table on the left, then right-click the table at cell F2 and choose Refresh.

83084

I imagine getting the record numbers are only part of a process, what are you going to do with them?
 

Attachments

  • Chandoo51359Untitled spreadsheet-3.xlsx
    68.9 KB · Views: 0
Last edited:
jazntail
Other sample to get those datas.
Usage:
#1 Paste valid data
#2 Press the button
#3 Press 2nd time the button to get other view
 

Attachments

  • Untitled spreadsheet.xlsb
    21.8 KB · Views: 2
Back
Top