Excel as a Database

[rigeljr]

Hi there,

I'm not even sure how to title this question properly and I think it is best demonstrated with an image. I'm not sure if this can be done but I would certainly like to try.

We have entries for various units (2026 is just an example). I would like to conditionally format the cells so that for each unit, if the following conditions are met, the cells get highlighted:
- If a "New" is followed by a "New" for the same unit
- If an "Expire" is followed by an "Expire" for the same unit
- If an "Expire" is followed by a "Renew" for the same unit
- If a "Renew" is followed by a "New" for the same unit

Happy to clarify the problem if necessary but would really appreciate any help with this.

Thank you!

 

[Toadstool]

Hi Rigeljr,

I've changed some of your values to make sure there's enough test cases.

Does this do what you want?

Rigeljr.xlsx
	A	B	C	D	E	F	G	H	I
1	Start Date	Recorded	Last Name	Action	Unit	Month	Term	Monthly	Fees
2	Jan-20	16-Jan	Smith	New	2026	1	1	$ 184.49	$ -
3	Feb-20	17-Feb	Jones	New	2026	1	1	$ 184.49	$ -
4	Mar-20	20-Mar	Hunt	Expire	2026	1	1	$ 184.49	$ -
5	Apr-20	21-Apr	Johnson	Expire	2026	1	1	$ 184.49	$ -
6	May-20	23-May	Mills	Renew	2026	1	1	$ 184.49	$ -
7	Jun-20	24-Jun	Dunn	Renew	2026	1	1	$ 184.49	$ -
8	Jul-20	26-Jul	Watson	Renew	2026	1	1	$ 184.49	$ -
9	Aug-20	27-Aug	Jones	New	2026	1	1	$ 184.49	$ -
10	Sep-20	28-Sep	Feen	New	2026	1	1	$ 184.49	$ -
11	Oct-20	30-Oct	Loor	Expire	2026	1	1	$ 184.49	$ -
12	Nov-20	1-Dec	Klaus	New	2026	1	1	$ 184.49	$ -
13	Dec-20	2-Jan	Brundon	New	2026	1	1	$ 184.49	$ -
14	Jan-21	3-Feb	Sope	New	1902	1	1	$ 184.49	$ -
15	Feb-21	7-Mar	Keel	Expire	1902	1	1	$ 184.49	$ -
16	Mar-21	8-Apr	Nune	Expire	1902	1	1	$ 184.49	$ -
17	Apr-21	10-May	Drester	Renew	1902	1	1	$ 184.49	$ -
18	May-21	11-Jun	Olant	Renew	1902	1	1	$ 184.49	$ -
19
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D99	Expression	=AND(E2=E3,D2="Renew",D3="New")	text	NO
D2:D99	Expression	=AND(E2=E3,D2="Expire",D3="Renew")	text	NO
D2:D99	Expression	=AND(E2=E3,D2="Expire",D2=D3)	text	NO
D2:D99	Expression	=AND(E2=E3,D2="New",D2=D3)	text	NO

 

[rigeljr]

Not exactly, maybe this image will help a bit because this is what the spreadsheet actually looks like without filtering.

As you can see the instances are mostly renews with expires and news scattered in there. What I need the formula to do is to look for the previous instance of each Unit and highlight it if any of the conditions I mentioned above are true.

 

[Toadstool]

Not exactly,

So where is it wrong? Which should be highlighted in your new image?

 

[Toadstool]

OK, let me take a new approach. Column T has been added to show the previous Action status. This can then be used to set the highlight.

Try it out and let me know.

Rigeljr.xlsx
	A	B	C	D	E	F	G	H	I	J									S	T
1		Start Date	Recorded	Last Name	Action	Unit	Month	Term	Monthly	Fees									Month	Previous
2		Jan-20	16-Jan	Smith	New	2026	1	1	$ 184.49	$ -
3		Feb-20	17-Feb	Jones	New	2026	1	1	$ 184.49	$ -										New
4		Mar-20	20-Mar	Hunt	Expire	2026	1	1	$ 184.49	$ -										New
5		Apr-20	21-Apr	Johnson	Expire	2026	1	1	$ 184.49	$ -										Expire
6		May-20	23-May	Mills	Renew	2026	1	1	$ 184.49	$ -										Expire
7		Jun-20	24-Jun	Dunn	Renew	2026	1	1	$ 184.49	$ -										Renew
8		Jul-20	26-Jul	Watson	Renew	2026	1	1	$ 184.49	$ -										Renew
9		Aug-20	27-Aug	Jones	New	2026	1	1	$ 184.49	$ -										Renew
10		Sep-20	28-Sep	Feen	New	2026	1	1	$ 184.49	$ -										New
11		Oct-20	30-Oct	Loor	Expire	2026	1	1	$ 184.49	$ -										New
12		Nov-20	1-Dec	Klaus	New	2026	1	1	$ 184.49	$ -										Expire
13		Dec-20	2-Jan	Brundon	New	2026	1	1	$ 184.49	$ -										New
14		Jan-21	3-Feb	Sope	New	1902	1	1	$ 184.49	$ -
15		Feb-21	7-Mar	Keel	Expire	1902	1	1	$ 184.49	$ -										New
16		Mar-21	8-Apr	Nune	Expire	1902	1	1	$ 184.49	$ -										Expire
17		Apr-21	10-May	Drester	Renew	1902	1	1	$ 184.49	$ -										Expire
18		May-21	11-Jun	Olant	Renew	1902	1	1	$ 184.49	$ -										Renew
19
Sheet1 (3)

Cell Formulas
Range	Formula
T2:T19	T2	=IFERROR(INDEX($E$2:$E$9999,AGGREGATE(15,6,ROW($E$2:$E$9999)-ROW($E$1)/(($F$2:$F$9999=F2)*($B$2:$B$9999=AGGREGATE(14,6,$B$2:$B$9999/(($B$2:$B$9999<B2)*($F$2:$F$9999=F2)),1))),1)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E99	Expression	=T2&E2="RenewNew"	text	NO
E2:E99	Expression	=T2&E2="ExpireRenew"	text	NO
E2:E99	Expression	=T2&E2="ExpireExpire"	text	NO
E2:E99	Expression	=T2&E2="NewNew"	text	NO

 

[rigeljr]

It looks promising but not quite there yet. Here is a screenshot of the result I get. The formula I used (this is being done inside a table) is:
=IFERROR(INDEX([Action],AGGREGATE(15,6,ROW([Action])-ROW(Leasing_Data[[#Headers],[Action]])/(([Unit No.]=[@[Unit No.]])*([Start Date]=AGGREGATE(14,6,[Start Date]/(([Start Date]<[@[Start Date]])*([Unit No.]=[@[Unit No.]])),1))),1)),"")

Can you maybe walk me through your logic in this formula so I can try to tweak it for my own purpose? I've never used an Aggregate function before and some of these functions are used in ways I've never seen before.

 

[Toadstool]

Hi rigeljr,

Your latest example seems to have unique Unit numbers so you can't verify the results.

I've created a table and used your formula but the limitation in Excel 2016 that you can't have a space in a Named name means I had to use underscores instead. I entered the formula and it worked fine with my test data (see below but note I've not changed the Conditional Format formulae so you could change them to use Names if you want).

To explain what my formula does:

• The second AGGREGATE uses the LARGE (14) option to search the start Date for the highest (i.e. latest date). It divides by the Start Date being less than the Start Date of the current row and the Unit number equaling the current row Unit number. If either comparisons return a logical zero then multiplied together they give zero which divided into the Start Date being checked generates a #DIV/0! which is trapped by the AGGREGATE 6 option (AGGREGATE(14,6) which says ignore errors, so it checks the next date. If it finds no such previous date (i.e. this is the first entry for that Unit number) it returns a #NUM! error and the IFERROR puts a null in the "Previous" column.
• The first AGGREGATE uses the SMALL (15) option to look for the first row (so it's not checking contents of a column but rather each row number in turn) where (using the same logical checks divided into row number) it looks for the date returned by AGGREGATE two for that Unit number. The first AGGREGATE returns that row number and the INDEX retrieves the Action and puts it into Previous.

Rigeljr.xlsx
	B	C	D	E	F	G	H	I	J	K
1	Start_Date	Recorded	Last_Name	Action	Unit_No.	Month	Term	Monthly	Fees	Previous
2	Jan-20	16-Jan	Smith	New	2026	1	1	$ 184.49	$ -
3	Feb-20	17-Feb	Jones	New	2026	1	1	$ 184.49	$ -	New
4	Mar-20	20-Mar	Hunt	Expire	2026	1	1	$ 184.49	$ -	New
5	Apr-20	21-Apr	Johnson	Expire	2026	1	1	$ 184.49	$ -	Expire
6	May-20	23-May	Mills	Renew	2026	1	1	$ 184.49	$ -	Expire
7	Jun-20	24-Jun	Dunn	Renew	2026	1	1	$ 184.49	$ -	Renew
8	Jul-20	26-Jul	Watson	Renew	2026	1	1	$ 184.49	$ -	Renew
9	Aug-20	27-Aug	Jones	New	2026	1	1	$ 184.49	$ -	Renew
10	Sep-20	28-Sep	Feen	New	2026	1	1	$ 184.49	$ -	New
11	Oct-20	30-Oct	Loor	Expire	2026	1	1	$ 184.49	$ -	New
12	Nov-20	1-Dec	Klaus	New	2026	1	1	$ 184.49	$ -	Expire
13	Dec-20	2-Jan	Brundon	New	2026	1	1	$ 184.49	$ -	New
14	Jan-21	3-Feb	Sope	New	1902	1	1	$ 184.49	$ -
15	Feb-21	7-Mar	Keel	Expire	1902	1	1	$ 184.49	$ -	New
16	Mar-21	8-Apr	Nune	Expire	1902	1	1	$ 184.49	$ -	Expire
17	Apr-21	10-May	Drester	Renew	1902	1	1	$ 184.49	$ -	Expire
18	May-21	11-Jun	Olant	Renew	1902	1	1	$ 184.49	$ -	Renew
Sheet1 (4)

Cell Formulas
Range	Formula
K2:K18	K2	=IFERROR(INDEX([Action],AGGREGATE(15,6,ROW([Action])-ROW(Leasing_Data[[#Headers],[Action]])/(([Unit_No.]=[@[Unit_No.]])*([Start_Date]=AGGREGATE(14,6,[Start_Date]/(([Start_Date]<[@[Start_Date]])*([Unit_No.]=[@[Unit_No.]])),1))),1)),"")

Named Ranges
Name	Refers To	Cells
Action	='Sheet1 (4)'!$E$2:$E$18	K2:K18
Start_Date	='Sheet1 (4)'!$B$2:$B$18	K2:K18
Unit_No.	='Sheet1 (4)'!$F$2:$F$18	K2:K18

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E99	Expression	=K2&E2="RenewNew"	text	NO
E2:E99	Expression	=K2&E2="ExpireRenew"	text	NO
E2:E99	Expression	=K2&E2="ExpireExpire"	text	NO
E2:E99	Expression	=K2&E2="NewNew"	text	NO

 

[rigeljr]

Can you explain what you mean by "Your latest example seems to have unique Unit numbers so you can't verify the results."?

My formula gives me results in my table as well but the only result that shows up in the "Previous" column is "New" and they are not accurate.

I'm just unsure where I'm going wrong with this.

The main reason I need this is because we have over 300 unique unit numbers and it would save a lot of time to not have to go through each one to make sure none of the conditions have been triggered.

 

[rigeljr]

There are multiple entries for the same unit (i.e. different people in the unit at different times in history). I don't know if it helps but a good unique identifier for the Aggregate search is the Start Date*Unit

 

[Toadstool]

Can you explain what you mean by "Your latest example seems to have unique Unit numbers so you can't verify the results."?

The screen image starts with a row of Renew Unit 2129 and the last entry is Renew 3118. I understood the check between status entries was for the same Unit. As I only see one row for each Unit then I can't tell if a Previous status of New is correct or not. It works with my test data.

My formula gives me results in my table as well but the only result that shows up in the "Previous" column is "New" and they are not accurate.

Please take a temporary copy of your data and sort by Start Date within Unit No. Then you can tell if it's working or not.

I'm just unsure where I'm going wrong with this.

Then we are both stymied.

The main reason I need this is because we have over 300 unique unit numbers and it would save a lot of time to not have to go through each one to make sure none of the conditions have been triggered.

Yes, I believe I understand what you want to do but unless we can agree on a set of test data with multiple entries for several Unit Nos so we can verify the highlighting then I can't help.

If you can't use XL2BB or provide a Dropbox version of your data then just sort a temporary set of data by Start Date within Unit No. and do a text copy and paste into this forum thread of at least 50 rows.


P.S. I see you've also posted "There are multiple entries for the same unit (i.e. different people in the unit at different times in history). I don't know if it helps but a good unique identifier for the Aggregate search is the Start Date*Unit".
What my formula tries to do is compare each row Action for its Unit with the previous (by Start Date) Action for the same Unit. I believed that was the request of your original post.