Data Management Question

[bergsh]

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
	A	B	C	D
1	4880-020O	GOOD	0.9283
2	4880-020I	GOOD	0.629
3	4880-020W	GOOD	0.1496
4	4880-020V	GOOD	0.0119
5	4826-010O	GOOD	0.7497
6	4826-010I	GOOD	0.3243
7	4826-010W	GOOD	0.2126
8	4826-010V	GOOD	0.0182
9	4826-010O	GOOD	0.7504
10	4826-010I	GOOD	0.3221
11	4826-010W	GOOD	0.2141
12	4826-010V	GOOD	0.0177
13	4826-010O	GOOD	0.7432
14	4826-010I	GOOD	0.3175
15	4826-010W	GOOD	0.2128
16	4826-010V	GOOD	0.0123
17	4828-010O	GOOD	0.7185
18	4828-010I	GOOD	0.3181
19	4828-010W	GOOD	0.2001
20	4828-010V	GOOD	0.0285
21	4828-010O	GOOD	0.7135
22	4828-010I	GOOD	0.3139
23	4828-010W	GOOD	0.1998
24	4828-010V	GOOD	0.026
Sheet1

Thanks for the help in advance,
Shawn

 

[macajm]

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]

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
	A	B	C	D	E	F	G
1	4880-020O	GOOD	0.9283			0.026
2	4880-020I	GOOD	0.629
3	4880-020W	GOOD	0.1496
4	4880-020V	GOOD	0.0119
5	4826-010O	GOOD	0.7497
6	4826-010I	GOOD	0.3243
7	4826-010W	GOOD	0.2126
8	4826-010V	GOOD	0.0182
9	4826-010O	GOOD	0.7504
10	4826-010I	GOOD	0.3221
11	4826-010W	GOOD	0.2141
12	4826-010V	GOOD	0.0177
13	4826-010O	GOOD	0.7432
14	4826-010I	GOOD	0.3175
15	4826-010W	GOOD	0.2128
16	4826-010V	GOOD	0.0123
17	4828-010O	GOOD	0.7185
18	4828-010I	GOOD	0.3181
19	4828-010W	GOOD	0.2001
20	4828-010V	GOOD	0.0285
21	4828-010O	GOOD	0.7135
22	4828-010I	GOOD	0.3139
23	4828-010W	GOOD	0.1998
24	4828-010V	GOOD	0.026
Sheet1

[/img]

 

[Norie]

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]

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]

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.

 

[bergsh]

That is exactly what I am looking to do. I really appreciate the help.

 

[Norie]

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.

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