Consolidate multiple columns into one

Having some trouble with this one!
I have a spreadsheet with numerical values in columns I, L, O, R and U. Additionally, these columns may have no data in them as well. The data starts at the fourth cell and each column may be a different length.
I'm trying to combine these columns to column W.
Then I need to sort the data descending, identify and then count duplicates (if any).

The attached spreadsheet has the code I've been trying to tweak to work. I haven't been able to figure out how to correctly combine all the variable sized ranges.
test.xlsm
jcgrooveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Quick question..The reason you want to sort descending is to find out duplicates??? And once you find out duplicates what you want to??

Also if you want to delete these duplicates after finding what i suggest is first select your entire data then try data-->remove duplicates

And select the columns in which you want to check for duplicates which in this case will be columns I, L, O, R and U. Now excel will automatically check for duplicates in this column and will delete them leaving only unique data for your reference...
0
jcgrooveAuthor Commented:
No, I need the duplicates. I just want to count how many of each duplicate there may be in the combined column.
I have deleted the duplicates from the individual columns prior to combining. I need to tally the dupes in the combined one.
0
Saurabh Singh TeotiaCommented:
If you want to know count..Then rather recording macro..I will recommending creating a pivot table out of it like as shown in the example which you can also create in a new tab as well which will do what you are looking for...

Saurabh...
test-1.xlsm
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

jcgrooveAuthor Commented:
I may not have explained myself well enough.
What I need is to combine these columns one on top of the other (consolidate?).
So that I have one column with all the individual values from the other columns.
0
Saurabh Singh TeotiaCommented:
I'm not sure i understand you what you are trying to achieve here? Can you post me a sample file about before and after once i see the file..  i will be able to write code for you easily..
0
jcgrooveAuthor Commented:
Here is what I'm looking for. Thanks!
test-after.xlsm
0
Saurabh Singh TeotiaCommented:
I believe this is what you are looking for...

Sub movecopy()
    Dim lr As Long, lr1 As Long
    Dim cell As Range, rng As Range
    Dim r As Range
    lr = Cells(Cells.Rows.Count, "i").End(xlUp).Row

    If lr > 4 Then Range("I4:I" & lr).Copy Range("W4")

    lr = Cells(Cells.Rows.Count, "L").End(xlUp).Row
    lr1 = Cells(Cells.Rows.Count, "w").End(xlUp).Row + 1
    If lr > 4 Then Range("L4:L" & lr).Copy Range("W" & lr1)

    lr = Cells(Cells.Rows.Count, "o").End(xlUp).Row
    lr1 = Cells(Cells.Rows.Count, "w").End(xlUp).Row + 1
    If lr > 4 Then Range("o4:o" & lr).Copy Range("W" & lr1)


    lr = Cells(Cells.Rows.Count, "r").End(xlUp).Row
    lr1 = Cells(Cells.Rows.Count, "w").End(xlUp).Row + 1
    If lr > 4 Then Range("r4:r" & lr).Copy Range("W" & lr1)

    lr = Cells(Cells.Rows.Count, "u").End(xlUp).Row
    lr1 = Cells(Cells.Rows.Count, "w").End(xlUp).Row + 1
    If lr > 4 Then Range("u4:u" & lr).Copy Range("W" & lr1)

    lr1 = Cells(Cells.Rows.Count, "w").End(xlUp).Row

    If lr1 > 4 Then

        Set rng = Range("W4:W" & lr1)

        ActiveSheet.Sort.SortFields.Add Key:=Range("W4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveSheet.Sort
            .SetRange rng
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        For Each cell In rng
            Set r = Range("W4:W" & cell.Row)

            If Application.WorksheetFunction.CountIf(r, cell.Value) = Application.WorksheetFunction.CountIf(rng, cell.Value) And Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then
                cell.Offset(0, 1).Value = Application.WorksheetFunction.CountIf(rng, cell.Value)
            End If


        Next cell

    End If


End Sub

Open in new window


Saurabh...
0
jcgrooveAuthor Commented:
Works partially, It errors at the sort.
If I comment out the sort everything else works.
0
Saurabh Singh TeotiaCommented:
Which version of excel you are using as i don't get any error on the code at my end?

In additional try using sort by this method..

Set rng = Range("W4:W" & lr1)

        rng.Sort Key1:=Range("w4"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jcgrooveAuthor Commented:
That got it!
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.