Data Management Question

bergsh

New Member
Joined
Sep 12, 2006
Messages
13
So here is the story: I am trying to create a macro that will sort a row into the corresponding worksheet. I have an example of the data that I am trying to sort. For all cells of text with the value " 4828-010O", I want to place the number from the corresponding C Row. I have not had any luck with lookup functions.
I tried these excel formulas.

=LOOKUP(" 4828-024O",A1:A28,C1:C28)
=VLOOKUP(" 4828-024O",A1:A28,C1:C28)
sorted.xls
ABCD
14880-020OGOOD0.9283
24880-020IGOOD0.629
34880-020WGOOD0.1496
44880-020VGOOD0.0119
54826-010OGOOD0.7497
64826-010IGOOD0.3243
74826-010WGOOD0.2126
84826-010VGOOD0.0182
94826-010OGOOD0.7504
104826-010IGOOD0.3221
114826-010WGOOD0.2141
124826-010VGOOD0.0177
134826-010OGOOD0.7432
144826-010IGOOD0.3175
154826-010WGOOD0.2128
164826-010VGOOD0.0123
174828-010OGOOD0.7185
184828-010IGOOD0.3181
194828-010WGOOD0.2001
204828-010VGOOD0.0285
214828-010OGOOD0.7135
224828-010IGOOD0.3139
234828-010WGOOD0.1998
244828-010VGOOD0.026
Sheet1


Thanks for the help in advance,
Shawn
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

macajm

New Member
Joined
Oct 21, 2005
Messages
32
Try
=VLOOKUP(" 4828-024O",A1:c28,3) or maybe you have to use the format VLOOKUP(" 4828-024O",A1:c28,3,FALSE) if the items not sorted.
 

bergsh

New Member
Joined
Sep 12, 2006
Messages
13
Hey thanks for the reply. But this far I am not having a lot of luck with using those suggestions. You state I may need to have the data sorted. Which column would I need to sort by do you think? Or is there another way to break out the data? I was thinking of a series of =if( statements. But this could become quite cumbersome. Please let me know if you have any more suggestions.
Thanks

Here is the output I received:
sorted.xls
ABCDEFG
14880-020OGOOD0.92830.026
24880-020IGOOD0.629
34880-020WGOOD0.1496
44880-020VGOOD0.0119
54826-010OGOOD0.7497
64826-010IGOOD0.3243
74826-010WGOOD0.2126
84826-010VGOOD0.0182
94826-010OGOOD0.7504
104826-010IGOOD0.3221
114826-010WGOOD0.2141
124826-010VGOOD0.0177
134826-010OGOOD0.7432
144826-010IGOOD0.3175
154826-010WGOOD0.2128
164826-010VGOOD0.0123
174828-010OGOOD0.7185
184828-010IGOOD0.3181
194828-010WGOOD0.2001
204828-010VGOOD0.0285
214828-010OGOOD0.7135
224828-010IGOOD0.3139
234828-010WGOOD0.1998
244828-010VGOOD0.026
Sheet1
[/img]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Shawn

What are you actually trying to do?

In your first post you said this.
I am trying to create a macro that will sort a row into the corresponding worksheet.
 

bergsh

New Member
Joined
Sep 12, 2006
Messages
13

ADVERTISEMENT

Norie,
If possible I would like to use a macro to sort this data, but if I was unable to do this I would use formulas to sort the data and then use a copy paste function to complete the same think.
Here is where I run into a problem. Each different string of text in column A needs to be sorted into a corresponding worksheet of the same name. I figured I would leave this off in my original post as a way to keep in simple. But I see now that I should have included my problem exactly as it is. Sorry for the confusion and thanks again for the help.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
So what you want to do is distribute the data to seperate worksheets based on the value in column A?

If that's the case I've got some code lying about that will do that using advanced filter.

I've got to shoot off now but I'll look it out and post back later. :coffee:
 

bergsh

New Member
Joined
Sep 12, 2006
Messages
13
That is exactly what I am looking to do. I really appreciate the help.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Shawn

Quickly knocked this up, it worked with your data after I'd cleaned it up a bit.

Note it creates new worksheets for each uniqe item in column A.
Code:
Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long
    
    Set wsAll = Worksheets("Sheet1")
    wsAll.Rows(1).Insert
    wsAll.Range("A1:C1") = Array("Field1", "Field2", "Field3")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    Set wsCrit = Worksheets.Add
    
    wsAll.Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For I = 2 To LastRowCrit
            
        Set rngCrit = wsCrit.Range("A2")
        
        Set wsNew = Worksheets.Add
        wsNew.Name = rngCrit
        
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        
        wsNew.Rows(1).Delete

        wsCrit.Rows(2).Delete
        
    Next I
    wsAll.Rows(1).Delete
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub
 

Forum statistics

Threads
1,136,348
Messages
5,675,241
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top