Not sure which forum to post this in. I have an issue I need to resolve, and have developed an extremely messy and time consuming way of implementing it. I am looking to streamline the process to enable faster and more frequent updates, and was hoping someone might be able to help me.

I have a long list of "areas" (ie Area 1, Area 2, 3, 4, etc all the way to 9000). Each area has a certain number of languages assigned to it, anywhere from 1 - 7. Each area can be listed a random number of times, ie Area 1 can be listed 20 times (so the total list has over 30,000 records). Each time an area is listed, it can have different languages attached to it. Example:

(each semi-colon indicates a new column)

Area 1 ; English ; French ; Italian ; German ; Farsi
Area 1 ; English
Area 1 ; French ; Hindi
Area 1 ; French ; Italian ; Tagalog
Area 2 ; English
Area 2 ; American Sign Language
Area 3 ; French

What I am trying to do, is have a final list of all areas listed just ONCE, and any languages that appear 2 or more times for each area. Example, if English appears twice or more for Area 1, it would be listed, but if Italian only appears once, it won't be:

Area 1 ; English ; French
Area 2 ; Hindi ;
Area 3 ; French

I can use excel/access/vba anything. I am eager to hear your ideas! Let me know if you need more clarification as this was hard to explain.

Thank you!!

I would start out by using SQL to create a table with just 2 columns: Area & Language

    SELECT DISTINCT Area, Language1 as Language into <newtable> FROM <oldtable>

    and then for <x> = 2..7:

    INSERT INTO <newtable> (Area, Language)
    SELECT DISTINCT Area, Language<x> as Language FROM <oldtable>

From here you can create yet another table with the area and 7 columns for languages and
build a record for each area by going thru the sorted list (by area) of records in <newtable>.

      -Phil-