Converting Data in two Columns into a Row on separate sheet

The Chad · Oct 20, 2011

Hi,
[FONT=&quot][/FONT]I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.
Example - Sheet "Gp Trg Plan"
Column B Column D
Tower Module 1
Tower Module 2
Tower Module 3
Building Module 1
Building Module 8
Street Module 6

Ideally the row will look like.
Example - Sheet "Status WS"
Column A Column B Column C Column D Column E Column F Column G
Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date

There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.

I hope this makes sense???

thanks for any help,
Chad

squidgeny · Oct 20, 2011

I don't understand your example.. you have "Person" in Status WS, but I see no mention of "Person" in Gp Trg Plan. Where did Person come from if the data is supposed to be transposed?

Peter_SSs · Oct 20, 2011

Chad

Welcome to the MrExcel board!

It is also virtually impossible to work out what is in ehat column in 'Status WS'.

Consider trying to show your small data samples using one of the methods mentioned in my signature block.

The Chad · Oct 20, 2011

[FONT=&quot]Apologies for the confusion - The Gp Trg Plan sheet is a reference sheet of all training that is being conducted this financial year. There is no reference to a person in this sheet, only the Sub Gp (Column B) which also aligns to Columns B in the Status WS Sheet. My worksheet is “Status WS” and I want to reference the data (training modules in Gp Trg Plan) that are required to be completed by the various Sub Groups.[/FONT]

The Chad · Oct 25, 2011

I have worked out what I believe to be a simple step by using the transpose function to take training modules from a column and add them to my Status WS.

Code:

Sub ModuleTranspose()
'
' ModuleTranspose Macro
' Macro recorded 21/10/2011 by burke_cz
'
    Sheets(" Mandatory Mods Gp Trg Plans").Select
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Status WS").Select
    Range("A1").Select
    Selection.End(xlToRight).Offset(0, 1).Select
    'Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub

I understand the code is not that flash (eg, using "select" quite a bit).

Converting Data in two Columns into a Row on separate sheet

The Chad

New Member

Excel Facts

squidgeny

Board Regular

Peter_SSs

MrExcel MVP, Moderator

The Chad

New Member

The Chad

New Member

Similar threads

Forum statistics

Share this page

Converting Data in two Columns into a Row on separate sheet

The Chad

New Member

Excel Facts

squidgeny

Board Regular

Peter_SSs

MrExcel MVP, Moderator

The Chad

New Member

The Chad

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock