PDA

View Full Version : Transposing multiple columns into one column while repeating the names



winfy
08-19-2016, 04:34 AM
Hi all,

I tried everything and got no where...I hope someone can help me.

I have the following data format.

Name Stage 1 Stage 2 Stage 3
John Hall 10 20 30
Alice Bao 15 23 35

I would like it in the following format.

John Hall Stage 1 10
John Hall Stage 2 20
John Hall Stage 3 30
Alice Bao Stage 1 15
Alice Bao Stage 2 23
Alice Bao Stage 3 35


Open to both VBA or excel functions.

Thanks in advance.

Winfy

fb7894
08-19-2016, 07:06 AM
formulas would be extremely complicated. here is vba code..

Sub TransposeMe()
Dim SourceRange As Range
Dim DestRange As Range

Set SourceRange = Range("A2")
Set DestRange = Range("A10")

Do Until SourceRange = vbNullString
With DestRange
.Value = SourceRange
.Offset(, 1).Value = "Source 1"
.Offset(, 2).Value = SourceRange.Offset(, 1)
.Offset(1, 0).Value = SourceRange
.Offset(1, 1).Value = "Source 2"
.Offset(1, 2).Value = SourceRange.Offset(, 2)
.Offset(2, 0).Value = SourceRange
.Offset(2, 1).Value = "Source 3"
.Offset(2, 2).Value = SourceRange.Offset(, 3)
Set SourceRange = SourceRange.Offset(1)
Set DestRange = DestRange.Offset(3)
End With
Loop
End Sub

mancubus
08-19-2016, 12:59 PM
array solution.
this may be quicker for large data sets



Sub vbax_56911_transpose_multi_col()

Dim NameCount As Long, i As Long, j As Long, k As Long
Dim ArrTrns

With Worksheets("Sheet1")
NameCount = Application.CountA(.UsedRange.Columns(1).Offset(1)) 'Offset 1 row to exclude header
ReDim ArrTrns(1 To NameCount * 3, 1 To 3) '*3 to include 3 stages for each name
For i = 1 To NameCount
For j = 2 To 4
k = k + 1
ArrTrns(k, 1) = .Cells(i + 1, 1)
ArrTrns(k, 2) = .Cells(1, j)
ArrTrns(k, 3) = .Cells(i + 1, j)
Next j
Next i
End With

Worksheets("Sheet2").Cells(1).Resize(UBound(ArrTrns, 1), UBound(ArrTrns, 2)) = ArrTrns

End Sub

winfy
08-19-2016, 01:20 PM
Hi fb7984,
Thanks so much for your response. I am wondering if you can show me how to extend the code for many more rows and columns. What I showed you is just a sample of a dataset of 500 rows and 10 columns.

Thanks in advance!

Winfy

winfy
08-19-2016, 01:23 PM
Thanks Mancubus,
I
I am not infront of the computer now. I will try your code when I get a chance! Is it scale-able as it is? I can't see it being so, but that could just be my low coding skills.

mancubus
08-19-2016, 02:32 PM
you are welcome.

to give you an idea;
it took 24,2 seconds to execute the code with 250000 rows of data (desired result 750000 rows) on my win10 64bit, office 2016 32bit, toshiba satellite p855-32f machine.

mancubus
08-20-2016, 01:07 AM
Hi Mancubus,

thanks for your second reply. As I am very new, I don't see where I put the row and column indicator to tell vba where to start and finish with the raw data table.

how does the code repeat the names as well?

i hope to hear from you soon.

winfy

mancubus
08-20-2016, 01:50 AM
taking into account the 'given' my code assumes you have a normal excel table.
(it means table's topleft cell is A1, Row 1 is the header row, Row 1 and Column 1 of the table contain no blank cells.)

we know that your input table has 4 columns.

the variable NameCount finds the number of the names in column A via CountA function. (CountA function counts the non-blank cells in specified range.)

in this specific case this range is the first column of the sheet's usedrange, offsetted by one row to exclude the header.

one important point is that normal excel data sheets contain only one table. if you have more than 1 table in your sheet separated by blank columns or rows then my code needs a modification.

you have 3 stages for each name. so NameCount * 3 is the total number of the rows needed.

in the output table, you want;
- names in the first column (taken from Column A or Column 1 in the input table),
- 3 stage names in second column (taken from Range B1-C1-D1 in the input table,
- stage data in the third column (taken from Columns B-C-D or Columns 2-3-4 in the input table).

after declaring a variant variable ArrTrns, it is redimensioned to have NameCount * 3 1st dimension elements (lets say 'rows') and 3 2nd dimension elements (lets say 'columns') with

ReDim ArrTrns(1 To NameCount * 3, 1 To 3)

the 2 loops after this populate the array with values from cells whose row indicator is variable i and column indicator is variable j. i + 1 is used to skip the header (first) row.

i loops names from 1 to NameCount, j loops the stages from 2 to 4. k is the first dimension or row indicatior of the array. it increments by 1 at each loop of j.

i recommend you practice the basics of the vba. take class or online training, buy a vba book, etc.

mancubus
08-20-2016, 01:55 AM
modified code for a sheet which contains multiple tables.



Sub vbax_56911_transpose_multi_col()

Dim NameCount As Long, i As Long, j As Long, k As Long
Dim ArrTrns

With Worksheets("Sheet1")
NameCount = Application.CountA(.Cells(1).CurrentRegion.Columns(1).Offset(1)) 'Offset 1 row to exclude header
ReDim ArrTrns(1 To NameCount * 3, 1 To 3) '*3 to include 3 stages for each name
For i = 1 To NameCount
For j = 2 To 4
k = k + 1
ArrTrns(k, 1) = .Cells(i + 1, 1)
ArrTrns(k, 2) = .Cells(1, j)
ArrTrns(k, 3) = .Cells(i + 1, j)
Next j
Next i
End With

With Worksheets("Sheet2")
.Cells(1).CurrentRegion.Clear 'clear existing data
.Cells(1).Resize(UBound(ArrTrns, 1), UBound(ArrTrns, 2)) = ArrTrns
End With

End Sub

mancubus
08-20-2016, 02:23 AM
i attach the file i used for testing to give an idea.
code execution time included here.



Sub vbax_56911_transpose_multi_col_timer()

Dim NameCount As Long, i As Long, j As Long, k As Long
Dim ArrTrns
Dim StartTime As Double

StartTime = Timer

With Worksheets("Sheet1")
NameCount = Application.CountA(.Cells(1).CurrentRegion.Columns(1).Offset(1)) 'Offset 1 row to exclude header
ReDim ArrTrns(1 To NameCount * 3, 1 To 3) '*3 to include 3 stages for each name
For i = 1 To NameCount
For j = 2 To 4
k = k + 1
ArrTrns(k, 1) = .Cells(i + 1, 1)
ArrTrns(k, 2) = .Cells(1, j)
ArrTrns(k, 3) = .Cells(i + 1, j)
Next j
Next i
End With

With Worksheets("Sheet2")
.Cells(1).CurrentRegion.Clear 'clear existing data
.Cells(1).Resize(UBound(ArrTrns, 1), UBound(ArrTrns, 2)) = ArrTrns
End With

MsgBox Round(Timer - StartTime, 2)

End Sub

winfy
08-20-2016, 05:25 AM
Thanks Mancubus. I will definitely buy a book and learn more about vba. However, in the meantime I need to get something done for work. Can you just show me where and how I would make the code start from cell c17, instead of cell a1? For the single table scenario.

thanks

winfy

mancubus
08-20-2016, 06:10 AM
upload your workbook. alter any sensitive, company specific data.

snb
08-20-2016, 09:02 AM
This is what we call 'normalising' data:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
ReDim sp((UBound(sn) - 1) * (UBound(sn, 2) - 1), 2)

For j = 0 To UBound(sp) - 1
x = j \ (UBound(sn, 2) - 1) + 2
y = j Mod (UBound(sn, 2) - 1) + 2
sp(j, 0) = sn(x, 1)
sp(j, 1) = sn(1, y)
sp(j, 2) = sn(x, y)
Next

Cells(25, 1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

winfy
08-20-2016, 04:10 PM
upload your workbook. alter any sensitive, company specific data.

Hi Mancubus, see attached.

I just realised I need it in a slightly more complicated format.

To recap, there are names in column B and there are dates in column AF to AQ.

I want to create another sheet where columns AF to AQ are stacked in one column, while all other columns are populating with the same information, such as the names.

Please let me know if it doesn't make sense. As i said before, I need it for 500 rows and 15 columns.

For example:

Original


Full name

Position
VIP Group Name
Group count
Business Group
VIP/ESS?
Region
Proposed Engagement Start
Actual Engagement Start
Proposed site survey
Actual site survey
Go/ No Go
Proposed migration
Actual Migration
Email 1
Email 2
Email 3
Email 4
Email 5


Adam Strange

Ange
Adam Strange
2
Business Group 7

VIP
Ainslie
11-Apr-2017
16-Apr-2017
26-Apr-2017
21-Apr-2017
1-May-2017
26-Apr-2017
6-May-2017
6-May-2017
11-May-2017
16-May-2017
21-May-2017
26-May-2017




Modified


Full name

Position
VIP Group Name
Group count
Business Group

VIP/ESS?
Region

Key Delivery

Dates



Adam Strange

Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Proposed Engagement Start
11-Apr-2017


Adam Strange

Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Actual Engagement Start
16-Apr-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Proposed site survey
26-Apr-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Actual site survey
21-Apr-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Go/ No Go
1-May-2017


Adam Strange

Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Proposed migration
26-Apr-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Actual Migration
6-May-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Email 1
6-May-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Email 2
11-May-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Email 3
16-May-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Email 4
21-May-2017


Adam Strange
Ange
Adam Strange
2
Business Group 7
VIP
Ainslie
Email 5
26-May-2017

winfy
08-20-2016, 04:11 PM
This is what we call 'normalising' data:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
ReDim sp((UBound(sn) - 1) * (UBound(sn, 2) - 1), 2)

For j = 0 To UBound(sp) - 1
x = j \ (UBound(sn, 2) - 1) + 2
y = j Mod (UBound(sn, 2) - 1) + 2
sp(j, 0) = sn(x, 1)
sp(j, 1) = sn(1, y)
sp(j, 2) = sn(x, y)
Next

Cells(25, 1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

Thanks Snb!

snb
08-21-2016, 05:48 AM
That's a completely different question:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
ReDim sp((UBound(sn) - 1) * 12, 8)

For j = 0 To UBound(sp) - 1
x = j \ 12 + 2
y = j Mod 12 + 32
For jj = 0 To 5
sp(j, jj) = sn(x, jj + 2)
Next
sp(j, 6) = sn(x, 24)
sp(j, 7) = sn(1, y)
sp(j, 8) = sn(x, y)
Next

Sheet2.Cells(1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
Sheet2.Cells(1).CurrentRegion.Columns(9).NumberFormat = "dd/mm/yyyy"
End Sub

You could have made this code yourself based on the earlier suggestion I made.
Please analyse the code before using it.

winfy
08-21-2016, 07:29 PM
That's a completely different question:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
ReDim sp((UBound(sn) - 1) * 12, 8)

For j = 0 To UBound(sp) - 1
x = j \ 12 + 2
y = j Mod 12 + 32
For jj = 0 To 5
sp(j, jj) = sn(x, jj + 2)
Next
sp(j, 6) = sn(x, 24)
sp(j, 7) = sn(1, y)
sp(j, 8) = sn(x, y)
Next

Sheet2.Cells(1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
Sheet2.Cells(1).CurrentRegion.Columns(9).NumberFormat = "dd/mm/yyyy"
End Sub

You could have made this code yourself based on the earlier suggestion I made.
Please analyse the code before using it.

Snb, thanks for the code. When I try to run it, I hit an error at ReDim sp((UBound(sn) - 1) * 12, 8)


The error message is Run-time error '13'
Type mismatch.

Do you know why I am getting that error message

mancubus
08-21-2016, 11:07 PM
The error message is Run-time error '13'
Type mismatch.




code assumes you have a normal excel table.

it means table's topleft cell is A1,
Row 1 is the header row,
Row 1 and Column 1 of the table contain no blank cells.



dont leave blank: Column A & Row 1


another point: dont quote previous messages. just refer them or quote related part when needed!

winfy
08-22-2016, 12:08 AM
Hi Mancubus, thanks for your reply.

The table was a format table. I pasted values to a new Sheet1 and I do not get that error anymore.

However, I do encounter another error though under the line sp(j, 7) = sn(1, y)

The error message is Run Time Error 9, Subscript out of bounds. I haven't made any changes to the dimensions I have provided earlier.

snb
08-22-2016, 01:12 AM
You applied the code to a different workbook than you posted.

winfy
08-22-2016, 03:46 PM
Hi Mancubus and Snb, I have tried both your codes and Mancubus code seems to work better for me, it seems to work and doesn't give me errors I don't understand.

As I mentioned earlier, I need to repeat column A to Column F (as an example) and then from column G to column K, I need to collapse all the dates into one column.

I have studied Mancubus code and I can extend the columns to repeat itself for column B etc. I am wondering if we can extend the code as a loop so I don't have to specify one line for every column I need. I appreciate Snb's code does that in notion, but I am getting errors even when I am using the same file I posted.

Please help.

mancubus
08-22-2016, 11:49 PM
winfy

helpers try to post a solution based off the data you have provided ans/or workbook you have uploaded.

if you try snb's solution on a different workbook than you have posted here, it apparently will not produce the desired output.

snb's code takes and repeats values from Columns 2-7 (B-G), 24 (X) for values from Column 32 (AF) throughout 12 (AF-AQ) columns with their headers.

i believe you know what those constant numbers (0-5, 12, 8, 24, 32, etc) in the code mean.



now you upload a file which is different from the previous.
when the desired column numbers change in the requirement (file), so should the numbers representing them in the code.
i think you can adopt snb's code to this new file.

winfy
08-24-2016, 09:40 PM
Hi Mancubus/Snb, I understand how the forum works in terms of providing assistance now.

I have been asked to include a status column for every date column. How I need to transform the status columns are essentially the same thing as the date columns. Except now I need to take a loop to take every second column after the first date column.

I am wondering if you can provide assistance.

I have attached a sample workbook for ease. Sheet1 is the raw data and Sheet3 is a sample of the output I need.

I hope to hear from you soon.

snb
08-24-2016, 11:20 PM
The assistance has been given. It's up to you now to use it.

winfy
08-24-2016, 11:49 PM
Hi Snb, I appreciate you have assisted already, but my question is different to the one I posted earlier. I am transforming the date columns to one column, but at the same time, I need to take every second column and change it into another column. If you look at the sample workbook, you will know what i mean.

mancubus
08-25-2016, 06:00 AM
i believe you can adopt yourself below code to changing sheet structure from now on.



Sub M_snb()

sn = Sheets("Sheet1").Cells(1).CurrentRegion
ReDim sp((UBound(sn) - 1) * 12, 8)

For j = 0 To UBound(sp) - 1
x = j \ 12 + 2
y = (j Mod 12) * 2 + 7
For jj = 0 To 5
sp(j, jj) = sn(x, jj + 1)
Next
sp(j, 6) = sn(1, y)
sp(j, 7) = sn(x, y)
sp(j, 8) = sn(x, y + 1)
Next

With Sheets("Sheet2")
.Cells(1).CurrentRegion.Clear
.Cells(1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
.Cells(1).CurrentRegion.Columns(8).NumberFormat = "dd/mm/yyyy"
End With

End Sub

winfy
08-25-2016, 06:04 PM
Hi Mancubus, thanks so much. it works like a charm!

One last thing. Can you please just put a brief description next to some of the codes so I can learn and understand the code. Besides having low coding skills, I struggled to adopt the code because I don't see what Snb did. I know you provided a brief description on how Snb's codes work earlier and that was massively helpful. I just would like the brief description around the new changes you did to incorporate the status column. I definitely can't just look at the code and understand what it's doing. I need some plain English comments to guide me. Thanks in advance!

mancubus
08-25-2016, 11:34 PM
you are welcome.

if your column structure will change in the future, first convert that future table into present table then run the code.

solving problems with other people's help will provide quick solutions but you can't rely on this.

so first study the basics of vba. (Google "excel vba tutorial")

then visit snb's web site (click "more suggestions" in his signature) and find title Arrays.

i dont mean "read"... i mean "learn"...

then ask specific questions rather than asking for a tutorial.


each person on this planet is unique; has different training, knowledge, experience, skills, personality, etc.
so we can or can't see what other people see. :)

mancubus
08-26-2016, 12:54 AM
sn is a Base 1 2D array which holds all values in Sheet1!A1 current region. since it's populated from sheet range its lower bounds are 1.

1st dimenson (row equivalent)
Lower bound =s 1 / Upper Bound = rows count of the range

2nd dimenson (column equivalent)
Lower bound = 1, Upper Bound = columns count of the range


sp is Base 0 2D array which will be populated from sn based on desired output. (unless specifically stated as 1, default base is 0)

1st dimenson (row equivalent)
Lower bound = 0, Upper Bound = Upper Bound of sn's 1st Dimension * 12 - 1
"*12" because we want 12 date columns be included as rows. "-1" because we declared sp as base 0 array. sn is a base 1 array

2nd dimenson (column equivalent)
Lower bound = 0, Upper Bound = 8
"8" because we want 9 columns.

(Array Size = Upper Bound - Lower Bound + 1) (One dimension)


- the first 6 'columns' of sp are directly filled from sn's first six columns via inner loop (counter jj).
2nd dimension index is "jj + 1" because arrays' bases are not identical (1 and 0).

- x is the 1D index (row counter) that we want to repeat 12 times. "\12" makes this. "+2" is for starting at second 'row' of sn (first 'row' contains headers).

- y is the 2D index (column counter) which will start from column 7 to the last 'odd' column.

- y + 1 is for the status 'columns' (even) which are next to the date 'columns'.

- 7th, 8th, 9th 'columns' values are filled with x and y variables used as 1D and 2D dimension indices of sn

- finally existing data is cleared from Sheet2 and sp is written instead.


start training. now!
i and other members cant always find the time to explain the code in detail, nor can we respond to your additional requirements.
:D

winfy
08-26-2016, 01:47 AM
Hi Mancubus, I definitely appreciate your detailed explanation. I am struggling with loops in VBA, hence why I fail to fully understand Snb's code, while I did understand your original code.

I have been asked to add in two additional columns next to status column - status description and status level.

Studying the code, I would have thought I just need to do
ReDim sp((UBound(sn) - 1) * 12, 8) - change the 8 to 10 to add in the two additional columns
.
.
.
sp(j, 6) = sn(1, y)
sp(j, 7) = sn(x, y)
sp(j, 8) = sn(x, y + 1) - under sp(j,8)=(x,y+1), I just need to add sp(j,9)=(x,y+2) and sp(j,10)=(x,y+3)

But I'm just getting errors :banghead::mkay

snb
08-26-2016, 02:32 AM
Learning takes time.
Start mastering the fundamentals/basics.

mancubus
08-26-2016, 05:17 AM
as snb stated, you need the time to learn the basics first. then take more steps. Arrays are a powerful tool in vba but it is harder to learn the advanced than expected.

regarding the workbook in post #23, which columns you want to add the desired output?

winfy
08-26-2016, 05:28 AM
Hi Mancubus,

See attached for what I mean.

Mancbus, Snb - I'm definitely not trying to take shortcuts to be a VBA guru. I'm just trying to finish a 8000 row spreadsheet and things keep getting changed :crying::banghead::banghead:

Your assistance is greatly appreciated.

mancubus
08-26-2016, 06:25 AM
there is no such a thing as "things getting changed" so fast.
but there is "pure planning" and there is "pure communication".
:D

it is apparent that this assignment is above your head at the moment.
you should infrom your boss that you have outsourced the issue but external helper(s) is (are) bored of getting notification emails.

this is my last 'solution' post to the thread.

you should ask for paid services (which i dont provide) maybe.



Sub M_snb_3()

sn = Sheets("Sheet1").Cells(1).CurrentRegion
ReDim sp((UBound(sn) - 1) * 12, 10)

For j = 0 To UBound(sp) - 1
x = j \ 12 + 2
y = (j Mod 12) * 4 + 7
For jj = 0 To 5
sp(j, jj) = sn(x, jj + 1)
Next
sp(j, 6) = sn(1, y)
sp(j, 7) = sn(x, y)
sp(j, 8) = sn(x, y + 1)
sp(j, 9) = sn(x, y + 2)
sp(j, 10) = sn(x, y + 3)
Next

With Sheets("Sheet2")
.Cells(1).CurrentRegion.Clear
.Cells(1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
.Cells(1).CurrentRegion.Columns(8).NumberFormat = "dd/mm/yyyy"
End With
End Sub