Court Membership challenge

[pbutcheck]

We have a project that is currently underwhelming me and I know there is vast room for improvement. But I think I'm failing to get the best idea for moving forward. Here's what we have:

Mission: track the law firm's attorney's registration due dates for every court each atty is registered with.

What we have: a few hundred attorneys. Dozens of courts. Every atty has a unique combination of 1) court registrations, 2) due dates

What is happening: our admins are going thru a clumsy table which shows each atty on a row, and then a due date under the respective court. See simple sample below.

the problem is that this table is painful to navigate, manage, etc. I wanted to track like a membership, but there can be differing membership(s) per person. So we could have as small as 1 membership, up to a dozen. Then I tried to organize it by atty, but really the admins need to know when it is due (more than for whom it is due)... BUT then once they know what is due, they have to know for whom so they can contact that person. Also have to know who didn't pay. And then it repeats every year.

I feel like I'm 90% to a solution, but that each solution isn't quite right. I'm hoping someone could give me an idea or an example that I could use to build a smarter method of tracking due dates, and payment dates. I would like build a chart sheet showing the next month's due dates (and for whom). BTW, the admins who would use this do NOT have the experience/skillset to manage it themselves. I have to build it so minimum modification is required moving forward.

anyone face this problem and find a graceful solution?

	Court 1		Court 2		Court 3		Court 4
	due	paid	due	paid	due	paid	due	paid
Mr. Blue	1/1/2023​	12/1/2022​			5/1/2023​
Mrs. Green			3/1/2023​		7/1/2023​		6/1/2023​
Ms. Yellow							11/1/2023​
Mr. Beige	7/1/2023​				11/1/2023​

 

[Micron]

Just read a few lines because they scream to me "this is a job for a relational database" but then I'm biased that way. If done correctly, users only need to interact with the same forms built for repetitive tasks for inputting data, or generating pre-made reports to retrieve that data. As I said, I'm biased, but that's because I know something about relational db's (Access) and feel bad for those who are stuck in the Excel world, trying to make a workbook act like a relational database.

Maybe you'll get an Excel solution but I think the shortcoming will be your user's ability to deal with worksheets. HTH.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[pbutcheck]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you - I did that. And I can't believe I left out those details!

 

[pbutcheck]

Just read a few lines because they scream to me "this is a job for a relational database" but then I'm biased that way. If done correctly, users only need to interact with the same forms built for repetitive tasks for inputting data, or generating pre-made reports to retrieve that data. As I said, I'm biased, but that's because I know something about relational db's (Access) and feel bad for those who are stuck in the Excel world, trying to make a workbook act like a relational database.

Maybe you'll get an Excel solution but I think the shortcoming will be your user's ability to deal with worksheets. HTH.

You're right; I'm working in the confines of the job. It can't go to a DB; or Access. More a political reason than technical.

 

[pbutcheck]

We have a project that is currently underwhelming me and I know there is vast room for improvement. But I think I'm failing to get the best idea for moving forward. Here's what we have:

Mission: track the law firm's attorney's registration due dates for every court each atty is registered with.

What we have: a few hundred attorneys. Dozens of courts. Every atty has a unique combination of 1) court registrations, 2) due dates

What is happening: our admins are going thru a clumsy table which shows each atty on a row, and then a due date under the respective court. See simple sample below.

the problem is that this table is painful to navigate, manage, etc. I wanted to track like a membership, but there can be differing membership(s) per person. So we could have as small as 1 membership, up to a dozen. Then I tried to organize it by atty, but really the admins need to know when it is due (more than for whom it is due)... BUT then once they know what is due, they have to know for whom so they can contact that person. Also have to know who didn't pay. And then it repeats every year.

I feel like I'm 90% to a solution, but that each solution isn't quite right. I'm hoping someone could give me an idea or an example that I could use to build a smarter method of tracking due dates, and payment dates. I would like build a chart sheet showing the next month's due dates (and for whom). BTW, the admins who would use this do NOT have the experience/skillset to manage it themselves. I have to build it so minimum modification is required moving forward.

anyone face this problem and find a graceful solution?

	Court 1		Court 2		Court 3		Court 4
	due	paid	due	paid	due	paid	due	paid
Mr. Blue	1/1/2023​	12/1/2022​			5/1/2023​
Mrs. Green			3/1/2023​		7/1/2023​		6/1/2023​
Ms. Yellow							11/1/2023​
Mr. Beige	7/1/2023​				11/1/2023​

Adding that this is for Excel 2013 (and in about 6 mos; 2016). As mentioned in other comments, cannot use Access or a DB for non-tech reasons.

 

[Automatrix]

Can you build something like this for them instead?

Atty	Court	Due	Paid
Mr. Blue	Court 1	1/1/2023	12/1/2022
Mr. Blue	Court 3	5/1/2023
Mrs. Green	Court 2	3/1/2023
Mrs. Green	Court 3	7/1/2023
Mrs. Green	Court 4	6/1/2023
Ms. Yellow	Court 4	11/1/2023
Mr. Beige	Court 1	7/1/2023
Mr. Beige	Court 3	11/1/2023

 

[pbutcheck]

Can you build something like this for them instead?

Atty	Court	Due	Paid
Mr. Blue	Court 1	1/1/2023	12/1/2022
Mr. Blue	Court 3	5/1/2023
Mrs. Green	Court 2	3/1/2023
Mrs. Green	Court 3	7/1/2023
Mrs. Green	Court 4	6/1/2023
Ms. Yellow	Court 4	11/1/2023
Mr. Beige	Court 1	7/1/2023
Mr. Beige	Court 3	11/1/2023

yes, absolutely. That is actually what I'm doing now. I was only shopping for better ideas to see if people more creative than me might have found a better solution. I think this is the best method; other than a tiny access DB (which I can't use for other reasons).
I'm actually taking it a step beyond and I'm looking into non-Excel solutions as well (docketing calendar, for example).

But a big thank you for sharing your ideas. I'm on board that this is the instant fix they need. (plus, pivot tables! yay!)

 

[Micron]

(which I can't use for other reasons)

Care to say what that is? If it has anything to do with users not having Access installed, that is not really an issue. Only the developer needs Access.

 

[JGordon11]

Power Query can transform your table from the blue example to the green

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CourtNames = List.RemoveNulls(Record.ToList(Source{0})),
    CourtCount = List.Count(CourtNames),
    rws = List.Skip(Table.ToRows(Source),2),
    rws1 = List.Accumulate(rws, {}, (s,c)=> s & List.Generate(()=>0, (x)=> x<CourtCount, (x)=> x+1, (x)=> [Name = c{0}, Court = CourtNames{x}, Due = Date.From(c{x*2 +1}), Paid = Date.From(c{x*2+2})])),
    tbl = Table.FromRecords(rws1),
    tbl1 = Table.SelectRows(tbl, each [Paid] <> null or [Due] <> null)
in
    tbl1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9		Name	Court	Due	Paid
2		Court 1		Court 2		Court 3		Court 4			Mr. Blue	Court 1	1/1/2023	12/1/2022
3		due	paid	due	paid	due	paid	due	paid		Mr. Blue	Court 3	5/1/2023
4	Mr. Blue	1/1/2023	12/1/2022			5/1/2023					Mrs. Green	Court 2	3/1/2023
5	Mrs. Green			3/1/2023		7/1/2023		6/1/2023			Mrs. Green	Court 3	7/1/2023
6	Ms. Yellow							11/1/2023			Mrs. Green	Court 4	6/1/2023
7	Mr. Beige	7/1/2023				11/1/2023					Ms. Yellow	Court 4	11/1/2023
8											Mr. Beige	Court 1	7/1/2023
9											Mr. Beige	Court 3	11/1/2023
10
Sheet1