FM - Efficient way of Displaying Section, Block, & Lot from Repeating Fields
Below is a case statement I wrote to list the section, block, and lot in our rental agreements. First, it is not very efficient, but second, I need to not show Section if it is the same as the previous repetition. For example, Section, Block, and Lot for a property might look like this:
Section Block Lot
56 352 6
56 352 7
57 353 2
57 353 3
58 200 1
How would I change the statement below so that the repetition of section does not show up. The above output should read, "Section 56, Block 352, Lots 6,7; Section 57, Block 353, Lots 2,3; Section 58, Block 200, Lot 1.
I could write an IF statement that If Section[2] = Section[1], then leave Section[2] out, but note below how many times I would have to do this. Is there a simpler way than about a hundred IF statements?
Case (
not IsEmpty ( Section[10]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7] & "; " &
"Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " & Lot[8] & "; " &
"Section " & Section[9] & ", Block " & Block[9] & ", " & "Lot " & Lot[9] & "; " &
"Section " & Section[10] & ", Block " & Block[10] & ", " & "Lot " & Lot[10]
;
not IsEmpty ( Section[9]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7] & "; " &
"Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " & Lot[8] & "; " &
"Section " & Section[9] & ", Block " & Block[9] & ", " & "Lot " & Lot[9]
;
not IsEmpty ( Section[8]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7] & "; " &
"Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " & Lot[8]
;
not IsEmpty ( Section[7]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7]
;
not IsEmpty ( Section[6]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6]
;
not IsEmpty ( Section[5]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5]
;
not IsEmpty ( Section[4]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4]
;
not IsEmpty ( Section[3]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3]
;
not IsEmpty ( Section[2]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2]
;
" (Section " & Section & ", Block " & Block & ", " & "Lot " &
If ( not IsEmpty ( Lot[1] ); Lot[1]; "" ) &
If ( not IsEmpty ( Lot[2] ); "," & Lot[2]; "" ) &
If ( not IsEmpty ( Lot[3] ); "," & Lot[3]; "" ) &
If ( not IsEmpty ( Lot[4] ); "," & Lot[4]; "" ) &
If ( not IsEmpty ( Lot[5] ); "," & Lot[5]; "" ) &
If ( not IsEmpty ( Lot[6] ); "," & Lot[6]; "" ) &
If ( not IsEmpty ( Lot[7] ); "," & Lot[7]; "" ) &
If ( not IsEmpty ( Lot[8] ); "," & Lot[8]; "" ) &
If ( not IsEmpty ( Lot[9] ); "," & Lot[9]; "" ) &
If ( not IsEmpty ( Lot[10] ); "," & Lot[10]; "" )
)
Section Block Lot
56 352 6
56 352 7
57 353 2
57 353 3
58 200 1
How would I change the statement below so that the repetition of section does not show up. The above output should read, "Section 56, Block 352, Lots 6,7; Section 57, Block 353, Lots 2,3; Section 58, Block 200, Lot 1.
I could write an IF statement that If Section[2] = Section[1], then leave Section[2] out, but note below how many times I would have to do this. Is there a simpler way than about a hundred IF statements?
Case (
not IsEmpty ( Section[10]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7] & "; " &
"Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " & Lot[8] & "; " &
"Section " & Section[9] & ", Block " & Block[9] & ", " & "Lot " & Lot[9] & "; " &
"Section " & Section[10] & ", Block " & Block[10] & ", " & "Lot " & Lot[10]
;
not IsEmpty ( Section[9]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7] & "; " &
"Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " & Lot[8] & "; " &
"Section " & Section[9] & ", Block " & Block[9] & ", " & "Lot " & Lot[9]
;
not IsEmpty ( Section[8]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7] & "; " &
"Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " & Lot[8]
;
not IsEmpty ( Section[7]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6] & "; " &
"Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " & Lot[7]
;
not IsEmpty ( Section[6]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5] & "; " &
"Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " & Lot[6]
;
not IsEmpty ( Section[5]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4] & "; " &
"Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " & Lot[5]
;
not IsEmpty ( Section[4]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3] & "; " &
"Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " & Lot[4]
;
not IsEmpty ( Section[3]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2] & "; " &
"Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " & Lot[3]
;
not IsEmpty ( Section[2]);
" (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " & Lot[1] & "; " &
"Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " & Lot[2]
;
" (Section " & Section & ", Block " & Block & ", " & "Lot " &
If ( not IsEmpty ( Lot[1] ); Lot[1]; "" ) &
If ( not IsEmpty ( Lot[2] ); "," & Lot[2]; "" ) &
If ( not IsEmpty ( Lot[3] ); "," & Lot[3]; "" ) &
If ( not IsEmpty ( Lot[4] ); "," & Lot[4]; "" ) &
If ( not IsEmpty ( Lot[5] ); "," & Lot[5]; "" ) &
If ( not IsEmpty ( Lot[6] ); "," & Lot[6]; "" ) &
If ( not IsEmpty ( Lot[7] ); "," & Lot[7]; "" ) &
If ( not IsEmpty ( Lot[8] ); "," & Lot[8]; "" ) &
If ( not IsEmpty ( Lot[9] ); "," & Lot[9]; "" ) &
If ( not IsEmpty ( Lot[10] ); "," & Lot[10]; "" )
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have some time today, so if you want to send me the database with enough info to be able to help, I would not mind taking a crack at it for you.
ASKER
Yes, I've converted several other repeating fields into a sub-table but have avoided this one for a while. Looks like it's finally necessary. Thanks. I like the solution.
ASKER