christianbiker
Active Member
- Joined
- Feb 3, 2006
- Messages
- 365
Evening...
I'm not sure I will be able to explain this properly but here goes! I am using the following formula to merge data from a range in a row (N:X):
=TEXT(N3,"yy-mmm-dd")&" > "&"CVIR: "&O3&", "&P3&(IF(Q3>0,", Moving Violation - YES","")&(IF(R3>0,", OOS x "&R3,"")&(IF(S3>0,", Violations x "&S3,"")&(IF(T3>0,", Admin Issues x "&T3,"")&(IF(U3>0,", Significant Event - YES ","") &(IF(V3>0,", Charges - YES ","") &(IF(W3>0,", Impound - YES ","") &(IF(X3>0,", Plate Removal - YES ","")))))))))
This formula is in column Z on each row from 3:300 and returns same information past the word "Level" for each row however it should only display the relevant data. I have included a couple examples below. The first is what it shows when the data is added (which is wrong) and the 2nd example shows the proper information but I must go through each blank cell in that row and press "Delete" or "F2 + Enter". I can't figure out how to fix the formula. Any help would be appreciated.
Example 1 - WRONG:
Example 2 - CORRECT:
I'm not sure I will be able to explain this properly but here goes! I am using the following formula to merge data from a range in a row (N:X):
=TEXT(N3,"yy-mmm-dd")&" > "&"CVIR: "&O3&", "&P3&(IF(Q3>0,", Moving Violation - YES","")&(IF(R3>0,", OOS x "&R3,"")&(IF(S3>0,", Violations x "&S3,"")&(IF(T3>0,", Admin Issues x "&T3,"")&(IF(U3>0,", Significant Event - YES ","") &(IF(V3>0,", Charges - YES ","") &(IF(W3>0,", Impound - YES ","") &(IF(X3>0,", Plate Removal - YES ","")))))))))
This formula is in column Z on each row from 3:300 and returns same information past the word "Level" for each row however it should only display the relevant data. I have included a couple examples below. The first is what it shows when the data is added (which is wrong) and the 2nd example shows the proper information but I must go through each blank cell in that row and press "Delete" or "F2 + Enter". I can't figure out how to fix the formula. Any help would be appreciated.
Example 1 - WRONG:
Inspection Date | CVIR # | Insp Level | Moving | OOS | Violations | Admin | Significant Event | Charges | Impound | Plate Removal | ||
18-06-05 | EA01218770 | Level 1 | 1 | 18-Jun-05 > CVIR: EA01218770, Level 1, Moving Violation - YES, OOS x , Violations x 1, Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES | ||||||||
18-06-13 | EA01220800 | Level 2 | 1 | 18-Jun-13 > CVIR: EA01220800, Level 2, Moving Violation - YES, OOS x , Violations x , Admin Issues x 1, Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES | ||||||||
18-06-18 | EA01222080 | Level 2 | 1 | Yes | 18-Jun-18 > CVIR: EA01222080, Level 2, Moving Violation - YES, OOS x , Violations x , Admin Issues x 1, Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES | |||||||
18-07-06 | EA01225280 | Level 2 | 1 | Yes | 18-Jul-06 > CVIR: EA01225280, Level 2, Moving Violation - YES, OOS x , Violations x 1, Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES | |||||||
18-08-06 | EA01235338 | Level 1 | 1 | 1 | 2 | Yes | 18-Aug-06 > CVIR: EA01235338, Level 1, Moving Violation - YES, OOS x 1, Violations x 2, Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES | |||||
18-09-24 | EA01239438 | Level 2 | 1 | Yes | 18-Sep-24 > CVIR: EA01239438, Level 2, Moving Violation - YES, OOS x , Violations x , Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES |
Example 2 - CORRECT:
Inspection Date | CVIR # | Insp Level | Moving | OOS | Violations | Admin | Significant Event | Charges | Impound | Plate Removal | ||
18-06-05 | EA01218770 | Level 1 | 1 | 18-Jun-05 > CVIR: EA01218770, Level 1, Violations x 1 | ||||||||
18-06-13 | EA01220800 | Level 2 | 1 | 18-Jun-13 > CVIR: EA01220800, Level 2, Admin Issues x 1 | ||||||||
18-06-18 | EA01222080 | Level 2 | 1 | Yes | 18-Jun-18 > CVIR: EA01222080, Level 2, Admin Issues x 1, Charges - YES | |||||||
18-07-06 | EA01225280 | Level 2 | 1 | Yes | 18-Jul-06 > CVIR: EA01225280, Level 2, Violations x 1, Charges - YES | |||||||
18-08-06 | EA01235338 | Level 1 | 1 | 1 | 2 | Yes | 18-Aug-06 > CVIR: EA01235338, Level 1, Moving Violation - YES, OOS x 1, Violations x 2, Charges - YES | |||||
18-09-24 | EA01239438 | Level 2 | 1 | Yes | 18-Sep-24 > CVIR: EA01239438, Level 2, Moving Violation - YES, Charges - YES |