Tennisguuy
Well-known Member
- Joined
- Oct 17, 2007
- Messages
- 564
- Office Version
- 2016
- Platform
- Windows
I am trying to figure out the best way to only extract certain data from a download from our rating system. Below is an example of how the data is downloaded from the system
VEH 1 - 2000 INTERNATIONAL 4000 4900 STRAIGHT TRUCK {1HTSDAAN7YH256306}
City State Territory Stated Amount Cost New Class Code
Ewing NJ 106 44988 33489
Liability
Med Pay
No-Fault
UM/UIM
UM - BI-PD - CSL
Comp 2000
Collision 2000
I wanted to create a template where I copy the data from the worksheet that is downloaded form the system into sheet1 and on sheet2 use formulas to extract only the data I want. I need to extract the vehicle which is the above example will always be in row B. I used this formula on sheet2 in cells A2:A300 IF(LEN(Sheet1!B1)>35,Sheet1!B1,"") the length of the vehicle will vary but in will always be greater than 35 characters and no other data will be more than 35 characters and it's working but the problem in when I copy the formula down of course it leaves blank spaces because in the above example that is only one auto and I could have say 100 auto so it could. Once I extracted the vehicle I used filters to remove the spaces which is working but I was wondering if there is a formula I could use so that I don't have to use filters to remove the spaces.
If this is possible I would also like to exact the comp and collision deductible for each auto. The Comp and collision is in column E and the value of the deductible is in column K
In sheet 2 I have the following headings. Vehicle is in cell A1, comp is in cell B1 and collision is in cell C1. I have the formula above in cell A2A300 F(LEN(Sheet1!B1)>35,Sheet1!B1,"") which extracts the vehicle
Vehicle Comp Coll
Is there a formula I could put in sheet2 Cells A2:300 to extract the vehicles so I don't have to use filter to remove the space. I don't know if it's possible to have formulas to extract the comp and collision since they are located below each vehile in different columns but thought I would ask but that is gravy if it can be done.
VEH 1 - 2000 INTERNATIONAL 4000 4900 STRAIGHT TRUCK {1HTSDAAN7YH256306}
City State Territory Stated Amount Cost New Class Code
Ewing NJ 106 44988 33489
Liability
Med Pay
No-Fault
UM/UIM
UM - BI-PD - CSL
Comp 2000
Collision 2000
I wanted to create a template where I copy the data from the worksheet that is downloaded form the system into sheet1 and on sheet2 use formulas to extract only the data I want. I need to extract the vehicle which is the above example will always be in row B. I used this formula on sheet2 in cells A2:A300 IF(LEN(Sheet1!B1)>35,Sheet1!B1,"") the length of the vehicle will vary but in will always be greater than 35 characters and no other data will be more than 35 characters and it's working but the problem in when I copy the formula down of course it leaves blank spaces because in the above example that is only one auto and I could have say 100 auto so it could. Once I extracted the vehicle I used filters to remove the spaces which is working but I was wondering if there is a formula I could use so that I don't have to use filters to remove the spaces.
If this is possible I would also like to exact the comp and collision deductible for each auto. The Comp and collision is in column E and the value of the deductible is in column K
In sheet 2 I have the following headings. Vehicle is in cell A1, comp is in cell B1 and collision is in cell C1. I have the formula above in cell A2A300 F(LEN(Sheet1!B1)>35,Sheet1!B1,"") which extracts the vehicle
Vehicle Comp Coll
Is there a formula I could put in sheet2 Cells A2:300 to extract the vehicles so I don't have to use filter to remove the space. I don't know if it's possible to have formulas to extract the comp and collision since they are located below each vehile in different columns but thought I would ask but that is gravy if it can be done.
