I have a problem extracting multiple data from a table that contains blanks. My array formula is
=IFERROR(INDEX(Transaction_No.,SMALL(IF(Status=B$1 5,ROW(Transaction_No.)-ROW(C$3)+1),ROWS(C$3:C3))),"")
Please help me in:
1. When the formula reaches a blank cell in the lookup range, there is also a blank row in the resulting table.There should be no blank rows.
2. In another sheet, I need to extract data with two criteria. How could I tweak this formula to come up with the correct list?


3. In column I of the source data, I would like a formula to automate the assigning of numbers to approved transactions starting from number 2018-1. In between the transactions are data that awaits processing.
Sample transactions attached for clarity

Attached Files

Sample Transactions.xlsx (18.1 KB, 8 views)

How about something like this. I would suggest you create an unique ID field which is in column L.

1. I'm guessing you are saying, if the Transaction No. is blank, then that row should not be returned. The formula is in E16:E19 and everything to the left pulls from the unique ID in E16:E19.

2. You didn't say what the two criteria are.

3. Look in column K. Along with the formula I used a custom format. The formual returns the rank based on the Date Approved and the custom format fills in the 2018-

Attached Files

Sample Transactions (1).xlsx (13.6 KB, 9 views)

Thank you JeffryBrown. Column K was the clincher. Re the two criteria, I just added another IF in the formula that you suggested. But the sequence numbering in column K is quite odd, if I type the same date on two or more rows, the RANK formula throws serial numbers for these same rows with the largest number on the first row.. Like this...

Approval Date------------Number--------Could this be arranged into
Jan 3----------------------2018-04-------2018-02
Jan 2----------------------2018-01-------2018-01
Jan 3----------------------2018-03-------2018-03
Jan 3----------------------2018-02-------2018-04
Jan 5----------------------2018-06-------2018-05
Jan 5----------------------2018-05-------2018-06

Is there a way for the RANK formula to start the lower sequence number from the first row of the same dates?

I'm sorry Marcia. I can't think of a way to do this, but I'll keep plugging along for a solution. In the meantime, here is a good resource for ranking.

http://www.cpearson.com/Excel/rank.aspx

Here you go!

Attached Files

Transactions.xlsx (22.6 KB, 10 views)

Big big thanks to both of you Jeffrey and ArviLaanamets. I'll keep on tweaking your formulas to accommodate more than one criteria until I get the correct result. Nested formula boggles me but I am getting there.

I missed that you did want the formula for SequenceNo too!

I added a formula, but the problem with SequenceNo calculated using formula is, it will be different whenever you sort your table, or add a new entry using Insert Row.

When you want to avoid this problem, you need a hidden sheet with table where every column represents a year. The table has a single row of data, where for every year next available SequenceNo is calculated (Max SequenceNo for given year + 1 to counting part of it). I.e. when you have a column for 2017, and there is no approved transactions registered or SequenceNo is not determined for them, then the next available SequenceNo will be "2017-01". When you have registered SequenceNo "2018-08", then in column 2018 the next available sequenceNo will be "2018-09", etc.

Now you define a dynamic named range - e.g. lSequenceNo - which returns a cell from hidden table column matching with year of transaction approval date on active row in transactions table. And you use this named range as source for Data Validation List in SequenceNo column. Now you can assign an unique sequence number to every approved entry in transactions table simply selecting it from Data Validation List - there will be always a single choice. I leave it for you to try out as an exercise :-)

Attached Files

Transactions.xlsx (22.9 KB, 10 views)

Thank you thank you for the added formula of sequence numbering. In fact I was thinking about the change in year of approval and you gave me a starting point to explore further.