Taking multiple columns and stacking in to one column

**caripinkert** · 11-22-2019, 02:37 PM

Hello!!! New to the forum and so happy to have found this! I am having a major issue!!! Here is the set up:

Column A: Name
Column B: Counties

Hundreds of records. Column B could consist of 1 County or 25 Counties. Every row is different. I do a simple 'text to column' creating multiple columns each with a county name.

I need for every row to be only 1 name and 1 county so that I can create pivot tables. In order to do this, I need for all of the counties to be in 1 single column with the corresponding name in column A on the same row. I have uploaded a sample sheet to show what I am talking about. I'm assuming a macro or VBA is warranted here because when I am dealing with hundreds of rows this becomes an impossible project. Any help here would be greatly appreciated!

**JLGWhiz** · 11-22-2019, 03:31 PM

See if this is what you want.

Sub t()
Dim rng As Range, i As Long, cnt As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
            cnt = Application.CountA(.Range(.Cells(i, 2), .Cells(i, Columns.Count).End(xlToLeft)))
            If cnt > 1 Then
                .Cells(i + 1, 1).Resize(cnt - 1).EntireRow.Insert
                .Cells(i, 1).Resize(cnt) = .Cells(i, 1).Value
                .Cells(i, 3).Resize(, cnt - 1).Copy
                .Cells(i + 1, 2).PasteSpecial xlPasteValues, Transpose:=True
                .Cells(i, 3).Resize(, cnt - 1).ClearContents
            End If
            cnt = 0
        Next
    End With
End Sub

**caripinkert** · 11-22-2019, 04:21 PM

Oh my God YOU ARE A GENIUS!!!!!!!! Do you know how long I have been struggling with this!?!??! You just made my MONTH! THANK YOU, THANK YOU, THANK YOU!!!!!!!!

**JLGWhiz** · 11-22-2019, 07:33 PM

You're welcome, welcome, welcome

regards, JLG

Taking multiple columns and stacking in to one column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Taking multiple columns and stacking in to one column

Re: Taking multiple columns and stacking in to one column

Re: Taking multiple columns and stacking in to one column

Re: Taking multiple columns and stacking in to one column

Thread Information

Users Browsing this Thread

Similar Threads

stacking multiple columns into one

[SOLVED] Stacking several columns into one column on another worksheet

[SOLVED] Stacking data from multiple columns.

Stacking data into one column based on unique values and multiple source columns

Combining Multiple Columns without stacking

Taking data in rows and stacking into columns

Stacking columns across multiple sheets

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions