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]

If it would help you check the results then you can add another column "Previous Row" which formula gives the row number of the previous Start Date for this unit.

Rigeljr.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Start_Date	Recorded	Last_Name	Action	Unit_No.	Month	Term	Monthly	Fees	Previous	Previous Row
2	Jan-20	16-Jan	Smith	New	1902	1	1	$ 184.49	$ -
3	Feb-20	17-Feb	Jones	New	1902	1	1	$ 184.49	$ -	Expire	14
4	Mar-20	20-Mar	Hunt	Expire	2026	1	1	$ 184.49	$ -
5	Apr-20	21-Apr	Johnson	Expire	2026	1	1	$ 184.49	$ -	Expire	4
6	May-20	23-May	Mills	Renew	2026	1	1	$ 184.49	$ -	Expire	5
7	Jun-20	24-Jun	Dunn	Renew	2026	1	1	$ 184.49	$ -	Renew	6
8	Jul-20	26-Jul	Watson	Renew	3118	1	1	$ 184.49	$ -
9	Aug-20	27-Aug	Jones	New	1902	1	1	$ 184.49	$ -	New	3
10	Sep-20	28-Sep	Feen	New	2026	1	1	$ 184.49	$ -	Renew	7
11	Oct-20	30-Oct	Loor	Expire	2026	1	1	$ 184.49	$ -	New	10
12	Nov-20	1-Dec	Klaus	New	2026	1	1	$ 184.49	$ -	Expire	11
13	Dec-20	2-Jan	Brundon	New	2026	1	1	$ 184.49	$ -	New	12
14	Jan-20	3-Feb	Sope	Expire	1902	1	1	$ 184.49	$ -	New	2
15	Feb-21	7-Mar	Keel	Expire	1902	1	1	$ 184.49	$ -	New	9
16	Mar-21	8-Apr	Nune	Expire	1902	1	1	$ 184.49	$ -	Expire	15
17	Apr-21	10-May	Drester	Renew	3118	1	1	$ 184.49	$ -	Renew	8
18	May-21	11-Jun	Olant	Renew	1902	1	1	$ 184.49	$ -	Expire	16
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)),"")
L2:L18	L2	=IFERROR(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)+ROW(Leasing_Data[[#Headers],[Previous Row]]),"")

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

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]

Hi Toadstool,

Sorry about the late response. I would like to share the spreadsheet with you as even this second "check" formula that you shared is not working correctly.

I do appreciate your patience in helping me with this. How would you like me to share the spreadsheet with you?

Thank you,

Rigel

 

[Toadstool]

You can share on Google Drive or Dropbox but make sure you've anonymized the data.

P.S. If you look at my last example can you identify which rows are wrong?

 

[rigeljr]

I have removed sensitive data to the best of my ability.

Your example is correct. The error only occurs when I transfer onto my spreadsheet.

What would be a good email to share the file with?

 

[Toadstool]

I have removed sensitive data to the best of my ability.

Your example is correct. The error only occurs when I transfer onto my spreadsheet.

What would be a good email to share the file with?

OK, I've taken a look at the large dataset. After looking at the data, with several duplicate Start Dates for the Action of a Unit, I concluded there was no key field available to find the previous entry for a Unit. This left the only option of relying on its actual position in the dataset to decide which is the previous action.

I've made the changes to the cut-down sample and show the mini-sheet below.

rigeljr-Temporary-Data-V3.xlsx
	B	C		E	F	G	H			K	L
1	Leasing Data
2	Start Date	Recorded Date		Action	Unit No.	Months Paid	Term			Previous	Check
3	Jan-17	11-Jan		New	1030	1	1
4	Jan-17	13-Jan		New	1034	1	1
5	Jan-17	13-Jan		New	1073	12	12
6	Jan-17	13-Jan		New	1081	1	1
7	Jan-17	13-Jan		New	1093	1	1
8	Jan-17	14-Jan		New	1035	1	1
9	Jan-17	14-Jan		New	1077	1	1
10	Jan-17	19-Jan		New	1083	1	1
11	Jan-17	21-Jan		New	1046	1	1
12	Jan-17	21-Jan		New	1084	1	1
13	Jan-17	28-Jan		New	1033	1	1
14	Feb-17	3-Feb		New	1087	1	1
15	Feb-17	6-Feb		New	1032	1	1
16	Feb-17	8-Feb		New	1031	1	1
17	Feb-17	9-Feb		New	1085	1	1
18	Feb-17	10-Feb		New	1067	1	1
19	Jan-17	11-Feb		Renew	1030	1	1			New	3
20	Jan-17	12-Feb		Renew	1034	1	1			New	4
21	Jan-17	13-Feb		Expire	1077	1	1			New	9
22	Jan-17	13-Feb		Renew	1081	1	1			New	6
23	Feb-17	13-Feb		New	1089	1	1
24	Jan-17	13-Feb		Renew	1093	1	1			New	7
25	Jan-17	14-Feb		Renew	1035	1	1			New	8
26	Feb-17	14-Feb		New	1077	1	1			New	21
Report Data

Cell Formulas
Range	Formula
K3:K26	K3	=IF([@Check]="","",INDEX([Action],[@Check]))
L3:L26	L3	=IFERROR(AGGREGATE(14,6,ROW([Action])-ROW(Leasing_Data[[#Headers],[Action]])/(([Unit No.]=[@[Unit No.]])*(ROW([Action])<ROW())),1)+ROW(Leasing_Data[[#Headers],[Check]]),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:E13810	Expression	=E3&K3="NewNew"	text	NO
E3:E13810	Expression	=E3&K3="ExpireExpire"	text	NO
E3:E13810	Expression	=E3&K3="ExpireRenew"	text	NO
E3:E13810	Expression	=E3&K3="RenewNew"	text	NO
B3:B13810	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3:E26	List	=INDIRECT("Actions[Leasing Actions]")