CODEWITHSUNDEEP
excelvbaconcatenationpowerqueryunpivot
Adam Ling
Adam Ling

Reputation: 13

User defined concatenate

I am trying to expand my annual planning document to give me more information. Currently however I am stuck.

My current Sheet is laid out as follows: Client Example image

I need to create a second sheet that concatenates the client name with each column heading there is a "yes" value in, as its own separate line.

Example 1 in the new sheet would become:

Example 1 - Annuals
Example 1 - Xero Fee

I had tried a copy and paste Macro, based on a quantity column counting the rows that include text. This gave the desired amount of client names in a new sheet but I was unable to work out how to include the "concatenate" part of this problem to it.

Public Sub CopyData()

    ' This routing will copy rows based on the quantity to a new sheet.
    Dim rngSinglecell As Range
    Dim rngQuantityCells As Range
    Dim intCount As Integer

    ' Set this for the range where the Quantity column exists. This works only if there are no empty cells
    Set rngQuantityCells = Range("G1", Range("G1").End(xlDown))

    For Each rngSinglecell In rngQuantityCells
        ' Check if this cell actually contains a number
        If IsNumeric(rngSinglecell.Value) Then
            ' Check if the number is greater than 0
            If rngSinglecell.Value > 0 Then
                ' Copy this row as many times as .value
                For intCount = 1 To rngSinglecell.Value
                    ' Copy the row into the next emtpy row in sheet2
                    Range(rngSinglecell.Address).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
                    ' The above line finds the next empty row.

                Next
            End If
        End If
    Next
End Sub

Upvotes: 1

Views: 135

Answers (1)

ashleedawg
ashleedawg

Reputation: 21629

If your goal is to get from this:

unpivot1

...to this (or that):

unpivot2

...then what you want to do is called an Unpivot.

How to Unpivot "crosstab-style" data:

UnPivot demo

You can find the steps written out on a question I answered a week ago, and there's a more detailed explanation and steps over here.

Let me know if you have any questions!

Yeah, maybe I used this as an excuse to practice using Screen2Gif, but it sure helps demo a point! :-)

🇨🇦

Upvotes: 3

Related Questions