Standardizing Data in excel

[sreed39]

Long story, but will try to make the explanation brief. I work with an individual customer and provide information on a daily basis. normally we enter our info into a database and the customer imports into their own and then does what they want with the data. This particular customer (same corp, different location) did not want to incorporate those tools already available (they would have had to learn new systems). Consequently, they asked us to create a spreadsheet that would track the same things.

We test drive vehicles and then provide comments and information about all issues we encounter.

Here is the issue, there are somewhere in the neighborhood of 400-500 unique issues, but in the past each individual vehicle had a list of issues and an occurrence number of that issue based on the issue description. Now they would like us to standardize it sot hat issue 1 in truck A will be the same issue number across all trucks.

I have NO clue how to do this other than manually comparing each comment for each truck and seeing if they were the same then manually changing them. Given that the spreadsheet currently has over 20,000 rows of information. To do this manually might take me years and would basically be counter productive as new issues arise.

Does anyone know of a method to resolve this? A quick way to run some form of comparison that I could use (I am not VBA savvy, FWIW)?

Any help would be appreciated!

 

[CharlesH]

sreed39,

Perhaps you can provide a sample of you data. I do not think you will be able to upload. But, if possible a scenario of the data showing what you need.
Not sure if I will be able to help, but some forum member may have a answer.

 

[SpillerBD]

Sounds like they want data validation.
If the entries already exist, I would use Pivot Tables to help group like and sililiar items to help make the validation list. Use a VLOOKUP to make a cosistent refernence and then use that to lookup the new description. Then going forward, its the use of the validation list on the description.

 

[sreed39]

sreed39,

Perhaps you can provide a sample of you data. I do not think you will be able to upload. But, if possible a scenario of the data showing what you need.
Not sure if I will be able to help, but some forum member may have a answer.

Lets see if I can put a small copy of this here: Basically, I need to have the issue # across all trucks match with the description, so that if an issue is about 'headlights aimed too high' I can assign that a specific issue number, etc. This was an 'easy' parameter to search, but I have a couple of trucks with over 200 unique issue numbers

Truck Engine Program driver Time Odometer Current Date First Occurrence Occ. # Issue # Description

<colgroup><col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:4790;width:98pt" width="131"> <col style="mso-width-source:userset;mso-width-alt:4937;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:5522;width:113pt" width="151"> <col style="mso-width-source:userset;mso-width-alt:5156;width:106pt" width="141"> <col style="mso-width-source:userset;mso-width-alt:5449;width:112pt" width="149"> <col style="mso-width-source:userset;mso-width-alt:6034;width:124pt" width="165"> <col style="mso-width-source:userset;mso-width-alt:6619;width:136pt" width="181"> <col style="mso-width-source:userset;mso-width-alt:4864;width:100pt" width="133"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:12946;width:266pt" width="354"> </colgroup><tbody>
[TD="class: xl101, width: 106"]ZZ9996[/TD]
[TD="class: xl85, width: 131"]8.8[/TD]
[TD="class: xl87, width: 135"]RG12549[/TD]
[TD="class: xl91, width: 151"]McCarrick, Mike[/TD]
[TD="class: xl87, width: 141"]8:05pm[/TD]
[TD="class: xl92, width: 149"]60929.0[/TD]
[TD="class: xl86, width: 165"]2/28/2019[/TD]
[TD="class: xl88, width: 181"]3/10/2018[/TD]
[TD="class: xl89, width: 133"]14[/TD]
[TD="class: xl89, width: 80"]12[/TD]
[TD="class: xl95, width: 354"]5:00am At post trip the 'switch light' panel to the left of the driver side flickered three times. The engine was running with interior fan on, headlights on, domelight on. (Driver did indicate another instance of this occurring during the shift. After it flickeredd the three times there were no more occurrences during post trip inspection that the driver saw)[/TD]

[TD="class: xl101"]ZZ9996[/TD]
[TD="class: xl85"]8.8[/TD]
[TD="class: xl87"]RG12549[/TD]
[TD="class: xl91"]McCarrick, Mike[/TD]
[TD="class: xl87"]8:05pm[/TD]
[TD="class: xl92"]60887.0[/TD]
[TD="class: xl86"]2/28/2019[/TD]
[TD="class: xl88"]1/31/2019[/TD]
[TD="class: xl89"]5[/TD]
[TD="class: xl89"]139[/TD]
[TD="class: xl94, width: 354"]4:10am Low beam (headlights) are aimed too high (Driver stated that the low beams seem to point to where high beams should and that the high beams shine into the trees and not onto the road at all.) Picture sent[/TD]

[TD="class: xl101"]ZZ9997[/TD]
[TD="class: xl85"]8.8[/TD]
[TD="class: xl87"]RG13889[/TD]
[TD="class: xl91"]Mickles, Dennis[/TD]
[TD="class: xl87"]7:50pm[/TD]
[TD="class: xl92"]37225.8[/TD]
[TD="class: xl86"]2/28/2019[/TD]
[TD="class: xl93"]2/27/2019[/TD]
[TD="class: xl90"]1[/TD]
[TD="class: xl90"]71[/TD]
[TD="class: xl95, width: 354"]12:30am Driver indicated that visibility on road was very low with headlights on in low beams; when he pulled over to check faults, another truck driver pulled in behind him to let him know that his headlights were very dim and that other drivers were having difficulty seeing him (driver had been going in opposite direction and the other driver stopped to help out a fellow truck driver)[/TD]

[TD="class: xl101"]ZZ9997[/TD]
[TD="class: xl85"]8.8[/TD]
[TD="class: xl87"]RG13889[/TD]
[TD="class: xl91"]Mickles, Dennis[/TD]
[TD="class: xl87"]7:55pm[/TD]
[TD="class: xl92"]37647.0[/TD]
[TD="class: xl86"]3/1/2019[/TD]
[TD="class: xl93"]2/27/2019[/TD]
[TD="class: xl90"]2[/TD]
[TD="class: xl90"]71[/TD]
[TD="class: xl95, width: 354"]6:00am Driver stated that the headlights were working considerably better than the last time he drive the vehicle (he was the last one to drive it).[/TD]

[TD="class: xl101"]BP9818[/TD]
[TD="class: xl85"]8.8[/TD]
[TD="class: xl87"]RG12017[/TD]
[TD="class: xl91"]McCarrick, Mike[/TD]
[TD="class: xl87"]8:15pm[/TD]
[TD="class: xl92"]115504.0[/TD]
[TD="class: xl86"]3/1/2019[/TD]
[TD="class: xl88"]9/25/2017[/TD]
[TD="class: xl89"]28[/TD]
[TD="class: xl97"]26[/TD]
[TD="class: xl94, width: 354"]11:57pm While slowing to a stop (at less than 5 mph) lights to the left of the driver flickered once, headlights on, fan on, grade brake on, foot on service brake[/TD]

</tbody>