Organizing multiple columns by name

[coool]

Essentially I have several data sets.Each one has a name in one column and a value in the next column. I want to match all the names together so all the names and associated values are in the same row . I cannot simply do alphabetical order because some of the data sets do not have many of the names and some are longer. I cannot do it manually because it is thousand of names long. Thanks

[JBeaucaire]

Post a smaller exemplary workbook showing your starting data and a manually mocked "results" sheet so we can see what you're doing.

Click GO ADVANCED and use the paperclip icon to post up your sample workbook.

[coool]

4 actresses 95 actresses 1 acts
1 acts 4 actual 1 again
33 actual 3 again 1 ahead

This is an example. I want each row to have a unique text value. The problem is, as you can see, not every column has every word. I would want to reorganize that above data to look like this below

4 actresses 95 actresses
1 acts 1 acts
33 actual 4 actual
3 again 1 again
1 ahead

[JBeaucaire]

4	actresses	95	actresses	1	acts
1	acts	        4	actual		1	again
33	actual		3	again   	1	ahead

4	actresses	95   actresses	
1	acts                                  1	acts	      
33	actual	 	4    actual  
                        3    again            1	again   	
                                              1	ahead

So some kind of "LineEmUp" macro? How many column sets in all?

[coool]

There are 6 columns (three with text values and three with associated numerical values). The number of rows in each column varies between 9000 and 16000
Thanks

[JBeaucaire]

I have a macro for lining up rows of data, published here:

LINE UP MATCHES - LineEmUp.xls

Here's a tweaked version for your "data pairs" scenario.

Option Explicit

Sub LineEmUp()
'Author:    Jerry Beaucaire
'Date:      7/5/2010
'Summary:   Line up column pairs so all matching
'           items are on the same rows
Dim LC  As Long
Dim Col As Long
Dim LR  As Long
Application.ScreenUpdating = False

'Spot last column of data
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
   
'Add new key column  to collect unique values
    Cells(1, LC + 1) = "Key"
    For Col = 2 To LC Step 2
        Range(Cells(2, Col), Cells(Rows.Count, Col)).SpecialCells(xlConstants).Copy _
           Cells(Rows.Count, LC + 1).End(xlUp).Offset(1)
    Next Col

    Columns(LC + 1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, LC + 2), Unique:=True
    Columns(LC + 2).Sort Key1:=Cells(2, LC + 2), Order1:=xlAscending, Header:=xlYes

'Fill in new table values
    LR = Cells(Rows.Count, LC + 2).End(xlUp).Row
    With Range(Cells(1, LC + 3), Cells(LR, LC + 2 + LC))
        .FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC" & LC + 2 & ",C[-" & LC + 2 _
                        & "],0)), RC" & LC + 2 & ", """")"
        .Value = .Value
    End With
    
    With Range(Cells(1, LC + 4), Cells(LR, LC + 8))
        .SpecialCells(xlConstants).Offset(, -1).FormulaR1C1 = "=INDEX(C[-8], MATCH(RC[1], C[-7], 0))"
    End With
        
    With Range(Cells(1, LC + 3), Cells(LR, LC + 2 + LC))
        .Value = .Value
    End With
    
'Cleanup/Erase old values
    Range("A1", Cells(1, LC + 2)).EntireColumn.Delete xlShiftToLeft
    Application.ScreenUpdating = True
End Sub

BTW, you do need to insert titles at the top of the columns for this to work smoothly. The macro will remove them again.

[wondering2]

Thank you very much! The program worked very well (for some reason it could only handle about 6000 rows at a time) but I just split my data into three sections. It was great. Thanks!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.