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.

<colgroup><col width="131"><col width="132"><col width="150"><col width="142"><col width="86"><col width="134"><col width="184"></colgroup><tbody>
</tbody>

 

[TerryChristiansohn]

Hi, here is the HUGE spreadsheet example that my aunt asked me to help her sort. I think she wants to make dog family trees for a variety of breeds.

She has up to 600,000 records per breed. She provided me with .csv's, and I can use an add in to make them into colored multiple Excel worksheets, and that takes me a long time. But arranging that many records into appropriate categories without making mistakes has been impossible for me to accomplish.

1. Columns A to M arrive in good order
2. Columns O to AD are completely jumbled. Like the example from the fruit spreadsheet, each dog's data needs to stay in its original row, but the data needs to go into its correct category.

And there are 25,000 to 50,000 rows per worksheet, so I need a macro that will continue working until the last row is done. The number of rows per worksheet vary.

This is the link to the first few rows of the worksheet (all names and other data is fictitious):

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

Thank you.

 

[hiker95]

TerryChristiansohn,


Thanks for the workbook.


After the newest macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Page	Breed	Title	Image	Call name	Sex	NAME	Sire	Dam	Paternal Grandsire	Paternal Granddam	Maternal Grandsire	Maternal Granddam
2	1	Spitz	Am. CH. Asta Minnow OD	[url]http://www.flickr/Asta[/url] Minnow.jpg	"Asta"	Gender:F	Asta Minnow	Am. CH. Marais OS	BISS BIS Am Can CH Sterling Girl OD SDHF	Am./Can./Mex. CH. Colonel Perry Am./Can. CD OS SDHF	Am. CH. Ginger the Pooh	BIS Am. CH Sunrise in the East CD OS SDHF	Eliesa OD
3	2	Spitz	Ch Sally the Swift VCD2 UD RE TD MX MXJ MXP MJP OS SDHF	[url]http://www.flickr/Sally[/url] the Swift.jpg	"Sally"	Gender:M	Sally the Swift	BIS BISS CH Tante Moonlight SDHF	Salyran NWM Blond Fast Streak	CH Mommy's Beauty Boy Effort OS SDHF	Am. CH Girlie my Pet	Saharo CD OS	Am. Krinolyn
4	3	Spitz	BIS BISS Am Can CH Kelly the Good Pooch CD JH WC VCX OS SDHF	[url]http://www.flickr/Kelly[/url] the Good Pooch.jpg	"Kelly"	Gender:M	Kelly the Good Pooch	BIS BISS Am/Can CH Rush More CDX JH AX OAJ WCX VCX OS SDHF Can. CD WC	Am. CH Gldn Lt Yumie OD	Am. CH Flame's Speed CD WC VC OS	Am./Can. CH Odine	BISS Am CH Oster Barrie OS SDHF	Am./Can. Chiobanni
5	4	Spitz	Tangel Truelove CDX	[url]http://www.flickr/Tango[/url] Truelove.jpg	"Tanglow"	Gender:F	Tango Truelove	Loman Reislie CDX	Meg the Mahogany	Am. CH. Christmas Boy OS	Rackham der Niebling	Sulier the Quick CD OS SDHF	Candy Crystal
6	5	Spitz	SkyHii Tiarra CD	[url]http://www.flickr/SkyHii[/url] Tiarra.jpg	"Tiarra"	Gender:F	SkyHii Tiarra	Elesio UDT MH ** WCX VCX DDHF OS	Sweetabee Baby CD TD JH WCX OD	Am. CH. Springwater Jumper CDX WC OS	CH. Ease Sur la Mer UD TDX JH	Am./Can. CH. Johnny Waterford OS SDHF	Am Ch Silver Sweet Streak
7	6	Spitz	Top Dog on the Heath CD MH WCX	[url]http://www.flickr/Top[/url] Dog on the Heath.jpg	"Heath"	Gender:M	Top Dog on the Heath	Midman Pareil UD SH WCX **	Applemirth WC OD	Prince Matchabello	Moonshine of Minnesota	Whirlwind of Gold CDX JH WCX	Ramie Girl
8	7	Spitz	Boogie Woogie Bugle Pup CDX	[url]http://www.flickr/Boogie[/url] Woogie Bugle Pup .jpg	"Buglo"	Gender:F	Boogie Woogie Bugle Pup	Renaissance Guy Dog CD	Kernie CDX	Am./Can. CH. Kiss the Blarneystone Am./Can. CD OS	Can Ch Miss Matilda UD JH WCX OD	Molto Mari CDX	Melindee the Sweet
9	8	Spitz	Am Ch Gilly Boy TDX, MH, OA	[url]http://www.flickr/Gilly[/url] Boy.jpg	"Gill"	Gender:M	Gilly Boy	Am./Can. CH. Westminister CDX TD MX AXJ OS	Am CH Big Boy Jumper MH UD TDX AX AXJ OAP OJP WCX VCX OD DDHF	Ch-OTCh Fields of Gold TD JH WCX VCX OS	Penny Princess	Andulasian Field's Bowser UDT MH ** WCX VCX DDHF OS	Am CH Miranda of the Honey CDX, SH, TD, MX, AXJ, RN, WCX, VCX, OD
10	9	Spitz	Caragold Horizon Flash SH WCX OD	[url]http://www.flickr/Horizon[/url] Flash.jpg	"Cara"	Gender:F	Horizon Flash	BISS Am. CH Ferriel OS SDHF	Am Can CH Heart of the Heart OD	Am./Can. CH. Flick Flack OS SDHF	Am CH Puff the Magic Pooh	CH Golden Top Boy OS	Shelly Keats OD
11	10	Spitz	Minnow Pupp CD SH WCX Can. WCI OD	[url]http://www.flickr/Minnow[/url] Pupp.jpg	"Minnow"	Gender:F	Minnow Pupp	Am./Can. CH. Prince Rumba UD JH WC VCX Can CDX WC OS	Dreams of Bliss MH WCX ** OD	Am./Can. CH Can. OTCH Sunny Boy CDX TD JH WCX VCX OS; Can. UDT WCI TT OS	Belle Tiffanie CDX SH WCX VCX OD Can CD WCX	FC AFC Barron of the Field OS FDHF	Princess Cammie OD
12	11	Spitz	Am./Can. CH. Twister the Pooh UD JH WC VCX Can CDX WC OS	[url]http://www.flickr/Twister[/url] the Pooh.jpg	"Twistee"	Gender:M	Twister the Pooh	Am./Can. CH Can. OTCH Oster Malone CDX TD JH WCX VCX OS; Can. UDT WCI TT OS	Sparkle Pepper CDX SH WCX VCX OD Can CD WCX	Reddi Ruff UD WC Can CDX WC Am./Can. OS	Am. CH Brandy the Blond	Am./Can.CH Cristo OS SDHF	Swedish Jan the Blonde JH WC OD
13	12	Spitz	Am Can CH Pepperpots of Gold CDX SH WCX	[url]http://www.flickr/Pepperpots[/url] of Gold.jpg	"Pepper"	Gender:F	Pepperpots of Gold	Am./Can.CH My Poodie Bear OS SDHF	English Blonde Rose JH WC OD	Am. CH Silver Boy Flash OS SDHF	Am./Can. CH. Samanthee OD	Am/Can CH Happy Feet OS	Am/Can CH Cinnamon Spice SDHF OD
14	13	Spitz	Gloria the Gem CD	[url]http://www.flickr/Gloria[/url] the Gem.jpg	"Gem"	Gender:F	Gloria the Gem	BIS AM.CH. Golden Sparkle Boy OS SDHF	Hopping Hanna	Am/Can CH. Lord of the Stride CDX WC	Am CH Esta la Belle CD OD	CH Tiny of the Tribe UD	Princess Nikki Nicole
15	14	Spitz	BIS Diamonds of the Glittering Field CD OD SDHF	[url]http://www.flickr/Diamonds[/url] of the Glittering Field.jpg	"Glitter"	Gender:F	Diamonds of the Glittering Field	Am CH Mischief Heart of Love OS	Am. Can. CH Star of the Jewel OD	Am. CH Bear Golden Fur OS	BISS BIS Am Can CH Portofino's Goldie SDHF	Beaulieu JJ OS	BIS Ambrosia of Austerling OD SDHF
16
Results

Excel 2007
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Page	Honors	Birth Country	Registration	Breeder	Owner	Permanent ID	Website	Hips	Eyes	Heart	Elbows	Thyroid	PRA1	Ichthyosis	Cause of death	Image linked by
2	1		Country of origin:USA	Registration:AKC S89j9	Breeder:Melba Murphy	Owner:Charles Barson		http://www.goldenretriever.Astawebsite.html	Hip clearance:OFA 45243543642	Eye clearance:484525-94754			Thyroid clearance:MSU Normal	PRA-1 Clear (Optigen)			Image linked by:Kenny Ireland
3	2	Honorifics:BIS BISS 1997, #1	Country of origin:United Kingdom	Registration:KC S47568839	Breeder:Bary Pedersen	Owner:Sam Grant		http://www.goldenretriever.Sallywebsite.html	Hip clearance:OFA 565426543		Heart clearance:OFA 7592-7475		Thyroid clearance:MSU Normal				Image linked by:Dawn Bolson
4	3	Honorifics:BIS BISS, GRCC Hall of Fame	Country of origin:USA	Registration:AKC 5840989q	Breeder:Carly Smithinson	Owner:Reese Lipton		http://www.goldenretriever.Kellywebsite.html	Hip clearance:OFA 5672223	Eye clearance:69042-8752-75-62	Heart clearance:Clear (cardiologist) 62975-2	Elbow clearance:OFA 48725	Thyroid clearance:MSU Normal				Image linked by:Melita Martee
5	4		Country of origin:Sweden	Registration:SKC S502860	Breeder:Messina Boltonsten	Owner:Keith Moorpark		http://www.goldenretriever.Tanglowwebsite.html	Hip clearance:OFA 42545652222	Eye clearance:7527-276772-7			Thyroid clearance:MSU Normal		Ichthyosis status:not a carrier	Cause of death:old age
6	5		Country of origin:USA	Registration:AKC 0486927	Breeder:Jane Reher	Owner:Michael Jano Metier		http://www.goldenretriever.Tiarrawebsite.html		Eye clearance:5-2764627498			Thyroid clearance:MSU Normal	PRA-1 Clear (Optigen)			Image linked by:Belle Isinglass
7	6	Honorifics:'95 Mst Nat. Finlst	Country of origin:USA	Registration:AKC 497628985092	Breeder:Givinchy Marlo	Owner:Gloria Liner		http://www.goldenretriever.Heathwebsite.html					Thyroid clearance:MSU Normal	PRA-2 Clear (Optigen)			Image linked by:Bart Theron
8	7		Country of origin:USA	Registration:AKC 5798427589	Breeder:Sandy Marlson	Owner:Sarah Sarinne	Microchip # 647684754208	http://www.goldenretriever.Buglowebsite.html	Hip clearance:OFA 6724850280	Eye clearance:852776-43	Heart clearance:OFA 6827-89476-2		Thyroid clearance:MSU Normal			Cause of death:died in sleep
9	8		Country of origin:Ireland	Registration:IKC 959048	Breeder:Suzie Connell	Owner:Liam Martinsen	Microchip # 5479258309485	http://www.goldenretriever.Gillwebsite.html	Hip clearance:OFA 427584729		Heart clearance:OFA 72976-894	Elbow clearance:OFA 4872-6y	Thyroid clearance:MSU Normal
10	9	Honorifics:BISS	Country of origin:USA	Registration:AKC 5092458	Breeder:Sharon Walker and Dane Barnes	Owner:Melvin and Elsa Ekkels		http://www.goldenretriever.Carawebsite.html			Heart clearance:OFA 672987-6	Elbow clearance:OFA 89627yr	Thyroid clearance:MSU Normal
11	10	Honorifics:Bloat	Country of origin:USA	Registration:AKC 47258774	Breeder:Nikki Candine	Owner:John and Tammy Olston	Microchip # 57984275824795	http://www.goldenretriever.Minnowwebsite.html	Hip clearance:OFA 75847276	Eye clearance:52-762454259034	Heart clearance:OFA 67-928746892
12	11	Honorifics:BIS	Country of origin:Canada	Registration:CKC 82764572	Breeder:Courtney Moore	Owner:Amilee Pennington		http://www.goldenretriever.Twisteewebsite.html	Hip clearance:OFA 768947250								Image linked by:Courtney Larringstone
13	12	Honorifics:AKC''s 1st CH Golden SH, CKC Tie HIT	Country of origin:Canada	Registration:CKC 498260874726	Breeder:Marnie Vandelle	Owner:Simone Perry	Avis Microchip #: 1324572956793-8	http://www.goldenretriever.Pepperwebsite.html		Eye clearance:7642-97643			Thyroid clearance:MSU Normal	PRA-2 Clear (Optigen)			Image linked by:Stephanie Searson
14	13		Country of origin:USA	Registration:AKC 498276482	Breeder:Johnathon and June Simpson	Owner:Chrissy Locklear		http://www.goldenretriever.Gemwebsite.html	Hip clearance:OFA 4582908	Eye clearance:752874-62342-9	Heart clearance:OFA 2758246u4	Elbow clearance:OFA 672974742	Thyroid clearance:MSU Normal
15	14	Honorifics:BIS	Country of origin:USA	Registration:AKC 859279	Breeder:Jackson Carrington	Owner:Karen Pillsberry		http://www.goldenretriever.Glitterwebsite.html	Hip clearance:OFA 74825749872				Thyroid clearance:MSU Normal				Image linked by:Adelle Holly
16
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 ReorgDogData()
' hiker95, 02/14/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
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
w1.Range(w1.Cells(1, 1), w1.Cells(lr, lc)).Copy wR.Cells(1, 1)
a = wR.Range("O2:AD" & lr).Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
With wR.Range("O2:AD" & lr)
  .ClearContents
  .Interior.Pattern = xlNone
End With
For r = 1 To lr - 1 Step 1
  For c = 1 To 16 Step 1
    If a(r, c) <> "" Then
      Select Case Left(LCase(a(r, c)), 2)
        Case "br"   '4
          b(r, 4) = a(r, c)
        Case "ca"   '15
          b(r, 15) = a(r, c)
        Case "co"   '2
          b(r, 2) = a(r, c)
        Case "el"   '11
          b(r, 11) = a(r, c)
        Case "ey"   '9
          b(r, 9) = a(r, c)
        Case "he"   '10
          b(r, 10) = a(r, c)
        Case "hi"   '8
          b(r, 8) = a(r, c)
        Case "ho"   '1
          b(r, 1) = a(r, c)
        Case "ht"   '7
          b(r, 7) = a(r, c)
        Case "ic"   '14
          b(r, 14) = a(r, c)
        Case "im"   '16
          b(r, 16) = a(r, c)
        Case "mo"   '20
          b(r, 20) = a(r, c)
        Case "ow"   '5
          b(r, 5) = a(r, c)
        Case "pe"   '6
          b(r, 6) = a(r, c)
        Case "pr"   '13
          b(r, 13) = a(r, c)
        Case "re"   '3
          b(r, 3) = a(r, c)
        Case "th"   '12
          b(r, 12) = a(r, c)
        Case Else   '"pe"   '6
          If InStr(a(r, c), "Microchip") > 0 Then
            b(r, 6) = a(r, c)
          End If
      End Select
    End If
  Next c
Next r
wR.Range("O2").Resize(UBound(b, 1), UBound(b, 2)) = b
For r = 2 To lr Step 1
  If wR.Cells(r, 15) <> "" Then wR.Cells(r, 15).Interior.ColorIndex = 6
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 16) <> "" Then wR.Cells(r, 16).Interior.ColorIndex = 43
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 17) <> "" Then wR.Cells(r, 17).Interior.ColorIndex = 35
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 18) <> "" Then wR.Cells(r, 18).Interior.ColorIndex = 42
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 19) <> "" Then wR.Cells(r, 19).Interior.ColorIndex = 39
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 20) <> "" Then wR.Cells(r, 20).Interior.ColorIndex = 54
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 21) <> "" Then wR.Cells(r, 21).Interior.ColorIndex = 40
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 22) <> "" Then wR.Cells(r, 22).Interior.ColorIndex = 50
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 23) <> "" Then wR.Cells(r, 23).Interior.ColorIndex = 41
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 24) <> "" Then wR.Cells(r, 24).Interior.ColorIndex = 7
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 25) <> "" Then wR.Cells(r, 25).Interior.ColorIndex = 44
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 26) <> "" Then wR.Cells(r, 26).Interior.ColorIndex = 36
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 27) <> "" Then wR.Cells(r, 27).Interior.ColorIndex = 48
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 28) <> "" Then wR.Cells(r, 28).Interior.ColorIndex = 45
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 29) <> "" Then wR.Cells(r, 29).Interior.ColorIndex = 37
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 30) <> "" Then wR.Cells(r, 30).Interior.ColorIndex = 53
Next r
wR.Activate
Application.ScreenUpdating = True
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 ReorgDogData macro.

 

[TerryChristiansohn]

Hello, I tried the macro you created on a worksheet with about 25,000 records. OMG!!! It is amazing!! It arranged everything in less than 5 seconds. How did you learn to do this?? You have an enviable, brilliant mind. Thank you, Thank you, and my Aunt thanks you....

I have been puzzling over how to achieve the following, and so far, I have not been able to find an add in that does this. I need to be able to compare cells within two column groups:

(columns O through AD) to (columns AE to AZ).

(Columns AE to AZ) have weird strings of data that are mostly duplicates of data found in (columns O through AD). The reason I am reluctant to simply select the entire range of (Columns AE to AZ) and delete it, is that I am concerned about potentially deleting data that is not merely a duplicate. So I need something that will compare each cell in the row and delete the Columns AE to AZ duplicates.

I've uploaded another version of the last spreadsheet that shows what I mean. Duplicate data that for some unknown reason, arrives in the .csv's. All of the duplicate data is to the right of column AD. I've bolded the duplicate data and colored it violet in the example.

In the actual spreadsheet, many of the rows do not contain the extra data, and there can be several hundred rows until more duplicate data appears to the right of column AD. And there are several thousand cells of this duplicate data throughout the sheet on the right of AD, and I cannot parse it manually without error, not to mention exhaustion.

https://www.box.com/s/0a7qh8zb33quhjxz57py

 

[hiker95]

TerryChristiansohn,

Thanks for the latest workbook.

The latest macro will find and move the data past column AD to the correct column in the row. However, if the data past column AD is not a duplicate of the data in columns O thru AD, then the data past column AD could overwrite the data in columns O thru AD.

In your latest workbook, the data past column AD are duplicates of the data in columns O thru AD.


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 ReorgDogDataV2()
' hiker95, 02/15/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.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
w1.Range(w1.Cells(1, 1), w1.Cells(lr, lc)).Copy wR.Cells(1, 1)
a = wR.Range(wR.Cells(2, 15), wR.Cells(lr, lc)).Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
With wR.Range(wR.Cells(2, 15), wR.Cells(lr, lc))
  .ClearContents
  .Interior.Pattern = xlNone
End With
For r = 1 To UBound(a, 1)
  For c = 1 To UBound(b, 2)
  
    If a(r, c) <> "" Then
      Select Case Left(LCase(a(r, c)), 2)
        Case "br"   '4
          b(r, 4) = a(r, c)
        Case "ca"   '15
          b(r, 15) = a(r, c)
        Case "co"   '2
          b(r, 2) = a(r, c)
        Case "el"   '11
          b(r, 11) = a(r, c)
        Case "ey"   '9
          b(r, 9) = a(r, c)
        Case "he"   '10
          b(r, 10) = a(r, c)
        Case "hi"   '8
          b(r, 8) = a(r, c)
        Case "ho"   '1
          b(r, 1) = a(r, c)
        Case "ht"   '7
          b(r, 7) = a(r, c)
        Case "ic"   '14
          b(r, 14) = a(r, c)
        Case "im"   '16
          b(r, 16) = a(r, c)
        Case "mo"   '20
          b(r, 20) = a(r, c)
        Case "ow"   '5
          b(r, 5) = a(r, c)
        Case "pe"   '6
          b(r, 6) = a(r, c)
        Case "pr"   '13
          b(r, 13) = a(r, c)
        Case "re"   '3
          b(r, 3) = a(r, c)
        Case "th"   '12
          b(r, 12) = a(r, c)
        Case Else   '"pe"   '6
          If InStr(a(r, c), "Microchip") > 0 Then
            b(r, 6) = a(r, c)
          End If
      End Select
    End If
  Next c
Next r
wR.Range("O2").Resize(UBound(b, 1), UBound(b, 2)) = b
For r = 2 To lr Step 1
  If wR.Cells(r, 15) <> "" Then wR.Cells(r, 15).Interior.ColorIndex = 6
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 16) <> "" Then wR.Cells(r, 16).Interior.ColorIndex = 43
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 17) <> "" Then wR.Cells(r, 17).Interior.ColorIndex = 35
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 18) <> "" Then wR.Cells(r, 18).Interior.ColorIndex = 42
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 19) <> "" Then wR.Cells(r, 19).Interior.ColorIndex = 39
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 20) <> "" Then wR.Cells(r, 20).Interior.ColorIndex = 54
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 21) <> "" Then wR.Cells(r, 21).Interior.ColorIndex = 40
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 22) <> "" Then wR.Cells(r, 22).Interior.ColorIndex = 50
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 23) <> "" Then wR.Cells(r, 23).Interior.ColorIndex = 41
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 24) <> "" Then wR.Cells(r, 24).Interior.ColorIndex = 7
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 25) <> "" Then wR.Cells(r, 25).Interior.ColorIndex = 44
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 26) <> "" Then wR.Cells(r, 26).Interior.ColorIndex = 36
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 27) <> "" Then wR.Cells(r, 27).Interior.ColorIndex = 48
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 28) <> "" Then wR.Cells(r, 28).Interior.ColorIndex = 45
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 29) <> "" Then wR.Cells(r, 29).Interior.ColorIndex = 37
Next r
For r = 2 To lr Step 1
  If wR.Cells(r, 30) <> "" Then wR.Cells(r, 30).Interior.ColorIndex = 53
Next r
wR.Activate
Application.ScreenUpdating = True
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 ReorgDogDataV2 macro.

 

[TerryChristiansohn]

Wow, thank you.... And yes, I can see what you mean by the dangers of potentially overwriting data in columns O through AD.

1. Would it be possible to simply delete duplicates in columns AE through AZ without moving them to O thru AD,
or,
2. To would it be possible to colorize the text of the duplicates in AE through AZ a magenta color. I realized that I could use the Excel autofilter feature on the columns, and if the duplicate values are colored magenta, I could see that they are duplicates and not have to manually compare them with data in columns O thru AD. Then I could just delete all the magenta items. Probably not the most elegant solution, but at least I would not kill my data.

Also: I have a question I've run into on several spreadsheets: do you know why it is that sometimes a column of numbers sort like text rather than numbers? Even after I have used the format menu to convert the cell to numbers. Do I need a macro or an addin to correct this condition?

 

[hiker95]

TerryChristiansohn,

You are very welcome, again. Glad I could help.

1. Would it be possible to simply delete duplicates in columns AE through AZ without moving them to O thru AD

Yes.

2. To would it be possible to colorize the text of the duplicates in AE through AZ a magenta color. I realized that I could use the Excel autofilter feature on the columns, and if the duplicate values are colored magenta, I could see that they are duplicates and not have to manually compare them with data in columns O thru AD. Then I could just delete all the magenta items. Probably not the most elegant solution, but at least I would not kill my data.

This would be extremely difficult to accomplish. If the data in columns AE thru AZ did not match the data in columns O thru AZ same row, it should then be added to a new row with the same information in columns A thru N. As the original data is being moved, in arrays in memory, to the correct location in the second array, for columns O thru AD, the cell background color is not applied to the array at this time. The colors are applied after all the data in the arrays have been moved to columns O thru AD. Then the macro checks each cell in one column at a time in columns O thru AD, and, if he cell is not blank, it adds the background color.

Also: I have a question I've run into on several spreadsheets: do you know why it is that sometimes a column of numbers sort like text rather than numbers? Even after I have used the format menu to convert the cell to numbers. Do I need a macro or an addin to correct this condition?

I would have to see the raw data in another workbook.

 

[TerryChristiansohn]

Hello hiker95,

Thank you again for all the help you gave me. I recorded a few formatting macro's and inserted the macro you wrote for me. Prior to using your macro, each worksheet had taken me at least 7 hours to reorganize the data manually, and that was error-prone. Now, when as my aunt sends me a csv with 25,000 to 50,000 records, I run the macro and 23 seconds later, the sheet is organized properly and formatted to have a nice appearance. None of my family has much in the way of computer skills. I told them all about how wonderful it was to have your help. I wish I had your talents!

Again, thank you so much!

Re: the strange number problem:
I think got fed up and got rid of the original workbook that had the text numbers, but if I come across that situation again, I will upload it on a new thread. There was nothing I could do with the numerical-looking data to change it using the normal Format > Cells menu option in Excel. Apparently Microsoft knows about it, and they have an article about it.

How to convert text to numbers in Excel

I used Method 4 and it worked very well, but I had so many columns of data I had to do this with, it was a tiresome task.

 

[hiker95]

TerryChristiansohn,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

I wish I had your talents!

Training / Books / Sites as of 2/17/2013

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
MrExcel's Products: Books, CDs, Podcasts

There are over 1800 Excel videos/tutorials here:
excelisfun -- Excel How To Videos - YouTube

Getting Started with VBA.
DataPig Technologies

If you are serious about learning VBA try
Macros Made Easy for Microsoft Excel

Excel Tutorials and Tips - VBA - macros - training
Excel Tutorial | Excel Tips | Excel Articles

Excel 2007 VBA materials to learn here:
VBA for Excel 2007 tutorial-VBA programming?

Here's a good primer on the scope of variables.
Understanding Scope

See David McRitchie's site if you just started with VBA
Getting Started with Macros and User Defined Functions

What is a Visual Basic Module?
What Is A VBA Module?

Here is a good introductory tutorial using a VBA Class:
Classes In VBA

Ron de Bruin's intro to macros:
Where do I paste the code that I want to use in my workbook

Ron's Excel Tips
Ron's Excel Tips

Anthony's Excel VBA Page - Excel Application - Excel Consultant - Excel Consulting (see 3 tutorials in Excel VBA Basic Tutorial Series)

http://www.jlathamsite.com/Teach/VBA...troduction.pdf (95 page "book")

BET: Microsoft Excel Visual Basic

Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
Creating An XLA Add In

How do I create a PERSONAL.XLS(B) or Add-in
How do I create a PERSONAL.XLS(B) or Add-in

Creating custom functions
Creating custom functions - Excel - Office.com

Writing Your First VBA Function in Excel
Writing Your First VBA Function in Excel

VBA for Excel (Macros)
Excel Macros (VBA) Tutorial

Excel Macros Tutorial
Excel Macros (VBA) Tutorial

Excel Macros & Programming
Learning about EXCEL macros

VBA Lesson 11: VBA Code General Tips and General Vocabulary
VBA for Excel macros language

Excel VBA -- Adding Code to a Workbook
Excel VBA -- Adding Code to Excel Workbook

Beyond Excel's recorder

Helpful as a simple concise reference for the basics, and, using the macro recorder is endlessly powerful.
MS Excel: Cells

Learn to debug:
Debugging VBA

How To: Assign a Macro to a Button or Shape
How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts

User Form Creation
Create an Excel UserForm

When To Use a UserForm & What to Use a UserForm For
When to use Userform & What To Use Them For. Excel Training VBA 2 lesson 2

Excel Tutorials / Video Tutorials - Functions
Excel VLookup Function Examples

INDEX MATCH - Excel Index Function and Excel Match Function
Excel Index Function and Match Function

Excel Data Validation
Excel Data Validation Tips and Quirks
Excel Data Validation - Add New Items

Excel -- Data Validation -- Create Dependent Lists
Excel Data Validation -- Dependent Lists

Your Quick Reference to Microsoft Excel Solutions
XL-CENTRAL.COM : For your Microsoft Excel Solutions

New! Excel Recorded Webinars
DataPig Technologies

Fuzzy Matching - new version plus explanation

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
Programming In The VBA Editor

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…

by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)
Professional Excel Development

by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)
Excel 2002 VBA: Programmers Reference

"Professional Excel Development" by Rob Bovey, Dennis Wallentin, Stephen Bullen, & John Green

DonkeyOte: My Recommended Reading, Volatility
Volatile Excel Functions -Decision Models

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
Excel: Introduction to Array Formulas - Xtreme Visual Basic Talk
Array in Excel VBA

Array Dimensions in Visual Basic - Working with Dimensions (code and graphics)
Array Dimensions in Visual Basic

Shortcut Keys in Excel 2000 through 2007

Pivot Intro
Using Pivot Tables and Pivot Charts in Microsoft Excel
Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
Excel Pivot Table -- Dynamic Data Source
Overview of PivotTable and PivotChart reports - Excel - Office.com

Email from XL - VBA
Example Code for sending mail from Excel

Outlook VBA
Writing VBA code for Microsoft Outlook

Function Dictionary
http://www.xlfdic.com/

Function Translations
Excel 2007 function name translations - Dictionary Chart Front Page

Dynamic Named Ranges
Excel Names -- Excel Named Ranges

How to create Excel Dashboards
http://www.mrexcel.com/Excel-dashboards-Xcelsius.html
Excel Dashboard Templates
Excel Dashboards - Templates, Tutorials, Downloads and Examples | Chandoo.org - Learn Microsoft Excel Online
Excel Dashboards - Templates, Tutorials, Downloads and Examples | Chandoo.org - Learn Microsoft Excel Online
Free Microsoft Excel Dashboard Widgets to Download
AJP Excel Information - Gauge

Excel Dashboard / Scorecard Ebook
Excel Dashboards and Scorecards Ebook | How to Create Dashboards in Excel

Mike Alexander from Data Pig Technologies
Excel 2007 Dashboards & Reports For Dummies

Templates
CPearson.com Topic Index
Excel Template - Golf Scores
Free Microsoft Excel Template Links & Search Engine

Microsoft Excel Cascading Listboxes Tutorial
Microsoft Excel Cascading Listboxes Tutorial - YouTube

Date & Time stamping:
McGimpsey & Associates : Excel : Time and date stamps

Get Formula / Formats thru custom functions:
Show FORMULA or FORMAT of another cell

A nice informative MS article "Improving Performance in Excel 2007"
Improving Performance in Excel 2007

Progress Meters
AJP Excel Information - Progress meters
Website Disabled

How to convert text to numbers in Excel
How to convert text to numbers in Excel

And, as your skills increase, try answering posts on sites like:
MrExcel.com | Excel Resources | Excel Seminars | Excel Products
Excel Help Forum
Excel Templates | Excel Add-ins and Excel Help with formulas and VBA Macros
VBA Express Portal
Excel, Access, PowerPoint and Word VBA Macro Automation Help

If you are willing to spend money for the training, then something here should work for you...
Amazon.com: excel tutorial dvd
Amazon.com: excel tutorial dvd

Advanced Excel Training - Online Excel Course
http://www.udemy.com/advanced-excel/