TerryChristiansohn
New Member
- Joined
- Feb 11, 2013
- Messages
- 11
Hello. I only know how to record macro's; nothing else, so I cannot even call myself a newbie. But I need help with data that comes to me all jumbled up. The example below is greatly simplified.
In reality, I need to sort out nearly 600,000 rows of data. Each data row contains 28 column records that need to be sorted within that row. The data is divided into worksheets of up to 25,000 rows each. Functions for this many cells kill my computer's memory, so I would really like to have a macro, or a series of macros to accomplish my task.
1. Each farmer grows certain types of fruit. The farmer's and the farmer's country is in one row; that same row also contains the fruit that the farmer grows.
2. The the types of fruit that farmer grows arrives to me scrambled as per its category.
3. Each fruit has its own category: cherries, berries, citrus, Stone Fruit, apples, melons. Happily, you can tell which category each fruit belongs to, for example, each apple variety is preceded by the string "apples:" in the cell.
4. The fruit needs to be sorted into the correct columns as per the row headers.
5. The fruit needs to stay in the same row, so that we always know which farmer grows what fruit, and which country that fruit has been grown in.
6. The index number, the farmer's name and the country must remain in columns A, B, C.
So this is an example that will, hopefully, clarify my problem.
This is how the data arrives:
<tbody>
</tbody>
<tbody>
</tbody> Thank you.
In reality, I need to sort out nearly 600,000 rows of data. Each data row contains 28 column records that need to be sorted within that row. The data is divided into worksheets of up to 25,000 rows each. Functions for this many cells kill my computer's memory, so I would really like to have a macro, or a series of macros to accomplish my task.
1. Each farmer grows certain types of fruit. The farmer's and the farmer's country is in one row; that same row also contains the fruit that the farmer grows.
2. The the types of fruit that farmer grows arrives to me scrambled as per its category.
3. Each fruit has its own category: cherries, berries, citrus, Stone Fruit, apples, melons. Happily, you can tell which category each fruit belongs to, for example, each apple variety is preceded by the string "apples:" in the cell.
4. The fruit needs to be sorted into the correct columns as per the row headers.
5. The fruit needs to stay in the same row, so that we always know which farmer grows what fruit, and which country that fruit has been grown in.
6. The index number, the farmer's name and the country must remain in columns A, B, C.
So this is an example that will, hopefully, clarify my problem.
This is how the data arrives:
| Index | Grower | Country | tropical | cherries | berries | citrus | Stone Fruit | apples | melons |
| 1 | Georgia Farms | Georgia | citrus: tangerine | tropical: mango | melons: cantaloupe | berries: Bilberry | stone:Apricots | cherries: Rainier | apples: gravenstein |
| 2 | Berries n' More | USA | berries: Cape Gooseberry | cherries: Lapin | citrus: tangelo | apples: golden delicious | tropical: banana | stone:Nectarines | melons: Casaba |
| 3 | Heathcliff Greenhouses Ltd | United Kingdom | tropical: starfruit | berries: Riberry | Peaches | citrus: kumquat | apples: fuji | cherries: Van | melons: Crenshaw |
| 4 | Innotec Growers | Canada | cherries: Bing | citrus: orange | apples: gala | tropical: passionfruit | melons: Honeydew | berries: Indian Gooseberry | stone:White peach |
| 5 | Best Organic | USA | berries: Riberry | citrus: lime | cherries: lambert | tropical: lichii | plum | melons: Juan Canary | apples: granny smith |
| 6 | Toutes Les Fruits | France | berries: Acai | melons: Orange-flesh Honeydew | apples: McIntosh | stone rune | tropical: longan | cherries: Skeena | citrus: grapefruit |
| 7 | Maccedonia di Fruita | Italy | apples: pippin | tropical: pineapple | cherries: Sweetheart | citrus: lemon | berries: Blueberry | melons: Pepino | |
| 8 | Delicious Tropic | Fiji | melons: Persian | tropical: coconut | berries: Black Berry | citrus: Bergamot orange | apples: pink lady | cherries: Queen Anne | |
| 9 | Small Farmers Club | New Zealand | cherries: Black | apples: braeburn | berries: Raspberry | melons: Santa Claus | tropical: cherimola | citrus: Cleopatra Mandarin | |
| 10 | Certified Organic Fruit Growers | USA | cherries: Choke | tropical: Rambutan | apples: Honeycrisp | melons: Seedless Watermelon | berries: Mulberry | citrus: Ponderosa lemon | |
| 11 | Master Gardeners | United Kingdom | tropical: guava | melons: Watermelon | berries: Strawberry | cherries: Maraschino | apples: Arkansas Black | citrus: Orangelo | |
| 12 | Mastricht | Neatherlands | citrus: Persian lime | cherries: Morello | berries: Red Mulberry | melons: Yellow-Flesh Watermelon | Tropical: orange papaya | ||
| 13 | Herbs de Bon Sante | France | melons: wintermelon | citrus: Palestine sweet lime | berries: Logan Berry | ||||
| 14 | Belle Tropicale | Tahiti | berries: Lingonberry | cherries: North Star | citrus: Pompia | ||||
| 15 | Oceana Farmers | New Zealand | citrus: kaffir lime | cherries: Spanish Cherry | berries: Huckleberry | ||||
| 16 | Mexico Fruitas | Mexico | citrus: Limetta | cherries: Tieton | berries: Goji berry | ||||
| 17 | Northlights Organic | Canada | berries: Elderberry | ||||||
| 18 | Eastern Seasonal Specialties | apples: Arkansas Black | stonerune | berries: Cranberry | cherries: tart |
<tbody>
</tbody>
This is how I need it to be sorted:
<tbody> </tbody> |
<tbody>
</tbody>
