Skip "0" values and find the next one.
Good evening everyone!
Firstly, attached is my sample excel spreadsheet (heavily cutdown).
The SQL Database can show many steps that run through the facility.
The "Name" (in column G) can show up many times within that DB (for the multiple steps) and sometimes a step will be registered at step "0".
I would like column O "Current Fab step" or W "Current TDI step" to return the next value of it comes across a "0" from the "Raw data" sheet. I am currently using a VLOOKUP().
Thank you!
I do not understand what you want when you say:
I would like column O "Current Fab step" or W "Current TDI step" to return the next value of it comes across a "0"
from the "Raw data" sheet.
Please explain, with examples, wexactly what you mean. One guess, though. I shall assume you mean the largest value from column E (Step Number) matching the SKY or PSD number. In which case (W12, copied down):
=IFERROR(AGGREGATE(14,6,vwADFab_LotScans[StepNumber]/(vwADFab_LotScans[LotID]=S12),1),"")
Last edited by Glenn Kennedy; 01-26-2021 at 03:55 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Apologies for the lack of details and the typo.
Currently, columns O and W pull data from sheet "Raw data" using a VLOOKUP function to look for the most recent step number that the "NAME" processed through. Unfortunately, the MAX value won't work because some processes require a step that make it jump up to 1000+ for a few days.
I currently have the query setup up so the '!Raw Data!B' is sorted from newest to oldest. Giving the most recent information at the top (most recent step number).
I would like my VLOOKUP function to "find the next row" if the current one has a step "0" in it thus giving me the next most recent step.
For example:
If I look up PDS002879E in "Raw Data A:E" the first instance that shows up is row 49 with a "Step number" of "0".
It also shows up in row 136 with a "Step number" of "13"
I would like my VLOOKUP to skip row 49 and give me the value from row 136.
OK.
=IFERROR(INDEX('Raw data'!E:E,AGGREGATE(15,6,ROW(vwADFab_LotScans[StepNumber])/((vwADFab_LotScans[LotID]=S12)*(vwADFab_LotScans[StepNumber]>0)),1)),"")
If you look at the sheet, it returns the 13, even though there is a 20 (typed in by me) a bit further down. So, it returns the first non-zero amount, not the largest.
Thank you VERY MUCH!
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
Will do. I am very new to this site.
I wanted to ask, for my own knowledge. What does the argument in ROW() doing?
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks