Retrieve Code From Prior Term Based on ID

is that what you want?

done with PivotTable (or I misunderstood)

Thank you for the help. What I meant is that to get the code associated with the ID for the previous term. So for example, an ID might have a code of TM for 9/1/2014 and I want to find out what the was the code associated with the ID for 2/1/2014, which is the prior term right before it. Likewise, if the ID exists for 2/1/2015, I would want to return what the code was for the ID for 9/1/2014, the prior term. And if the ID exists for 9/1/2015, return the code associated with the ID for 2/1/2015, and so on and so forth. I created "Term Code Based on Order" to help. If the term code is 4, the prior term has a code of 3, etc.

something like this one?

if not, post any example how it should be

Thank you, but not exactly. For example, let's say I have the following row:

Term Date Term Code Based on Order Code ID Code During Prior Term
09/01/14 2 PE 2073093 LA

The first four data cells are given to me, so the code PE (column 3) is associated with ID 2073093 for "Term Code Based on Order" 2 and "Term Date" 9/1/2014. I want to find what code is associated with the ID 2073093 for the prior term (defined as "Term Date" 02/1/2014 or "Term Code Based on Order" of 1). In this example for this ID, the "Code During Prior Term" was "LA", but it could be "PE", or many other codes. If no match is found for the prior term for the ID, there is a blank. Even if that ID can be found several terms back, all that I care about is the prior term nearest in time to the one listed on the row.

My solution is based on two formulas.

Enter the following formula into E8252 (start of 2nd term) and fill it down

=COUNTIFS($D$2:D8252,D8252)

Enter the following array formula into F8252 using Shift + Ctrl + Enter and copy it down

=IFERROR(INDEX($C$2:$C$41367,MATCH(1,((E8252-1)=$E$2:$E$41367)*(D8252=$D$2:$D$41367),0)),"")


I ran a pivottable and show the result for 3000945972 below. Reference Previous Term Based on ID.PNG


Kind regards

Saba

I have the solution workbook and do not know how to load it.

To attach an Excel file to your post,

• desensitize data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Thank you Sandy.

The solution workbook is attached.

I had to zip the file as it is more than 1mb.

Kind regards

Saba

You could save it as xlsb format instead of zip

Thank you! Sandy

Xlsb file is attached.

Kind regards

Saba

FlameRetired--the output would be in column E. Columns K:L just show the numbers I used for the Vlookup.

Saba--The issue with that formula is that it returns the code based on the last term where the id was found. So if the COUNTIF returns 3 for an ID, the formula will find the code for that ID when the COUNTIF was 2. However, this may be in one or more terms ago, when I am only looking to see if the ID had a code in the prior term, based on the order in column B.

Luckily, I think I was able to solve it based on your formula, by using column B.
=IFERROR(INDEX($C$2:$C$41367, MATCH(1, (D2=$D$2:$D$41367)*((B2-1)=$B$2:$B$41367),0)),"")

It seems it was simpler than I thought. Thank you all for the help!

Nice to see you resolve the problem

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Have a nice day

No problem.

Happy to help.

Kind regards

Saba