Hello, I have a serious excel difficulty. It is also very complex to explain, so I will do my best. I have two separate spread sheets of data relating to cars. One (D1) is a list all vehicles from 1984-2009 and their respective EPA segment, equating to roughly 28,000 entries. Many car names are repeated year after year, but are different only in their respective year, however this can make them distinct if the car has switched EPA segments over the vehicle's lifetime, ie.
1985 = Honda Civic FWD = subcompact car; 2005 = Honda Civic FWD = compact car
The second set of data (D2) is the vehicles name, its segment, and volume of sales for a given year from 1975-2007. However, the major difficulty I have is the Segments are not under the same title. I would like to replace the segment titles of D2 with EPA segments from D1. This is difficult because segments D1 are broken out by vehicle size, segments D2 are by size and price. There is no direct correlation between the two segmentations. So, the best way to do this is by matching up the vehicle name's from D1 & D2 and then doing a vlookup function to get its EPA segment from D1. Yet this becomes difficult because the names don't match up either. D1 might say "Mercedes-Benz SLK 350" and D2 reads "Mercedes SLK" These are the same car, but the names are different. If I perform a Vlookup, and set it as an approximate match, it will equate cars like Mercedes-Benz SLK with Mercedes CLK, which are completely different model lines. Is there any other way I can match up the vehicle names? I'd do it manually, except we're dealing with 28,000 rows for D1 and 9,000 rows for D2. That might take me till January of 2011 to finish. I hope you can help!
1985 = Honda Civic FWD = subcompact car; 2005 = Honda Civic FWD = compact car
The second set of data (D2) is the vehicles name, its segment, and volume of sales for a given year from 1975-2007. However, the major difficulty I have is the Segments are not under the same title. I would like to replace the segment titles of D2 with EPA segments from D1. This is difficult because segments D1 are broken out by vehicle size, segments D2 are by size and price. There is no direct correlation between the two segmentations. So, the best way to do this is by matching up the vehicle name's from D1 & D2 and then doing a vlookup function to get its EPA segment from D1. Yet this becomes difficult because the names don't match up either. D1 might say "Mercedes-Benz SLK 350" and D2 reads "Mercedes SLK" These are the same car, but the names are different. If I perform a Vlookup, and set it as an approximate match, it will equate cars like Mercedes-Benz SLK with Mercedes CLK, which are completely different model lines. Is there any other way I can match up the vehicle names? I'd do it manually, except we're dealing with 28,000 rows for D1 and 9,000 rows for D2. That might take me till January of 2011 to finish. I hope you can help!