Challenge #47: Vehicle Identification Number Test

[SueDonim]

Interesting exercise.  Not difficult, but a lot more involved than I would have expected.  Learned more than a bit about VINs (and check sums) as well.

3 of 6 were not valid (i.e. calculated check sum did not match position 9 check sum in VIN

Spoiler

As usual, guessing this was not the most efficient way to solve, but looking forward to others ideas.

Process:
- Add Record ID (to enable simplified calcs and rejoin data)
- Split each character of VIN into a row (RegEx on "."0
- Multi-Row to determine character position in VIN
- Find and Replace to join VIN Letter values
- Formula to pull forward numeric values for numbers (since Find and Replace only captured letters and otherwise left Null)
- Find and Replace to pull forward VIN Position Weights
- Formula to calculate Product of Value * Weight
- Summarize to add values grouped on Record ID
- Formula to calculate modulus (when dividing by 11), and convert to "X" if modulus = 10
- Filter to grab value of Position 9 (the check sum)
- Join to combine check sum and calculated check sum value
- Join to grab VIN data and combine with the two check sums from immediately above
- Sort for the win

[RoDO]

My solution

Spoiler

[Jia_Li]

Took 2 hours to build this... at the end I had to look at the solution to find out about the Modulo formula. Very interesting. 

[justindavis]

Spoiler

[PhilBalderson]

I loved this challenge. Decoding the wiki entry was the first challenge, and learning new skills to complete. Thanks! 

[Jonathan-Sherman]

Challenge 47 is done!

Spoiler

[JamesCameron]

My Effort

Spoiler

[sarahjohnson]

Spoiler

[danicahui]

Spoiler