Extract unique values based on either of two conditions in another column

[RJK]

Hi,

We have a six (6) column table: Date-Txn ID-FirstName-LastName-Plan-Amount
In report columns (preferably on a different tab), we have this array formula to extract unique Txn ID's from the table.

Please Login or Register  to view this content.

How can I use additional criteria to extract unique Txn ID' that have a plan named "5-Pay" or "10-Pay" and in a separate column count the number of transactions in the table for each Txn ID.

Also, on the Expected results tab have a list of the unique Tnx ID's, name it belongs to, and number of payments made, including the initial payment and date of last payment received.

I have attached a sample file with raw data, my formulas and what result is expected.

I appreciate taking the time to read my post.

RJ

[Glenn Kennedy]

1. What is your CURRENT Excel version?

2. Formula or VBA?

[JohnTopley]

Try

Please Login or Register  to view this content.

CSE entry

[Glenn Kennedy]

Heres a formula version. Each shade = different formula. Here's the one (in C2) that does the hard work. NONE are array formulae.

=IFERROR(INDEX(Data!B:B,AGGREGATE(15,6,ROW(Data!$B$2:$B$76)/((Data!$B$2:$B$76<>"")*(ISNUMBER(SEARCH("Pay Plan",Data!$E$2:$E$76)))*(COUNTIF(C$1:C1, Data!$B$2:$B$76)=0)),1)),"")

[jindon]

Full vba solution,

updates Data!G2:G3 & Expected Results each time you select Expected Results sheet from other worksheet.
1) to Extpected Result sheet code module

Please Login or Register  to view this content.

2) to a Standard code module

Please Login or Register  to view this content.

[RJK]

Thank you Glenn for this perfect solution, especially all formulas are without CSE and I can make further changes after I study how to use the aggregate command.

I just noticed one thing, the formulas work perfect on the "Expected Results" sheet but not on the "Data" sheet, the first result show up on all rows in column J. My intention was to use the formula on the Data sheet, but I can live with using it on the Expected Results sheet, which I'll change to "Report".

Also when I changed the absolute references for column B and column E in the formula, in column J of the Data sheet,

Please Login or Register  to view this content.

I got duplicate results, so I guess I must keep using absolute references and keep changing the range every time I get more data rows.

Again I appreciate your time and expertise, I'll mark this thread solved after you reply to this reply.

[RJK]

Thank you Jindon,

Your vba code worked too, but, it will take me a longer time to study it and learn the nuances of the code and how to change it, if needed, for additional tasks and learn from it.

[Glenn Kennedy]

1. I have put the results into the desired sheet.

2. There are times when using whole-column references is a good idea. There are times when it is NOT. This is one of the second type of times. But, you cna used dynamic named ranges. These autoadjust the ranges to suit your date. CTRL-F3 to view/edit.

They all are like this one:

=Data!$B$2:INDEX(Data!$B:$B,MATCH("Zzzz",Data!$B:$B))

The formulae then change a bit to look like this:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(ID)/((ID<>"")*(ISNUMBER(SEARCH("Pay Plan",Plan)))*(COUNTIF(K$1:K1,ID)=0)),1)),"")

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 click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[RJK]

Thank you Glenn again for your help.

The formula in the new file is exactly same as what I entered in the Data sheet, yet yours works and mine did not, I guess I don't have the magic touch

I'll test with CTRL-F3 later.

[Glenn Kennedy]

Did you set up ALL of the Named Ranges???