Row sorting problem.Need to place row data into correct columns

[TerryChristiansohn]

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.

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 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. 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.


[TABLE="width: 959"]
<colgroup><col width="131"><col width="132"><col width="150"><col width="142"><col width="86"><col width="134"><col width="184"></colgroup><tbody>[TR]
[TD="class: xl22, width: 184"][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

TerryChristiansohn,

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker
Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.


If posting VBA code, please use Code Tags - like this:

[code]

'Paste your code here.

[/code]

 

[TerryChristiansohn]

I am using Excel 2000 (very old version, old computer)

 

[TerryChristiansohn]

Here is a link to a copy of the Excel workbook.

1. The first worksheet is the data as it arrives.
2. The second worksheet shows the data properly arranged so that the data stays in the same row to preserve the growers' data accurately, and the fruit is properly categorized

https://www.box.com/s/ycmnye84vabynhjxwmqr

Thank you.

 

[hiker95]

TerryChristiansohn,

Thanks for the workbook.


Sample raw data in the first (most left worksheet) in the workbook (notice cell H6 has no leading identifier):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Index	Grower	Country	tropical	cherries	berries	citrus	Stone Fruit	apples	melons
2	1	Georgia Farms	Georgia	citrus: tangerine	tropical: mango	melons: cantaloupe	berries: Bilberry	stone:Apricots	cherries: Rainier	apples: gravenstein
3	2	Berries n' More	USA	berries: Cape Gooseberry	cherries: Lapin	citrus: tangelo	apples: golden delicious	tropical: banana	stone:Nectarines	melons: Casaba
4	3	Heathcliff Greenhouses Ltd	United Kingdom	tropical: starfruit	berries: Riberry	Peaches	citrus: kumquat	apples: fuji	cherries: Van	melons: Crenshaw
5	4	Innotec Growers	Canada	cherries: Bing	citrus: orange	apples: gala	tropical: passionfruit	melons: Honeydew	berries: Indian Gooseberry	stone:White peach
6	5	Best Organic	USA	berries: Riberry	citrus: lime	cherries: lambert	tropical: lichii	plum	melons: Juan Canary	apples: granny smith
7	6	Toutes Les Fruits	France	berries: Acai	melons: Orange-flesh Honeydew	apples: McIntosh	stone:prune	tropical: longan	cherries: Skeena	citrus: grapefruit
8	7	Maccedonia di Fruita	Italy	apples: pippin	tropical: pineapple	cherries: Sweetheart		citrus: lemon	berries: Blueberry	melons: Pepino
9	8	Delicious Tropic	Fiji	melons: Persian	tropical: coconut	berries: Black Berry	citrus: Bergamot orange	apples: pink lady		cherries: Queen Anne
10	9	Small Farmers Club	New Zealand		cherries: Black	apples: braeburn	berries: Raspberry	melons: Santa Claus	tropical: cherimola	citrus: Cleopatra Mandarin
11	10	Certified Organic Fruit Growers	USA	cherries: Choke	tropical: Rambutan	apples: Honeycrisp	melons: Seedless Watermelon		berries: Mulberry	citrus: Ponderosa lemon
12	11	Master Gardeners	United Kingdom	tropical: guava	melons: Watermelon	berries: Strawberry	cherries: Maraschino		apples: Arkansas Black	citrus: Orangelo
13	12	Mastricht	Neatherlands	citrus: Persian lime	cherries: Morello	berries: Red Mulberry		melons: Yellow-Flesh Watermelon		Tropical: orange papaya
14	13	Herbs de Bon Sante	France	melons: wintermelon			citrus: Palestine sweet lime	berries: Logan Berry
15	14	Belle Tropicale	Tahiti	berries: Lingonberry		cherries: North Star			citrus: Pompia
16	15	Oceana Farmers	New Zealand				citrus: kaffir lime	cherries: Spanish Cherry	berries: Huckleberry
17	16	Mexico Fruitas	Mexico					citrus: Limetta	cherries: Tieton	berries: Goji berry
18	17	Northlights Organic	Canada						berries: Elderberry
19	18	Eastern Seasonal Specialties		apples: Arkansas Black	stone:prune	berries: Cranberry			cherries: tart
20
Original Scrambled Data

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Index	Grower	Country	tropical	cherries	berries	citrus	Stone Fruit	apples	melons
2	1	Georgia Farms	Georgia	tropical: mango	cherries: Rainier	berries: Bilberry	citrus: tangerine	stone:Apricots	apples: gravenstein	melons: cantaloupe
3	2	Berries n' More	USA	tropical: banana	cherries: Lapin	berries: Cape Gooseberry	citrus: tangelo	stone:Nectarines	apples: golden delicious	melons: Casaba
4	3	Heathcliff Greenhouses Ltd	United Kingdom	tropical: starfruit	cherries: Van	berries: Riberry	citrus: kumquat		apples: fuji	melons: Crenshaw
5	4	Innotec Growers	Canada	tropical: passionfruit	cherries: Bing	berries: Indian Gooseberry	citrus: orange	stone:White peach	apples: gala	melons: Honeydew
6	5	Best Organic	USA	tropical: lichii	cherries: lambert	berries: Riberry	citrus: lime		apples: granny smith	melons: Juan Canary
7	6	Toutes Les Fruits	France	tropical: longan	cherries: Skeena	berries: Acai	citrus: grapefruit	stone:prune	apples: McIntosh	melons: Orange-flesh Honeydew
8	7	Maccedonia di Fruita	Italy	tropical: pineapple	cherries: Sweetheart	berries: Blueberry	citrus: lemon		apples: pippin	melons: Pepino
9	8	Delicious Tropic	Fiji	tropical: coconut	cherries: Queen Anne	berries: Black Berry	citrus: Bergamot orange		apples: pink lady	melons: Persian
10	9	Small Farmers Club	New Zealand	tropical: cherimola	cherries: Black	berries: Raspberry	citrus: Cleopatra Mandarin		apples: braeburn	melons: Santa Claus
11	10	Certified Organic Fruit Growers	USA	tropical: Rambutan	cherries: Choke	berries: Mulberry	citrus: Ponderosa lemon		apples: Honeycrisp	melons: Seedless Watermelon
12	11	Master Gardeners	United Kingdom	tropical: guava	cherries: Maraschino	berries: Strawberry	citrus: Orangelo		apples: Arkansas Black	melons: Watermelon
13	12	Mastricht	Neatherlands	Tropical: orange papaya	cherries: Morello	berries: Red Mulberry	citrus: Persian lime			melons: Yellow-Flesh Watermelon
14	13	Herbs de Bon Sante	France			berries: Logan Berry	citrus: Palestine sweet lime			melons: wintermelon
15	14	Belle Tropicale	Tahiti		cherries: North Star	berries: Lingonberry	citrus: Pompia
16	15	Oceana Farmers	New Zealand		cherries: Spanish Cherry	berries: Huckleberry	citrus: kaffir lime
17	16	Mexico Fruitas	Mexico		cherries: Tieton	berries: Goji berry	citrus: Limetta
18	17	Northlights Organic	Canada			berries: Elderberry
19	18	Eastern Seasonal Specialties			cherries: tart	berries: Cranberry		stone:prune	apples: Arkansas Black
20
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 02/12/2013
' http://www.mrexcel.com/forum/excel-questions/685028-row-sorting-problem-need-place-row-data-into-correct-columns.html
Dim w1 As Worksheet, wR As Worksheet
Dim a As Variant, b As Variant
Dim r As Long, c As Long, lr As Long, lc As Long
Set w1 = Worksheets(1)
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
lc = w1.Cells(1, Columns.Count).End(xlToLeft).Column
a = w1.Range(Cells(1, 1), Cells(lr, lc))
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
b(1, 1) = a(1, 1): b(1, 2) = a(1, 2): b(1, 3) = a(1, 3): b(1, 4) = a(1, 4)
b(1, 5) = a(1, 5): b(1, 6) = a(1, 6): b(1, 7) = a(1, 7): b(1, 8) = a(1, 8)
b(1, 9) = a(1, 9): b(1, 10) = a(1, 10)
For r = 2 To lr Step 1
  b(r, 1) = a(r, 1): b(r, 2) = a(r, 2): b(r, 3) = a(r, 3)
  For c = 4 To lc Step 1
    If a(r, c) <> "" Then
      Select Case Left(LCase(a(r, c)), 2)
        Case "tr"   '4
          b(r, 4) = a(r, c)
        Case "ch"   '5
          b(r, 5) = a(r, c)
        Case "be"   '6
          b(r, 6) = a(r, c)
        Case "ci"   '7
          b(r, 7) = a(r, c)
        Case "st"   '8
          b(r, 8) = a(r, c)
        Case "ap"   '9
          b(r, 9) = a(r, c)
        Case "me"   '10
          b(r, 10) = a(r, c)
      End Select
    End If
  Next c
Next r
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range(Cells(1, 1), Cells(lr, lc)) = b
wR.Cells.EntireColumn.AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

 

[TerryChristiansohn]

Thank you so much! I will try it later today and let you know if it works. And thank you for catching the mistake on the lack of header identifier for the "prune" on cell c6.

 

[TerryChristiansohn]

Wow !! You are a genius! It worked so well, so quickly. A split second and it was DONE!!
I was wondering: on the original Excel file, the cells were colored to correspond with their category (tropical, berries, apples, etc). Can the macro you wrote be modified to preserve the color? Reason I am asking is that my vision, even though I wear eyeglasses, is not very strong, and having the colors to look at helps me to identify the cell I need to focus my attention on. Thank you again. I so appreciate the effort and care you put into this.

 

[hiker95]

TerryChristiansohn,

Can the macro you wrote be modified to preserve the color?

Here you go.


After the updated macro:

Excel Workbook
	A	B	C	D	E
1	Index	Grower	Country	tropical	cherries
2	1	Georgia Farms	Georgia	tropical: mango	cherries: Rainier
3	2	Berries n' More	USA	tropical: banana	cherries: Lapin
4	3	Heathcliff Greenhouses Ltd	United Kingdom	tropical: starfruit	cherries: Van
5	4	Innotec Growers	Canada	tropical: passionfruit	cherries: Bing
6	5	Best Organic	USA	tropical: lichii	cherries: lambert
7	6	Toutes Les Fruits	France	tropical: longan	cherries: Skeena
8	7	Maccedonia di Fruita	Italy	tropical: pineapple	cherries: Sweetheart
9	8	Delicious Tropic	Fiji	tropical: coconut	cherries: Queen Anne
10	9	Small Farmers Club	New Zealand	tropical: cherimola	cherries: Black
11	10	Certified Organic Fruit Growers	USA	tropical: Rambutan	cherries: Choke
12	11	Master Gardeners	United Kingdom	tropical: guava	cherries: Maraschino
13	12	Mastricht	Neatherlands	Tropical: orange papaya	cherries: Morello
14	13	Herbs de Bon Sante	France
15	14	Belle Tropicale	Tahiti		cherries: North Star
16	15	Oceana Farmers	New Zealand		cherries: Spanish Cherry
17	16	Mexico Fruitas	Mexico		cherries: Tieton
18	17	Northlights Organic	Canada
19	18	Eastern Seasonal Specialties			cherries: tart
20
Results

Excel Workbook
	F	G	H	I	J
1	berries	citrus	Stone Fruit	apples	melons
2	berries: Bilberry	citrus: tangerine	stone:Apricots	apples: gravenstein	melons: cantaloupe
3	berries: Cape Gooseberry	citrus: tangelo	stone:Nectarines	apples: golden delicious	melons: Casaba
4	berries: Riberry	citrus: kumquat		apples: fuji	melons: Crenshaw
5	berries: Indian Gooseberry	citrus: orange	stone:White peach	apples: gala	melons: Honeydew
6	berries: Riberry	citrus: lime		apples: granny smith	melons: Juan Canary
7	berries: Acai	citrus: grapefruit	stone:prune	apples: McIntosh	melons: Orange-flesh Honeydew
8	berries: Blueberry	citrus: lemon		apples: pippin	melons: Pepino
9	berries: Black Berry	citrus: Bergamot orange		apples: pink lady	melons: Persian
10	berries: Raspberry	citrus: Cleopatra Mandarin		apples: braeburn	melons: Santa Claus
11	berries: Mulberry	citrus: Ponderosa lemon		apples: Honeycrisp	melons: Seedless Watermelon
12	berries: Strawberry	citrus: Orangelo		apples: Arkansas Black	melons: Watermelon
13	berries: Red Mulberry	citrus: Persian lime			melons: Yellow-Flesh Watermelon
14	berries: Logan Berry	citrus: Palestine sweet lime			melons: wintermelon
15	berries: Lingonberry	citrus: Pompia
16	berries: Huckleberry	citrus: kaffir lime
17	berries: Goji berry	citrus: Limetta
18	berries: Elderberry
19	berries: Cranberry		stone:prune	apples: Arkansas Black
20
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataV2()
' hiker95, 02/12/2013
' http://www.mrexcel.com/forum/excel-questions/685028-row-sorting-problem-need-place-row-data-into-correct-columns.html
Dim w1 As Worksheet, wR As Worksheet
Dim a As Variant, b As Variant
Dim r As Long, c As Long, lr As Long, lc As Long
Application.ScreenUpdating = False
Set w1 = Worksheets(1)
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
lc = w1.Cells(1, Columns.Count).End(xlToLeft).Column
a = w1.Range(Cells(1, 1), Cells(lr, lc))
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
b(1, 1) = a(1, 1): b(1, 2) = a(1, 2): b(1, 3) = a(1, 3): b(1, 4) = a(1, 4)
b(1, 5) = a(1, 5): b(1, 6) = a(1, 6): b(1, 7) = a(1, 7): b(1, 8) = a(1, 8)
b(1, 9) = a(1, 9): b(1, 10) = a(1, 10)
For r = 2 To lr Step 1
  b(r, 1) = a(r, 1): b(r, 2) = a(r, 2): b(r, 3) = a(r, 3)
  For c = 4 To lc Step 1
    If a(r, c) <> "" Then
      Select Case Left(LCase(a(r, c)), 2)
        Case "tr"   '4
          b(r, 4) = a(r, c)
        Case "ch"   '5
          b(r, 5) = a(r, c)
        Case "be"   '6
          b(r, 6) = a(r, c)
        Case "ci"   '7
          b(r, 7) = a(r, c)
        Case "st"   '8
          b(r, 8) = a(r, c)
        Case "ap"   '9
          b(r, 9) = a(r, c)
        Case "me"   '10
          b(r, 10) = a(r, c)
      End Select
    End If
  Next c
Next r
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range(Cells(1, 1), Cells(lr, lc)) = b
wR.Cells.EntireColumn.AutoFit
With wR.Range("A1").Resize(, lc)
  .Interior.ColorIndex = 43
  .Font.Underline = xlUnderlineStyleSingle
End With
For r = 2 To lr Step 1
  If wR.Cells(r, 4) <> "" Then wR.Cells(r, 4).Interior.ColorIndex = 40
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 5) <> "" Then wR.Cells(r, 5).Interior.ColorIndex = 7
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 6) <> "" Then wR.Cells(r, 6).Interior.ColorIndex = 39
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 7) <> "" Then wR.Cells(r, 7).Interior.ColorIndex = 6
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 8) <> "" Then wR.Cells(r, 8).Interior.ColorIndex = 40
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 9) <> "" Then wR.Cells(r, 9).Interior.ColorIndex = 45
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 10) <> "" Then wR.Cells(r, 10).Interior.ColorIndex = 35
Next r
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV2 macro.

 

[TerryChristiansohn]

Wow! It worked so perfectly! You are totally amazing. Thank you!
I was wondering if a macro is extensible. Actually, I don't even know if "extensible" is the correct word to describe this hypothetical situation:

1. A certain worksheet for charitable contributions contains only five columns containing data that needs to be categorized into its correct rows.
2. A certain worksheet for health data has 30 columns containing data that needs to be categorized into its correct rows.
3. Another hypothetical worksheet contains x number of rows and y number of columns.

Each of the above worksheets have a HUGE number of rows that (at least 50,000), so the macro would need to iterate until the last row is processed. I will upload a real-life example after I get home from work.

Again, thank you. This is a real eye-opener for me.

 

[hiker95]

TerryChristiansohn,

Thanks for the feedback.

You are very welcome. Glad I could help.

Looking forward to hearing from you later.